SQL - Split values into different columns?

Question

I have a table that looks like this

CAN | Contact | Email | Order Ref
22  | C1      |blah@b | 23334
22  | C2      |blah@b | 23334
22  | C3      |blah@b | 23334

How could I get this to split the contact Ids across 3 columns so there is only 1 row per CAN? Something like

CAN | Contact1 | Contact 2 | Contact 3|  Email | Order Ref
22  | C1       |    C2     |    C3    |blah@b | 23334

Thanks for any help!

Answer 1

Here is an example of dynamic PIVOT operator:

create table #tbl(
    can int
    ,contact varchar(3)
    ,email varchar(10)
    ,orderRef int
)

insert into #tbl values (22, 'C1', 'blah@b', 23334)
insert into #tbl values (22, 'C2', 'blah@b', 23334)
insert into #tbl values (22, 'C3', 'blah@b', 23334)


declare @columnName as varchar(max)
declare @columnNameWAlias as varchar(max)
declare @sql as nvarchar(max)


SELECT @ColumnName = 
    ISNULL(@ColumnName + ',','') + QUOTENAME(contact)
    FROM (SELECT DISTINCT can, contact, email, orderRef FROM #tbl) AS Contacts

SELECT @columnNameWAlias = 
    ISNULL(@columnNameWAlias + ',','') + QUOTENAME(contact) + ' AS [Contact' + CAST(seq as varchar) + ']'
    FROM (SELECT DISTINCT can, contact, email, orderRef, seq = ROW_NUMBER() OVER (order by contact) FROM #tbl) AS Contacts


SET @sql = N'
    SELECT 
    CAN
    ,' + @columnNameWAlias + 
    ',Email
    ,orderREf
FROM (
    SELECT
        can
        ,contact
        ,Email
        ,orderRef
    FROM #tbl
) tbl
PIVOT (
    MAX(contact) FOR contact IN (' + @columnName + ')
) pvt
'

EXEC sp_executesql @sql;

drop table #tbl

Here you can find more info and examples about it.

Answer 2

Dont know why scimon delete his answer, but here is the final version:

SQL DEMO

with cte as(
select
    *
    ,row_number() over (partition by CAN order by Contact) as RN
from
    Table1)    
select
    CAN
    , MAX(case when RN = 1 then Contact end) as Contact1
    , MAX(case when RN = 2 then Contact end) as Contact2
    , MAX(case when RN = 3 then Contact end) as Contact3
    ,Email
    ,[Order Ref]
from
    cte
GROUP BY CAN, Email, [Order Ref] 

OUTPUT