I need the help from the community to solve the following problem that I found (I’m using SQL Server):
I have two tables, USER and INFO.
USER:
ID NAME ---------- AAA John BBB Mike
INFO:
ID CONTANCT_INFO CONTACT_DESC ------------------------------------- AAA 111111 Phone AAA 222222 Mobile AAA aaa@example.com Email AAA bbb@example.com Email BBB 333333 Phone BBB ccc@example.com Email BBB ddd@example.com Email BBB eee@example.com Email BBB 444444 Phone BBB 555555 Mobile
I want to merge the info table per user in one row as follows:
ID NAME Phone1 Phone2 Phone3 Email1 Email2 Email3 --------------------------------------------------------------------------- AAA John 111111 222222 aaa@example.com bbb@example.com BBB Mike 333333 444444 555555 ccc@example.com ddd@example.com eee@example.com
I’ve been trying many options and also surfing through stackoverflow posts, but without any results to my situation.
Thanks in advance!
Kind regards,
Aovon.
Advertisement
Answer
If you are sure that there will be only 3 phones and emails then following query will work:
;with PivotData as
(
select I.id,
U.Name,
I.CONTANCT_INFO,
Replace(I.CONTACT_DESC,'Mobile','Phone') + '_'+
cast( row_number() over (partition by I.id,
Replace(I.CONTACT_DESC,'Mobile','Phone')
order by (Select null))--change here for sort order
as char(1)) as CONTACT_DESC_Attribute
From Info I
join [User] U on U.id = I.Id
)
SELECT id,Name,[Phone_1],[Phone_2],[Phone_3],
[Email_1],[Email_2],[Email_3]
FROM PivotData
PIVOT( Max(CONTANCT_INFO)
FOR CONTACT_DESC_Attribute IN ([Phone_1],[Phone_2],[Phone_3],
[Email_1],[Email_2],[Email_3]) ) AS P;
Use dynamic query for pivoting as:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ','
+ QUOTENAME(Replace(I.CONTACT_DESC,'Mobile','Phone') + '_'+
cast( row_number() over (partition by I.id,
Replace(I.CONTACT_DESC,'Mobile','Phone')
order by (Select null))--change here for sort order
as char(1)))
FROM Info I
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id,Name, ' + @cols + ' from
(
select I.id,
U.Name,
I.CONTANCT_INFO,
Replace(I.CONTACT_DESC,''Mobile'',''Phone'') + ''_''+
cast( row_number() over (partition by I.id,
Replace(I.CONTACT_DESC,''Mobile'',''Phone'')
order by (Select null))--change here for sort order
as char(1)) as CONTACT_DESC_Attribute
From Info I
join [User] U on U.id = I.Id
) x
pivot
(
max(CONTANCT_INFO)
for CONTACT_DESC_Attribute in (' + @cols + ')
) p '
execute(@query);
Hope this helps!!!