在SQL Server中的多个列上使用Pivot

I have a table for calendar events with following schema:

我有一个日历事件表，包含以下模式:

CREATE TABLE [dbo].[Calendar](
    [ID] [int] NOT NULL,
    [PersonCode] [int] NOT NULL,
    [Title] [nvarchar](50) NULL,
    [Description] [nvarchar](max) NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL,
    [NotifyOrder] [int] NULL)

 INSERT INTO [dbo].[Calendar]
       ([ID]
       ,[PersonCode]
       ,[Title]
       ,[Description]
       ,[StartDate]
       ,[EndDate]
       ,[NotifyOrder])
 VALUES
       (1
       ,1000
       ,'Calendar Event 1'
       ,'Description For Event 1'
       ,'2016/01/28 08:00'
       ,'2016/01/28 09:00'
       ,1)


INSERT INTO [dbo].[Calendar]
       ([ID]
       ,[PersonCode]
       ,[Title]
       ,[Description]
       ,[StartDate]
       ,[EndDate]
       ,[NotifyOrder])    
 VALUES
       (2
       ,1000
       ,'Calendar Event 2'
       ,'Description For Event 2'
       ,'2016/01/28 09:00'
       ,'2016/01/28 10:00'
       ,2)

INSERT INTO [dbo].[Calendar]
       ([ID]
       ,[PersonCode]
       ,[Title]
       ,[Description]
       ,[StartDate]
       ,[EndDate]
       ,[NotifyOrder])    
 VALUES
       (3
       ,1000
       ,'Calendar Event 3'
       ,'Description For Event 3'
       ,'2016/01/28 10:00'
       ,'2016/01/28 11:00'
       ,3)

I need to display rows in a format like this:

我需要以如下格式显示行:

 EventDate  |    1      |   Title1          |     2     |   Title2          |     3     |   Title3         |
 -----------------------------------------------------------------------------------------------------------
  2016-01-28|08:00-09:00 | Calendar Event 1 |09:00-10:00| Calendar Event 2  |10:00-11:00| Calendar Event 3 |

When I use pivot only on row number column (Here is NotifyOrder), the outpu is correct (without Title Columns) :

当我只在行号列(这里是NotifyOrder)上使用pivot时，outpu是正确的(没有标题列):

SELECT   EventDate
, [1], [2], [3], [4], [5], [6]
FROM 
(
 SELECT  NotifyOrder 
    ,CAST(StartDate AS Date) EventDate
    ,FORMAT(StartDate ,'hh:mm')  + ' - ' + FORMAT(EndDate ,'hh:mm')  EventTime 
 FROM [Calendar] 
 ) Notifies

 PIVOT 
(
    Max(EventTime) for NotifyOrder IN([1], [2], [3], [4], [5], [6])
) AS PV1

Result is:

结果是:

 EventDate      1           |   2               |   3               |   4   |   5       |   6
-----------------------------------------------------------------------------------------------
2016-01-28  08:00 - 09:00   |   09:00 - 10:00   |   10:00 - 11:00   | NULL  |   NULL    |   NULL

But when I add Pivot on Title Columns, the result is displayed in multi rows:

但当我在标题列上添加Pivot时，结果显示为多行:

    SELECT   EventDate
        , [1], [2], [3], [4], [5], [6]
        , [Title1], [Title2], [Title3], [Title4], [Title5], [Title6] 
    FROM 
    (
     SELECT  NotifyOrder 
            ,CAST(StartDate AS Date) EventDate
  ,FORMAT(StartDate ,'hh:mm')  + ' - ' + FORMAT(EndDate ,'hh:mm')  EventTime                ,Title, 'Title' + CAST(NotifyOrder AS Varchar(2)) AS EventTitle
     FROM [Calendar] 
     ) Notifies

     PIVOT 
    (
        Max(EventTime) for NotifyOrder IN([1], [2], [3], [4], [5], [6])
    ) AS PV1

     PIVOT 
     (
        MAX (Title) for EventTitle IN([Title1], [Title2], [Title3], [Title4], [Title5], [Title6])
     ) AS PV2

EventDate   1   2   3   4   5   6   Title1  Title2  Title3  Title4  Title5  Title6
2016-01-28  (null)  (null)  10:00 - 11:00   (null)  (null)  (null)  (null)  (null)  Calendar Event 3    (null)  (null)  (null)
2016-01-28  (null)  09:00 - 10:00   (null)  (null)  (null)  (null)  (null)  Calendar Event 2    (null)  (null)  (null)  (null)
2016-01-28  08:00 - 09:00   (null)  (null)  (null)  (null)  (null)  Calendar Event 1    (null)  (null)  (null)  (null)  (null)

Here is SQL Fiddle source:

下面是SQL Fiddle源代码:

http://sqlfiddle.com/#!6/a34a5/1

6 / a34a5/1 http://sqlfiddle.com/ !

1 个解决方案

#1

Using conditional aggregation and FORMAT:

使用条件聚合和格式:

SELECT PersonCode
 ,[1]      = MAX(CASE WHEN rn = 1 THEN EventTime END)
 ,[Title1] = MAX(CASE WHEN rn = 1 THEN Title END)
 ,[2]      = MAX(CASE WHEN rn = 2 THEN EventTime END)
 ,[Title2] = MAX(CASE WHEN rn = 2 THEN Title END)
 ,[3]      = MAX(CASE WHEN rn = 3 THEN EventTime END)
 ,[Title3] = MAX(CASE WHEN rn = 3 THEN Title END)
FROM (SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY PersonCode ORDER BY id)
          ,EventTime = FORMAT(StartDate, 'hh:mm') + '-' + FORMAT(EndDate, 'hh:mm')
      FROM #Calendar) AS sub
GROUP BY PersonCode;

LiveDemo

LiveDemo

Output:

输出:

╔════════════╦═════════════╦══════════════════╦═════════════╦══════════════════╦═════════════╦══════════════════╗
║ PersonCode ║      1      ║      Title1      ║      2      ║      Title2      ║      3      ║      Title3      ║
╠════════════╬═════════════╬══════════════════╬═════════════╬══════════════════╬═════════════╬══════════════════╣
║       1000 ║ 08:00-09:00 ║ Calendar Event 1 ║ 09:00-10:00 ║ Calendar Event 2 ║ 10:00-11:00 ║ Calendar Event 3 ║
╚════════════╩═════════════╩══════════════════╩═════════════╩══════════════════╩═════════════╩══════════════════╝

If you use SQL Server lower than 2012, change FORMAT to your custom code.

如果您使用的SQL Server低于2012，请将格式更改为自定义代码。

#1