Grouping columns

• dhanasekar.palani

  SSC Eights!

  Points: 841

  More actions

  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.

• michal.lisinski

  SSCertifiable

  Points: 6933

  More actions

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  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

• dhanasekar.palani

  SSC Eights!

  Points: 841

  More actions

  September 16, 2015 at 2:40 am

  #1826998

  Thanks that works.