这在Server中是可能的吗

Question

我有一张桌子，上面有事件和发生的日期：

表“事件”：

Name  Day
-----------
A     1
B     2
A     2
B     3

我需要输出列是基于查询输入的日期范围，其中的行是当天发生的事件，因此：

期望产出：

Day-1 Day-2 Day-3
-----------------
A     A     -
-     B     B

如果这是可能的话，谁能给我一个示例查询，可以根据日期范围生成这个输出。有各种各样的，我不知道如何处理这个问题在这里，就像一个未知的列数。

Answer 1

试试这个..。

表脚本和示例数据

CREATE TABLE [TableName](
    [Name] [nvarchar](50) NULL,
    [Day] [int] NULL
) 

INSERT [TableName] ([Name], [Day]) VALUES (N'A', 1)
INSERT [TableName] ([Name], [Day]) VALUES (N'B', 2)
INSERT [TableName] ([Name], [Day]) VALUES (N'A', 2)
INSERT [TableName] ([Name], [Day]) VALUES (N'B', 3)

查询(动态枢轴)

DECLARE @startDay AS INT;
DECLARE @endDay   AS INT;

SET @startDay = 1;
SET @endDay   = 3;  

DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename([day])
         FROM   TableName
         WHERE [Day] >= @startDay AND [Day] <= @endDay
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max) =  'SELECT '+ @cols +'
                                    FROM   (SELECT *, 
                                                   Dense_rank() OVER (ORDER BY NAME) AS dr 
                                            FROM   TableName) sq 
                                           PIVOT(Max([name]) 
                                                FOR [day] IN ('+ @cols +') ) pvt ';

EXECUTE(@query) 

输出

+--------+---+--------+
|   1    | 2 |   3    |
+--------+---+--------+
| A      | A | (null) |
| (null) | B | B      |
+--------+---+--------+

在线演示：http://www.sqlfiddle.com/#!18/c688b/8/0

如果您还想使用自定义列名，请尝试以下.

DECLARE @startDay AS INT;
DECLARE @endDay   AS INT;

SET @startDay = 1;
SET @endDay   = 3;  

DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename([day])
         FROM   TableName
         WHERE [Day] >= @startDay AND [Day] <= @endDay
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @colNames AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename([day]) + ' AS Days' + CONVERT(NVARCHAR(MAX), [day])
         FROM   TableName
         WHERE [Day] >= @startDay AND [Day] <= @endDay
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max) =  'SELECT '+ @colNames +'
                                    FROM   (SELECT *, 
                                                   Dense_rank() OVER (ORDER BY NAME) AS dr 
                                            FROM   TableName) sq 
                                           PIVOT(Max([name]) 
                                                FOR [day] IN ('+ @cols +') ) pvt ';
EXECUTE(@query) 

输出

+-------+-------+-------+
| Days1 | Days2 | Days3 |
+-------+-------+-------+
| A     | A     | NULL  |
| NULL  | B     | B     |
+-------+-------+-------+

在线演示：http://www.sqlfiddle.com/#!18/c688b/9/0

Answer 2

您可以使用条件聚合：

select max(case when day = 1 then name end) as day_1,
       max(case when day = 2 then name end) as day_2,
       max(case when day = 3 then name end) as day_3       
from t
group by name;

注意:这将返回NULL而不是-。我认为NULL更有意义。