具有动态日期的动态数据透视查询作为SQL Server中的列标题

小编典典

具有动态日期的动态数据透视查询作为SQL Server中的列标题

sql

这是SQL Server的实际结果集

+-----------+--------+------------------+
| Dates     | Orders | Cancelled Orders |
+-----------+--------+------------------+
|2016-01-17 |  100   |       50         |             
|2016-01-18 |  120   |       20         |                
|2016-01-20 |  150   |       30         |    
+-----------+--------+------------------+

我需要旋转表，如下所示

+----------+------------+------------+------------+
|Dates     | 2016-01-17 | 2016-01-18 | 2016-01-19 |
+----------+------------+------------+------------+
|Orders    |    100     | 120        |  150       | 
+----------+------------+------------+------------+
|Cancelled |            |            |            |
|Orders    |     50     |  20        |   30       |
+----------+------------+------------+------------+

有人可以给我写查询的建议吗？这里的日期必须动态地变化。

阅读 341

2021-03-17

共1个答案

小编典典

试试这个查询..它将为您提供帮助

select * into #tempp from(

select '2016-01-17' as DATES,100 ORDERS,50 CANCELED_ORDERS
UNION ALL
SELECT '2016-01-18',120,20
UNION ALL
SELECT '2016-01-20',150,30
)AS A

--SELECT * FROM #tempp

declare @pivotcols nvarchar(max),@unpivotcols nvarchar(max),@SQLQUERY NVARCHAR(MAX)
select @pivotcols=stuff((select ','+quotename(dates) from #tempp for xml path('')),1,1,'')
--select @pivotcols

select @unpivotcols=stuff((select ','+name from tempdb.sys.columns where object_id =
object_id('tempdb..#tempp') and name<>'DATES' for xml path('')),1,1,'')
--select @unpivotcols

SET @SQLQUERY=N'select * from (
SELECT * FROM #tempp
)as a
unpivot (AMOUNTS FOR Dates  in ('+@unpivotcols+N') 
) AS UNPI
PIVOT (MAX(AMOUNTS) FOR DATES IN ('+@pivotcols+N')
)AS A'

PRINT @SQLQUERY

EXEC SP_EXECUTESQL @SQLQUERY

输出将是这样。

    +-----------------+------------+------------+------------+
    |      Dates       | 2016-01-17 | 2016-01-18 | 2016-01-20|
    +-----------------+------------+------------+------------+
    | CANCELED_ORDERS |         50 |         20 |         30 |
    | ORDERS          |        100 |        120 |        150 |
    +-----------------+------------+------------+------------+

2021-03-17