Group ID in each other

• Thinky Night

  SSCommitted

  Points: 1663

  More actions

  February 20, 2012 at 9:08 am

  #253324

  I have a table

  X Y

  ---- -----

  A B

  A B

  A C

  A C

  A C

  A D

  A D

  And I want to get an output like this:

  X Y SEQ

  ---- ---- -----

  A B 1

  A B 1

  A C 2

  A C 2

  A C 2

  A D 3

  A D 3

  How do i achive this with one nested or CTE SQL?

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  February 20, 2012 at 9:15 am

  #1449301

  DECLARE @t TABLE(X CHAR(1), Y CHAR(1))

  INSERT INTO @t(X,Y)

  SELECT 'A' ,'B' UNION ALL

  SELECT 'A' ,'B' UNION ALL

  SELECT 'A' ,'C' UNION ALL

  SELECT 'A' ,'C' UNION ALL

  SELECT 'A' ,'C' UNION ALL

  SELECT 'A' ,'D' UNION ALL

  SELECT 'A' ,'D';

  SELECT X,Y,DENSE_RANK() OVER(ORDER BY X,Y) AS SEQ

  FROM @t

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Thinky Night

  SSCommitted

  Points: 1663

  More actions

  February 20, 2012 at 9:24 am

  #1449306

  Thanks a lot