SQL Server 2008 透视查询

Question

我的 SQL Server 2008 表中的数据如下所示：

我想将其旋转为如下所示this:

基本上我想对 ReadTime 和 Coater 进行分组，然后在 DataType 列上进行旋转。有人可以帮我用 T-SQL 来做到这一点吗？

Answer 1

也许这会有所帮助：

测试数据：

DECLARE @tbl TABLE
(
    ReadTime DATETIME,
    Coater VARCHAR(100),
    Value FLOAT,
    DateType VARCHAR(100)
)
INSERT INTO @tbl VALUES('2012-10-01 13:28:01','PBG11A-COATER',1,'FCDAOpside473')
INSERT INTO @tbl VALUES('2012-10-01 13:28:01','PBG11A-COATER',1,'FCDABackside473')
INSERT INTO @tbl VALUES('2012-10-01 13:28:01','PBG11A-COATER',0,'FCDAOpside472')
INSERT INTO @tbl VALUES('2012-10-01 13:28:01','PBG11A-COATER',0.9,'FCDABackside472')
INSERT INTO @tbl VALUES('2012-10-01 13:28:01','PBG11B-COATER',1,'FCDAOpside473')
INSERT INTO @tbl VALUES('2012-10-01 13:28:01','PBG11B-COATER',2,'FCDABackside472')
INSERT INTO @tbl VALUES('2012-10-01 13:28:01','PBG11B-COATER',2.9,'FCDAOpside472')
INSERT INTO @tbl VALUES('2012-10-01 13:28:01','PBG11B-COATER',0,'FCDABackside473')

INSERT INTO @tbl VALUES('2012-10-01 13:28:41','PBG11A-COATER',1,'FCDAOpside473')
INSERT INTO @tbl VALUES('2012-10-01 13:28:41','PBG11A-COATER',1,'FCDABackside473')
INSERT INTO @tbl VALUES('2012-10-01 13:28:41','PBG11A-COATER',0,'FCDAOpside472')
INSERT INTO @tbl VALUES('2012-10-01 13:28:41','PBG11A-COATER',0.9,'FCDABackside472')
INSERT INTO @tbl VALUES('2012-10-01 13:28:41','PBG11B-COATER',1,'FCDAOpside473')
INSERT INTO @tbl VALUES('2012-10-01 13:28:41','PBG11B-COATER',2,'FCDABackside472')
INSERT INTO @tbl VALUES('2012-10-01 13:28:41','PBG11B-COATER',2.9,'FCDAOpside472')
INSERT INTO @tbl VALUES('2012-10-01 13:28:41','PBG11B-COATER',0,'FCDABackside473')

查询：

SELECT
    *
FROM
(
    SELECT 
        ReadTime,
        Coater, 
        DateType,
        Value
    FROM @tbl AS tbl
) AS SourceTable
PIVOT
    (
        MIN(Value)
        FOR DateType IN 
                     (
                        [FCDAOpside472], 
                        [FCDAOpside473], 
                        [FCDABackside472], 
                        [FCDABackside473]
                     )
    )AS PivotTable
 ORDER BY 
    ReadTime