CODEWITHSUNDEEP
c#sql
eric
eric

Reputation: 454

combine multiple sql rows with different columns

Okay so say I have something like this:

ID | Name | Address
 1 | Bob  | 123 Fake Street
 1 | Bob  | 221 Other Street

done by doing something like:

select p.ID, p.Name a.Address from People p 
inner join Addresses a on a.OwnerID = p.ID

Is there any way to turn that into 

ID | Name |    Address_1    |     Address_2    | etc...
 1 | Bob  | 123 Fake Street | 221 Other street | etc

I've seen things that do comma separated values in one column but I don't want that I want distinct columns. I am querying this using MSSQL and C# I don't know if that changes anything. Also this is a made up scenario that is just similar to what I'm doing so the actual structure of the tables can't be changed.

Anyone have any suggestions?

Upvotes: 1

Views: 392

Answers (1)

Taryn
Taryn

Reputation: 247810

You can use the PIVOT function to get the result but you will also have to implement using a row_number() so you can convert multiple addresses per person into columns.

If you had a known number of addresses, then you would hard-code the query:

select id, name, address_1, address_2
from
(
  select p.id, p.name, a.address,
    'Address_'+cast(row_number() over(partition by p.id 
                                      order by a.ownerid) as varchar(10)) rn
  from people p
  inner join addresses a
    on p.id = a.ownerid
) d
pivot
(
  max(address)
  for rn in (address_1, address_2)
) piv;

See SQL Fiddle with Demo.

But if your case, you will have an unknown number of addresses per person so you will want to use dynamic SQL and place it into a stored procedure to execute:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Address_'+d.rn) 
                    from 
                    (
                      select cast(row_number() over(partition by a.ownerid
                                      order by a.ownerid) as varchar(10)) rn
                      from Addresses a
                    ) d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, name, ' + @cols + ' 
            from
            (
              select p.id, p.name, a.address,
                ''Address_''+cast(row_number() over(partition by p.id 
                                                  order by a.ownerid) as varchar(10)) rn
              from people p
              inner join addresses a
                on p.id = a.ownerid
            ) d
            pivot 
            (
                max(address)
                for rn in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo. These both give a result:

| ID | NAME |         ADDRESS_1 |        ADDRESS_2 | ADDRESS_3 |
----------------------------------------------------------------
|  1 |  Bob |   123 Fake Street | 221 Other Street |    (null) |
|  2 |  Jim | 123 e main street |           (null) |    (null) |
|  3 |  Tim |   489 North Drive |   56 June Street |  415 Lost |

Upvotes: 4

Related Questions