获取行值作为列标题

rjzwgtxy 于 2021-08-13 发布在 Java

关注(0)|答案(3)|浏览(351)

我有两张table如下。我想连接这两个表，需要将第二个表的列值设置为列标题，如下所示。我怎样才能做到这一点？
表1

ID       NAME    
---------------
1        AAA
2        BBB

表2

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

输出表应该是

ID      NAME       Q1     Q2      Q3     Q4      Q5
   ----------------------------------------------------
   1       AAA        YES    NO     YES     NO      YES
   2       BBB        YES    YES    YES     YES     YES

sql sql-server sql-server-2008

来源：https://stackoverflow.com/questions/62082122/get-row-value-as-column-header

3条答案

按热度按时间

jjjwad0x1#

您可以使用动态枢轴，如下所示-

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)

赞(0）回复(0）举报 2021-08-13

qqrboqgw2#

必须使用pivot来编写此查询：

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

如果你的问题是动态的，你可以这样写

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

赞(0）回复(0）举报 2021-08-13

ny6fqffe3#

这不是您通常希望在表示层中执行的操作吗？报告工具就是为此而设计的。

赞(0）回复(0）举报 2021-08-13