SQL Server - Rows to Columns without Aggregation -

SQL Server - Rows to Columns without Aggregation -

i have info looks this:

address | id 12anystreet | 1234 12anystreet | 1235 12anystreet | 1236 12anystreet | 1237

my goal create this:

address id1 id2 id3 id4 123any 1234 1235 1246 1237

based on googling , not, able generate next cte:

with cust_cte (address, id, rid) ( select address, id, row_number() on (partition (address) order address) rid tab)

the next step pivot each rid, place associated id in column. however, can't seem illustration found work. rather post rest of illustration may not apply, i'll leave audience. other novel approaches not utilizing cte appreciated. chugging through lot of data, efficiency important.

you can transform info using pivot function in sql server. in order pivot data, want create new column using row_number().

if have known number of values, can hard-code query:

select * ( select address, id, 'id_'+cast(row_number() over(partition address order id) varchar(20)) rn yourtable ) src pivot ( max(id) rn in ([id_1], [id_2], [id_3], [id_4]) ) piv

see sql fiddle demo

but if values unknown need utilize dynamic sql:

declare @cols nvarchar(max), @query nvarchar(max) select @cols = stuff((select distinct ',' + quotename(rn) ( select 'id_'+cast(row_number() over(partition address order id) varchar(20)) rn yourtable ) src xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = 'select address,' + @cols + ' ( select address, id, ''id_''+cast(row_number() over(partition address order id) varchar(20)) rn yourtable ) x pivot ( max(id) rn in (' + @cols + ') ) p ' execute(@query)

see sql fiddle demo

the result of both queries is:

| address | id_1 | id_2 | id_3 | id_4 | ------------------------------------------- | 12anystreet | 1234 | 1235 | 1236 | 1237 |

sql sql-server tsql pivot