时间戳

首页 > 解决方案 > 在一个表中显示来自不同临时表的值

sql - 在一个表中显示来自不同临时表的值

问题描述

我的 sql 表如下：-

 CREATE TABLE #TmpA (
 Type1 Varchar(10),
 Col1 VARCHAR(10),
 Request INT,
 Due INT
  );

CREATE TABLE #TmpB (
 Type1 Varchar(10),
 Col1 VARCHAR(10),
 Request INT,
 Due INT );

CREATE TABLE #TmpC (
 Type1 Varchar(10),
 Col1 VARCHAR(10),
 Request INT,
 Due INT );

 INSERT INTO #TmpA VALUES('P', 'Name1',0,278),('P', 'Name2',10,89),('R', 'Name3',5,89)

 INSERT INTO #TmpB VALUES ('P', 'Name1',0,10),('P', 'Name2',1,78),('A', 'Name4',4,289 )

 INSERT INTO #TmpC VALUES ('P', 'Name1',54,67),('P', 'Name5',5,47),('A', 'Name6',3,90 )

SELECT * FROM #TmpA
SELECT * FROM #TmpB
SELECT * FROM #TmpC

我想将我所有的表合并到一张表中，因为我又创建了一张表#TmpD。我想以以下格式显示：- 在此处输入图像描述

在这个例子中，#TmpD 列需要是动态的，我取了 3，但也可以多于 3 或少于 3。

标签： sqlsql-server

解决方案

另一种使用动态枢轴的方法：

---- create new table #tmpD by using select ... into
select *
into #tmpD
from  
    (      
        select *, 1 as reqNr  from #TmpA union all
        select *, 2  from #tmpB union all
        select *, 3  from #tmpc 
    ) t

declare @cols_req as nvarchar(max)
     ,  @cols_req_max as nvarchar(max)
     ,  @cols_due as nvarchar(max)
     ,  @cols_due_max as nvarchar(max)
     ,  @query  as nvarchar(max)

select @cols_req = stuff((select distinct ',' + quotename('Request'+ cast(row_number() over(partition by col1, type1 order by type1, col1) as varchar(10))) from #tmpD for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')
select @cols_req_max = stuff((select distinct ',' + ('max(Request'+ cast(row_number() over(partition by col1, type1 order by type1, col1) as varchar(10)) +') as Request' + cast(row_number() over(partition by col1, type1 order by col1, type1) as varchar(10))) from #tmpD for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')
select @cols_due = stuff((select distinct ',' + quotename('Due'+ cast(row_number() over(partition by col1, type1 order by type1, col1) as varchar(10))) from #tmpD for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')
select @cols_due_max = stuff((select distinct ',' + ('max(Due'+ cast(row_number() over(partition by col1, type1 order by type1, col1) as varchar(10)) +') as Due' + cast(row_number() over(partition by col1, type1 order by col1, type1) as varchar(10))) from #tmpD for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')

set @query = '    select Type1, Col1, isnull(Request1, 0) Request1, isnull(Due1, 0) Duel1, isnull(Request2, 0) Request2, isnull(Due2, 0) Due2, isnull(Request3, 0) Request3, isnull(Due3, 0) Due3 
                  from (
                          select Type1, Col1, ' + @cols_req_max + ', ' + @cols_due_max + '
                          from 
                                (
                                    select  Type1
                                          , Col1
                                          , Request
                                          , Due
                                          , col_req = ''Request''+ cast(reqNR as varchar(10))
                                          , col_due = ''Due''+ cast(reqNR as varchar(10))
                                    from #tmpD 
                                ) x
                          pivot ( max(request) for col_req in (' + @cols_req + ') )p  
                          pivot ( max(due) for col_due in (' + @cols_due + ') ) q
                          group by Type1, Col1
                       ) t
              '
print @query
execute sp_executesql @query;

我最初创建了一个新的临时表(#tmpD)，在其中插入了所有三个初始表中的行，以及一个名为的额外列'reqNr'，它显示了哪个表是源。

对于超过 3 个表的情况，您只需调整将所有行插入#tmpD表中的初始语句，以包括其他表。

您可以在此处查看工作演示。

推荐阅读