pivot查询多连接

o8x7eapl 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(220)

我有三张table如下。
提交1\u标题

Submission1_id   Submission2_id     Name      
100                  500            Rahul    
200                  500            Deva

提交详细信息

Submission1_id  Submission2_id    Question_no     Answer
  100              500                1          YES
  100              500                2          YES
  100              500                3          NO
  100              500                4          NO
  100              500                5          YES
  200              501                1          YES
  200              501                3          YES
  200              501                4          YES

提交详细信息

Submission2_id     Question_no     Answer
   500                1             YES
   500                2             YES
   501                1             YES
   501                2             YES
   501                3             NA

使用下面的查询，我得到如下输出（仅连接前两个表）

==================DYNAMIC PIVOT===============
DECLARE
@columns NVARCHAR(MAX) = '',
@sql     NVARCHAR(MAX) = '';

 -- select the question num
SELECT
@columns+=QUOTENAME(Question_no) + ','
FROM
submission1_details  WHERE Submission1_id=100
ORDER BY
Question_no;

 -- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

 -- construct dynamic SQL
 SET @sql ='
  SELECT * FROM  
 (
SELECT  Submission1_id,Name,Question_no,Answer FROM submission1_header t1 INNER JOIN submission1_details t2 ON  t1.Submission1_id=  t2.Submission1_id WHERE t1.Submission1_id=100
  ) t
PIVOT(
MAX(Answer)
for Question_no  IN ('+ @columns +')
) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

输出：

Submission1_id       NAME       1      2       3       4      5

    100              Rahul      YES    YES     NO      NO     YES

现在我也想加入第三个小组。输出应为：

Submission1_id  NAME  sub1_Q1  sub1_Q2   sub1_Q3  sub1_Q4   sub1_Q5   sub2_Q1    sub2_Q2    sub2_Q3

   100          Rahul   YES       YES       NO       NO       YES       YES         YES

如果 submission1_details 表中最多包含10个问题，则输出应显示 sub1_Q1 至 sub1_Q10 .
同样，如果 submission2_details 表中最多包含20个问题，那么输出应该从中创建列 sub2_Q1 至 sub2_Q20 .

sql sql-server pivot sql-server-2008 dynamic-pivot

来源：https://stackoverflow.com/questions/62166553/pivot-query-multiple-joining

1条答案

按热度按时间

bwntbbo31#

DECLARE
            @columns NVARCHAR(MAX) = '',
            @columnsname NVARCHAR(MAX) = '',
            @columnsnameA NVARCHAR(MAX) = '',

            @columnsB NVARCHAR(MAX) = '',
            @columnsnameB NVARCHAR(MAX) = '',

            @sql     NVARCHAR(MAX) = '';

             -- select the question num
             SET @columns = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar)) 
                        FROM submission1_details  WHERE Submission1_id=100

                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                    ,1,1,'')

            SET @columnsname = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar)) + ' sub1_Q'+ cast(Question_no as varchar)
                        FROM submission1_details  WHERE Submission1_id=100

                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                    ,1,1,'')

                    SET @columnsnameA = STUFF((SELECT distinct ','+' sub1_Q'+ cast(Question_no as varchar)
                        FROM submission1_details  WHERE Submission1_id=100

                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                    ,1,1,'')

                    SET @columnsB = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar)) 
                        FROM submission2_details  WHERE Submission2_id=500

                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                    ,1,1,'')

            SET @columnsnameB = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar)) + ' sub2_Q'+ cast(Question_no as varchar)
                        FROM submission2_details  WHERE Submission2_id=500

                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                    ,1,1,'')

            print @columns

             SET @sql ='
              select Submission1_id,Submission2_id,Name ,'+ @columnsnameA+ ','+@columnsnameB +' from (
             select Submission1_id,Submission2_id,Name ,'+@columnsname+',Question_no, Answer from (
             select distinct t1.Submission1_id,t1.Submission2_id,Name,t2.Question_no t,t2.Answer A,t3.Question_no,t3.Answer from submission1_header t1 
             inner JOIN submission1_details t2 ON  t1.Submission1_id=  t2.Submission1_id
              inner JOIN submission2_details t3 ON  t1.Submission2_id=  t3.Submission2_id  

             WHERE t1.Submission1_id=100
             ) as a
             PIVOT(
            MAX(A)
            for t  IN ('+ @columns +')
            ) AS pivot_table
            ) as b
            PIVOT(
            MAX(Answer)
            for Question_no  IN ('+ @columnsB +')
            ) AS pivot_table1;';
            print @sql

            -- execute the dynamic SQL
            EXECUTE sp_executesql @sql;

赞(0）回复(0）举报 2021-07-26

我来回答

pivot查询多连接

1条答案

相关问题

热门标签

最新问答