0

This question already has an answer here:

I have a table with 22,000 records formatted like so:

Input

[ID],[Cost Center],[Function]
1,01A1001710,GF000001
2,01A1001710,GF000802
3,02R1012000,GF000001
4,02R1012000,GF000059
5,02R1012000,GF000013

What I would like to do is append these to a new table formatted like so:

Output

[Cost Center], [Node1], [Node2], [Node3], [Node4], [Node5], [Node6]
01A1001710,GF000001,GF000802
02R1012000,GF000001,GF000059,GF000013

What is unknown is the number of [Function] per [Cost Center] it could range from a single function to a maximum of 6.

I tried to pivot but wound up with 60 different columns representing [Function]

marked as duplicate by Paul White May 6 '16 at 18:20

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

[], pivot are hints it's MS SQL. Then

select [Cost Center]
  , [Node1] = max(case rn when 1 then [Function] end)
  , [Node2] = max(case rn when 2 then [Function] end)
  , [Node3] = max(case rn when 3 then [Function] end)
  , [Node4] = max(case rn when 4 then [Function] end)
  , [Node5] = max(case rn when 5 then [Function] end)
  , [Node6] = max(case rn when 6 then [Function] end)
from (
    select [Cost Center],[Function]
     , rn = row_number() over(partition by [Cost Center] order by [Function])
     --  or may be ..order by [id]) depending on column sequence you need.
    from youTable) t
group by [Cost Center];

Not the answer you're looking for? Browse other questions tagged or ask your own question.