动态数据库查询，动态日期为列标题 [英] Dynamic pivot queries with dynamic dates as column header

问题描述

我需要如下图所示转动桌子





日期2016-01-17 2016-01-18 2016-01 -19



订单100 120 150



CanceledOrders 50 20 30



任何人都可以给我提出书面查询的建议吗？这里的日期必须是动态的。



我的尝试：



这是Sql实际结果集



---------- ------------------------

|日期|订单|取消订单|

----------- | ----- | --------------- |

| 2016-01-17 | 100 | 50 |

| 2016-01-18 | 120 | 20 |

| 2016-01-20 | 150 | 30 |

----------------------------------

解决方案

如果日期列的数量是可变的，则无法使用T-SQL语言解决此问题（无论如何，您可以使用C＃或VB.net中的CLR函数执行此操作）。 />


如果日期列的数量是固定的并且始终相同，您可以执行以下操作：



< pre lang =SQL> DECLARE @ date1 AS nvarchar （ 10 ）;
DECLARE @ date2 AS nvarchar （ 10 ）;
DECLARE @ date3 AS nvarchar （ 10 ）;
DECLARE @ query AS nvarchar （max）;

- 收集日期并将其设置为列标题（nvarchars）：
SET @ date1 =< first 发现日期> ;;
SET @ date2 =< second date 发​​现> ;;
SET @ date3 =< third date 发​​现> ;;

- 现在将查询创建为nvarchar（max）变量：
SET @ query = ' SELECT< a field>
，<另一个字段>
，SUM（<第一个要汇总的字段>）AS [' + @ date1 + ' ]
，SUM（<第二个要汇总的字段>）AS [' + @ date2 + ' ]
，SUM（<第三个要汇总的字段>）AS [' + @ date3 + ' ]
FROM [dbo]。[< source table name>]
WHERE（< any filter expressions here>）';

- 执行查询
EXEC sp_executesql @ query ;

I need to pivot the table as shown below


Dates 2016-01-17 2016-01-18 2016-01-19

Orders 100 120 150

CanceledOrders 50 20 30

Can anyone give me suggestions in writing query?Here Dates have to come dynamically.

What I have tried:

This is Sql actual Result Set

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

解决方案

If the number of date columns is variable there is no chance to solve this problem with T-SQL language (anyway you might do this with a CLR function in C# or VB.net).

If the number of date columns is fixed and always the same, you can do something like:

DECLARE @date1 AS nvarchar(10);
DECLARE @date2 AS nvarchar(10);
DECLARE @date3 AS nvarchar(10);
DECLARE @query AS nvarchar(max);

--Gather your dates and set them as column headers (nvarchars ):
SET @date1 = <first date found>;
SET @date2 = <second date found>;
SET @date3 = <third date found>;

--Now create your query as nvarchar(max) variable:
SET @query = 'SELECT <a field>
                   , <another field>
                   , SUM(<first field to summarize>) AS [' + @date1 + '] 
                   , SUM(<second field to summarize>) AS [' + @date2 + ']
                   , SUM(<third field to summarize>) AS [' + @date3 + ']
               FROM [dbo].[<source table name>]
              WHERE (<any filter expressions here>)';

--Execute the query
EXEC sp_executesql @query;

这篇关于动态数据库查询，动态日期为列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！