SQL Server - Rows to Columns without Aggregation

Question

I have data that looks like this:

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

My goal is to make it look like this:

Address  id1   id2   id3   id4   
123Any   1234  1235  1246  1237

Based on some Googling and what not, I was able to generate the following CTE:

with cust_cte (Address, id, RID) as (
    SELECT Address, id, 
           ROW_NUMBER() OVER (PARTITION BY (Address) ORDER BY Address) AS RID 
    FROM tab)  

The next step would be to pivot so that for each RID, I place the associated id in the column. However, I can't seem to get the example I found to work. Rather than post the rest of the example which may not even really apply, I'll leave it up to the audience. Other novel approaches not necessarily utilizing the CTE are also appreciated. This will be chugging through a lot of data, so efficiency is important.

Answer 1

You can transform this data using the PIVOT function in SQL Server. In order to PIVOT the data, you will want to create your new column using the row_number().

If you have a known number of values, then you can hard-code the query:

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

See SQL Fiddle with Demo

But if the values are unknown then you will need to use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' 
                      + QUOTENAME(rn) 
                    from
                    (
                      select 'id_'+cast(row_number() over(partition by address 
                                order by id) as varchar(20)) rn
                      from yourtable
                    ) src
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT address,' + @cols + ' from 
             (
                select address, id,
                  ''id_''+cast(row_number() over(partition by address 
                                              order by id) as varchar(20)) rn
                from yourtable
            ) x
            pivot 
            (
                max(id)
                for rn in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

The result of both queries is:

|     ADDRESS | ID_1 | ID_2 | ID_3 | ID_4 |
-------------------------------------------
| 12AnyStreet | 1234 | 1235 | 1236 | 1237 |