I have two tables as below. I want to join these two tables and need to set the column values of second table as column header as shown below. How can I achieve this?
Table1
x
ID NAME
---------------
1 AAA
2 BBB
table2
ID QUESTION ANSWER
----------------------------------
1 Q1 YES
1 Q2 NO
1 Q3 YES
1 Q4 NO
1 Q5 YES
2 Q1 YES
2 Q2 YES
2 Q3 YES
2 Q4 YES
2 Q5 YES
The output table should be
ID NAME Q1 Q2 Q3 Q4 Q5
----------------------------------------------------
1 AAA YES NO YES NO YES
2 BBB YES YES YES YES YES
Advertisement
Answer
You can use Dynamic Pivot as below-
DECLARE @cols AS NVARCHAR(MAX),
@sqlCommand AS NVARCHAR(MAX);
SELECT @cols =
STUFF((SELECT ( '],[' + A.QUESTION)
FROM (SELECT DISTINCT QUESTION FROM table2) A
ORDER BY A.QUESTION
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')+']'
FROM table2
SET @sqlCommand=
N'SELECT [ID],NAME,'+SUBSTRING(@cols,2,LEN(@cols))+'
FROM
(
SELECT A.ID,A.NAME, B.QUESTION, B.ANSWER
FROM table1 A
INNER JOIN table2 B ON A.ID = B.ID
) AS P
PIVOT
(
MAX(ANSWER)
FOR QUESTION IN('+SUBSTRING(@cols,2,LEN(@cols))+')
) PVT'
--PRINT @sqlCommand
EXEC (@sqlCommand)