RANK ISSUE

• Snargables

  SSCrazy Eights

  Points: 8855

  More actions

  March 7, 2013 at 12:57 pm

  #394339

  NOTICE THE DESIRED OUTPUT ON THE BOTTOM.

  SELECT 'table1' TABLENAME

  ,'col1' COLNAME

  INTO #temp

  UNION ALL

  SELECT 'table1'

  ,'col2'

  UNION ALL

  SELECT 'table1'

  ,'col3'

  UNION ALL

  SELECT 'table2'

  ,'col1'

  UNION ALL

  SELECT 'table2'

  ,'col2'

  UNION ALL

  SELECT 'table3'

  ,'col1'

  SELECT *

  FROM #temp

  --DESIRED OUTPUT ID COL:

  --ID TABLENAMECOLNAME

  --1 table1 col1

  --2 table1 col2

  --3 table1 col3

  --1 table2 col1

  --2 table2 col2

  --1 table3 col1

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 7, 2013 at 1:01 pm

  #1594657

  Snargables (3/7/2013)

  ---

  NOTICE THE DESIRED OUTPUT ON THE BOTTOM.

  SELECT 'table1' TABLENAME

  ,'col1' COLNAME

  INTO #temp

  UNION ALL

  SELECT 'table1'

  ,'col2'

  UNION ALL

  SELECT 'table1'

  ,'col3'

  UNION ALL

  SELECT 'table2'

  ,'col1'

  UNION ALL

  SELECT 'table2'

  ,'col2'

  UNION ALL

  SELECT 'table3'

  ,'col1'

  SELECT *

  FROM #temp

  --DESIRED OUTPUT ID COL:

  --ID TABLENAMECOLNAME

  --1 table1 col1

  --2 table1 col2

  --3 table1 col3

  --1 table2 col1

  --2 table2 col2

  --1 table3 col1

  Okay, so what is the problem?

• Snargables

  SSCrazy Eights

  Points: 8855

  More actions

  March 7, 2013 at 1:06 pm

  #1594662

  I need to generate the id col values. I don’t know how to make a rank or rownumber reset.

  notice it goes back to one. When it gets to a new table. I 've been playing w dense rank but don’t see one where it goes back to 1.

  am I making any sense? for some reason i'm having a really hard time explaining it

• Snargables

  SSCrazy Eights

  Points: 8855

  More actions

  March 7, 2013 at 1:08 pm

  #1594664

  run the insert and select. Notice there is no id col being retruned. It's in the desired output. i need to find a way to generate that id patter in my select

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 7, 2013 at 1:13 pm

  #1594673

  select

  ID = row_number() over (partition by TABLENAME order by COLNAME),

  TABLENAME,

  COLNAME

  from

  #temp;

• Snargables

  SSCrazy Eights

  Points: 8855

  More actions

  March 7, 2013 at 1:18 pm

  #1594676

  ah. row number w partition. nice. i was about to cross apply it to itself to get the darn id to reset. thanks