# Append Records to a fixed number of columns using dynamic data [duplicate]

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

[], 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];
``````