Get Row value as Column Header

Question

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

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

Is it a fixed number of questions, or may Q6 suddenly show up some day? — jarlh, May 29 '20 at 08:55
You need to learn how to use Pivot Tables. Here's a quick link: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Algef Almocera, May 29 '20 at 09:34

score 1 · Accepted Answer · answered May 29 '20 at 09:35

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)

Hesam Akbari · Answer 2 · 2020-05-29T10:44:35.080

You must use Pivot to write this query:

Sample Data:

DECLARE @Table1 TABLE( id int, name nvarchar(10));
DECLARE @Table2 TABLE( id int, Question nvarchar(10),Answer nvarchar(10))

insert into @Table1 (id,[name])values(1,'AAA')
insert into @Table1 (id,[name])values(2,'BBB')


insert into @Table2 (id,Question,Answer)values(1,'Q1','YES')
insert into @Table2 (id,Question,Answer)values(1,'Q2','NO')
insert into @Table2 (id,Question,Answer)values(1,'Q3','YES')
insert into @Table2 (id,Question,Answer)values(1,'Q4','NO')
insert into @Table2 (id,Question,Answer)values(1,'Q5','YES')
insert into @Table2 (id,Question,Answer)values(2,'Q1','YES')
insert into @Table2 (id,Question,Answer)values(2,'Q2','YES')
insert into @Table2 (id,Question,Answer)values(2,'Q3','YES')
insert into @Table2 (id,Question,Answer)values(2,'Q4','YES')
insert into @Table2 (id,Question,Answer)values(2,'Q5','YES')

select * from @Table1 t1
inner join (
select * from(
select 
t2.id
,t2.Question
,t2.Answer
from @Table2 t2
)temp
pivot
    (
    MAX(Answer)
    for question in ([Q1],[Q2],[Q3],[Q4],[Q5])
    ) as PivotTable 
)TemporaryTable on t1.id=TemporaryTable.id

if your Questions are Dynamic, you can write like this

Declare @cols nvarchar(MAX);
Declare @query nvarchar(MAX);
    set @cols=( select STRING_AGG('['+Question+']',',') from @Table2);
    set @query='
    select * from @Table1 t1
    inner join (
    select * from(
    select 
    t2.id
    ,t2.Question
    ,t2.Answer
    from @Table2 t2
    )temp
    pivot
        (
        MAX(Answer)
        for question in ('+@cols+')
        ) as PivotTable 
    )TemporaryTable on t1.id=TemporaryTable.id
    '
    exec sp_executesql  @query

shows "'STRING_AGG' is not a recognized built-in function name." — user2431727, May 29 '20 at 10:57

score -1 · Answer 3 · answered May 29 '20 at 10:14

-1

Isn't this something you typically want to do in your presentation layer? Reporting tools are made for this.

answered May 29 '20 at 10:14

Wouter

2,881
2
9
22

Yes. can you provide any useful link? i found RDLC report. but it is not shows column values as header – user2431727 May 29 '20 at 10:39
You can use Reporting Services (SSRS) and use a matrix. – Wouter May 29 '20 at 10:55
Or Power BI, also lets you pivot data – Wouter May 29 '20 at 10:55

Get Row value as Column Header

3 Answers3