SQL 行列转换应用

准备测试表和测试数据：

CREATE TABLE [dbo].[StateLog](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [State] [int] NULL,
    [CreateDate] [datetime] NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[StateLog]([State],[CreateDate]) VALUES ('0','2012-03-05')
INSERT INTO [dbo].[StateLog]([State],[CreateDate]) VALUES ('2','2012-03-05')
INSERT INTO [dbo].[StateLog]([State],[CreateDate]) VALUES ('3','2012-03-05')
INSERT INTO [dbo].[StateLog]([State],[CreateDate]) VALUES ('1','2012-03-05')
INSERT INTO [dbo].[StateLog]([State],[CreateDate]) VALUES ('1','2012-03-05')
INSERT INTO [dbo].[StateLog]([State],[CreateDate]) VALUES ('1','2012-03-05')
INSERT INTO [dbo].[StateLog]([State],[CreateDate]) VALUES ('1','2012-03-06')
INSERT INTO [dbo].[StateLog]([State],[CreateDate]) VALUES ('2','2012-03-06')
INSERT INTO [dbo].[StateLog]([State],[CreateDate]) VALUES ('2','2012-03-06')
INSERT INTO [dbo].[StateLog]([State],[CreateDate]) VALUES ('2','2012-03-06')

需要统计每天的总数以及各个状态有多少数量：

 

SQL语句如下：

select A.CreateDate,B.TotalQty,A.State0,A.State1,A.State2,A.State3 from
(
    --行列转换，核心
    SELECT [0][State0],[1][State1],[2][State2],[3][State3],CreateDate FROM
    (
        SELECT MAIN.[State],[StateLog].CreateDate
        FROM [StateLog] MAIN JOIN [StateLog] ON MAIN.CreateDate=[StateLog].CreateDate
        WHERE MAIN.[State] IN (0,1,2,3)
            AND [StateLog].[State] IN (0,1,2,3)
            AND MAIN.[State]<>[StateLog].[State]
        GROUP BY MAIN.ID,MAIN.[State],[StateLog].CreateDate
    ) G
    PIVOT
    (
        COUNT([State])
        FOR [State] IN ([0],[1],[2],[3])
    )P
) A,
(
    --计算总数
    SELECT COUNT(1) as TotalQty, CreateDate
    FROM [dbo].[StateLog]
    group by CreateDate
) B
where A.CreateDate=B.CreateDate