MS SQL 2017建筑数据行转列问题及Measurement字段类型咨询

一、行转列问题的修正方案 #

你之前用Pivot时遇到的NULL行和数据丢失问题，大概率是因为源数据的首尾空格和Measurement字段的格式不兼容导致的。咱们一步步来解决：

1. 先清理数据并转换数值格式 #

首先要处理字段的首尾空格，同时把北欧格式的数字（千分位用.，小数点用,）转换成SQL能识别的标准格式：

WITH CleanedData AS (
    SELECT
        -- 清理key1和Unit字段的首尾空格
        LTRIM(RTRIM(key1)) AS key1,
        LTRIM(RTRIM(Unit)) AS Unit,
        -- 转换北欧数字格式：先移除千分位的点，再把逗号换成小数点
        CAST(REPLACE(REPLACE(Measurement, '.', ''), ',', '.') AS DECIMAL(18,4)) AS Measurement
    FROM YourOriginalTableName -- 替换成你的实际表名
)

2. 用条件聚合实现行转列（比Pivot更稳定） #

相比Pivot，条件聚合的写法更直观，也不容易因为格式问题出错。在上面的CTE基础上，我们可以生成你想要的结构：

SELECT
    key1,
    -- 对每个Unit列取对应的值，没有则显示NULL
    MAX(CASE WHEN Unit = 'Unit 1' THEN Measurement END) AS [Unit 1],
    MAX(CASE WHEN Unit = 'Unit 2' THEN Measurement END) AS [Unit 2],
    MAX(CASE WHEN Unit = 'Unit 3' THEN Measurement END) AS [Unit 3]
FROM CleanedData
GROUP BY key1
ORDER BY key1;

为什么之前的Pivot会出错？ #

• 源数据中key1或Unit字段的首尾空格，会导致Pivot分组时把'Unit 1'和' Unit 1 '当成不同的列，从而生成大量NULL行；
• Measurement字段的北欧格式没有转换，直接用varchar做聚合会导致数值逻辑错误，甚至丢失有效数据。

二、Measurement字段：Float还是Decimal？ #

作为北欧用户，结合建筑数据的场景，强烈推荐使用Decimal（或Numeric，两者在SQL Server中是等价的），原因如下：

• Decimal是精确数值类型：建筑数据涉及工程量、造价等需要精确计算的场景，Decimal不会像Float那样出现浮点精度丢失的问题（比如0.1在Float中是近似值，累加时会产生误差）；
• Float是近似数值类型：适合科学计算等对精度要求不高的场景，但完全不适合建筑这类需要精确值的业务；
• 关于格式：Decimal存储的是数值本身，北欧的数字格式（千分位.、小数点,）只是显示层面的需求，你可以在查询时再转换回该格式（比如用FORMAT函数），但存储时用Decimal才是正确的选择。

比如你可以定义字段为DECIMAL(18,4)，足够覆盖你示例中的所有数值（如5845.3000、156.8000等）。

内容的提问来源于stack exchange，提问作者tvviewer