SQL server中数据行转列

sql

参考网上他人博客，重温SQL——行转列，列转行

1.一个简单易懂的例子

CREATE TABLE [Inpours]
(
   [ID]                INT IDENTITY(1,1), 
   [UserName]          NVARCHAR(20),  --游戏玩家
    [CreateTime]        DATETIME,      --充值时间    
    [PayType]           NVARCHAR(20),  --充值类型    
    [Money]             DECIMAL,       --充值金额
    [IsSuccess]         BIT,           --是否成功 1表示成功， 0表示失败
    CONSTRAINT [PK_Inpours_ID] PRIMARY KEY(ID)
)
INSERT INTO Inpours SELECT '张三', '2010-05-01', '支付宝', 50, 1
INSERT INTO Inpours SELECT '张三', '2010-06-14', '支付宝', 50, 1
INSERT INTO Inpours SELECT '张三', '2010-06-14', '手机短信', 100, 1
INSERT INTO Inpours SELECT '李四', '2010-06-14', '手机短信', 100, 1
INSERT INTO Inpours SELECT '李四', '2010-07-14', '支付宝', 100, 1
INSERT INTO Inpours SELECT '王五', '2010-07-14', '工商银行卡', 100, 1
INSERT INTO Inpours SELECT '赵六', '2010-07-14', '建设银行卡', 100, 1

先把上述代码输入到查询框中，创建一个如下所示的数据库表Inpours：

该表中，同一个人有1~3条不等的行记录，每次的时间相同或不同。以下操作利用PIVOT函数，针对支付方式这一列，将所有的支付方式由行转列，使每个人同一个时间，仅有一条关于支付的记录。

SELECT
    UserName,
    CreateTime,
    [支付宝],
    [手机短信],
    [工商银行卡],
    [建设银行卡]
FROM
    (
        SELECT
            UserName,
            CONVERT (VARCHAR(10), CreateTime, 120) AS CreateTime,
            PayType,
            Money
        FROM
            Inpours
    ) P PIVOT (
        SUM (Money) FOR PayType IN (
            [支付宝],
            [手机短信],
            [工商银行卡],
            [建设银行卡]
        )
    ) AS T
ORDER BY
    CreateTime

得到如下的合并结果

2.PIVOT函数

依然以上一个例子为例解释PIVOT的用法。
原表中PayType字段中的值（支付宝、手机短信、工商银行卡、建设银行卡）由行记录变成了列名。在代码中的体现是PIVOT(PayType in (..填写字段里的值...))：

PIVOT (
        SUM (Money) FOR PayType IN (
            [支付宝],
            [手机短信],
            [工商银行卡],
            [建设银行卡]
        )
    )

最后，还用了一个聚合函数（求和，求平均，求方差等等），对同一个人、相同时间、同一列字段值做聚合操作，特别地，行转列后的字段值必须为数值型才能进行聚合。
抛开原理，总结一下PIVOT做行转列操作的思路。

SELECT
    ID————代表不同的人,
    Time————代表不同的时间,
    [xxx]————需要做行转列操作的那一列的值,
    [xxx]————需要做行转列操作的那一列的值,
    [xxx]————需要做行转列操作的那一列的值
FROM
    (
        SELECT
            ID————代表不同的人,
            Time————代表不同的时间,
            Column————代表需要做行转列操作的那一列列名,
            Value————代表行记录的值
        FROM
            Table————代表原表表名
    ) P(随便设置，代表上一个select结果的名字) PIVOT (
        SUM(Value) (做聚合，依照需要使用聚合函数) FOR Column(需要行转列列名) IN (
                [xxx]————需要做行转列操作的那一列的值,
                [xxx]————需要做行转列操作的那一列的值,
                [xxx]————需要做行转列操作的那一列的值
        )
    ) AS T(随便设置，该表PIVOT后面那一串)
ORDER BY
    CreateTime (所有记录按时间排序，可选)

更新，增加微软官方给出的PIVOT使用方法

SELECT <非透视的列>,
    [第一个透视的列] AS <列名称>,
    [第二个透视的列] AS <列名称>,
    ...
    [最后一个透视的列] AS <列名称>,
FROM
    (<生成数据的 SELECT 查询>)
    AS <源查询的别名>
PIVOT
(
    <聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
    IN ( [第一个透视的列], [第二个透视的列],
    ... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;

3.实际工作中遇到的情况

从后台数据库找数据时，遇到需要行转列情况最多的应该就是检验记录表了。同一人，同一个时间，一个检验单，就有N多条关于检验元素的记录，如图：

该表类似于1中的举例的简单表，但是明显的不同点在于LIS中检验表的检验值是varchar字符型，没法直接做聚合操作。因此，首先应该把检验值由字符型转换成可以进行加减乘除操作的数值型数据。
然而，实际中检验值形式繁多，有汉字、大于小于符号、小数，除此之外还有人工输入的误操作，比如空格、多个小数点、只有小数点没有数值等。
对于以上存在的问题：

• 把不符合规范的记录给排除
• 确定需要转成列字段的项目名（比如血小板、红细胞数、白细胞数等等）
  对于项目名的确定，可以根据研究需要的变量确定，也可以根据所有记录中各项目名频次确定（确保缺失率可接受）

不规范操作记录的排除

以下代码为暂时遇到的可出现不规范操作的情况（可补充）

Test_Result_Value IS NOT NULL
AND Test_Result_Value NOT LIKE '%-%'
AND Test_Result_Value NOT LIKE '%未做%'
AND Test_Result_Value NOT LIKE '%<%'
AND Test_Result_Value NOT LIKE '%>%'
AND Test_Result_Value != '.'
AND Test_Result_Value NOT LIKE '% %'

确保value为数值形式

--使用CASE WHEN (条件) THEN 操作1 ELSE 操作2 END
CASE
WHEN (
    PATINDEX(
        '%[^0-9|.|-|+]%',
        Test_Result_Value
    ) = 0  --确保value为由0~9组成的数值，可以有正负号和小数点
)
AND (
    len(Test_Result_Value) - len(
        REPLACE(Test_Result_Value, '.', '')
    ) < 2  --确保小数点个数不超过1个
) THEN
CAST (  
-- CAST(value AS decimal(10,2)) 将value转换成位数为10，保留两位有效数字的数值型，二次转换成浮点float型
    CAST (
        Test_Result_Value AS DECIMAL (10, 2)
    ) as float)
ELSE
    NULL
END

至此，完成varchar的检验值转换成float浮点数值，可使用聚合函数和PIVOT进行行转列操作。