如何使用SQL Server枢

Question

我的用户表

CREATE TABLE [dbo].[User] 
(
    [Id] [int] IDENTITY(1,1) NOT NULL, 
    [Username] [varchar](255) NULL, 
    [Password] [varchar](255) NULL, 
    [RegisterDate] [smalldatetime] NULL 
) ON [PRIMARY] 
GO 

我的日志表

CREATE TABLE [dbo].[LogForLogin] 
(
    [Id] [int] IDENTITY(1,1) NOT NULL, 
    [UserId] [int] NULL, 
    [Login] [smalldatetime] NULL 
) ON [PRIMARY] 
GO 

我需要这样的：

RegisterDate | 14.06.2017 | 15.06.2017 | 16.06.2017 | 
-------------+------------+------------+------------+ 
14.06.2017 | 7   | 5   | 3 
15.06.2017 | 4   | 9   | 4 
16.06.2017 | 3   | 7   | 6 

列名需要是动态的。我希望注册日期的条目是并排的。

我想这一点：

Select * from (
    Select RegisterDate, [Login] from dbo.[User] 
     INNER JOIN dbo.[LogForLogin] 
     On [LogUserServerLogin].UserId = [User].Id ) a 
pivot (count([Login]) for RegisterDate in ([14.06.2017], [15.06.2017], [16.06.2017])) p 

BU给人一种错误：

Msg 8114, Level 16, State 1, Line 5 
Error converting data type nvarchar to smalldatetime. 
Msg 473, Level 16, State 1, Line 5 
The incorrect value "14.06.2017" is supplied in the PIVOT operator. 

Answer 1

可以使用旋转如下：

Select * from (
    Select u.RegisterDate, l.[Login] from dbo.[User] u 
     INNER JOIN dbo.[LogForLogin] l 
     On u.Id = l.UserId ) a 
    pivot (count(l.[Login]) for u.RegisterDate in ([14.06.2017], [15.06.2017], [16.06.2017])) p 

您可以尝试动态查询作为如下：

declare @cols1 varchar(max) 
declare @query nvarchar(max) 

Select @cols1 = stuff((select distinct ','+QuoteName(RegisterDate) from [User] for xml path('')),1,1,'') 

Set @Query = ' Select * from (
    Select u.RegisterDate, l.[Login] from dbo.[User] u 
     INNER JOIN dbo.[LogForLogin] l 
     On u.Id = l.UserId ) a 
    pivot (count(l.[Login]) for u.RegisterDate in (' + @cols1 + ')) p ' 

exec sp_executeSql @Query