Post reply

Grouping columns

  • September 15, 2015 at 3:30 am

    #304506

    I have table like below.

    filename col1 col2 col3

    ABD Y NULL Y

    XYZ Y Y Y

    CDZ Y Y Y

    I Need a output like this

    filename col1 col2 col3 Group

    ABD Y NULL Y Group1

    XYZ Y Y Y Group2

    CDZ Y Y Y Group2

    I wanted to group the col1 , col2, col3 and group it as same group. Can somebody help on this.

  • September 15, 2015 at 3:43 am

    #1826663

    Hi

    Try this:

    alter table your_table add group_col AS CHECKSUM(col1, col2, col3)

    Br.

    Mike

  • September 15, 2015 at 6:41 am

    #1826710

    Or something like this?

    CREATE TABLE SampleData(

    filename char(3),

    col1 char(1),

    col2 char(1),

    col3 char(1),

    [Group] char(10),

    );

    INSERT INTO SampleData(filename, col1, col2, col3)

    VALUES

    ('ABD', 'Y', NULL, 'Y'),

    ('XYZ', 'Y', 'Y', 'Y'),

    ('CDZ', 'Y', 'Y', 'Y');

    -- As SELECT

    SELECT filename,

    col1,

    col2,

    col3,

    'Group' + CAST( DENSE_RANK() OVER( ORDER BY col1, col2, col3) AS varchar(3)) AS [Group]

    FROM SampleData;

    --As UPDATE

    WITH CTE AS(

    SELECT *,

    'Group' + CAST( DENSE_RANK() OVER( ORDER BY col1, col2, col3) AS varchar(3)) AS [CalculatedGroup]

    FROM SampleData

    )

    UPDATE CTE

    SET [Group] = CalculatedGroup;

    SELECT * FROM SampleData;

    GO

    DROP TABLE SampleData;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 16, 2015 at 2:40 am

    #1826998

    Thanks that works.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply