SQL Server:将行转置为列 [英] SQL Server : Transpose rows to columns

问题描述

除了将光标写入读取每一行并将其填充到列中之外，如果我需要将每一行转换为列，还有其他选择吗?

TimeSeconds TagID 值1378700244 A1 3.751378700245 A1 301378700304 A1 1.21378700305 A2 561378700344 A2 111378700345 A3 0.531378700364 A1 41378700365 A1 14.51378700384 A1 1441378700384 A4 10

列数不固定.

输出:我只是将 n/a 指定为该交叉点中没有数据的占位符.

TimeSec A1 A2 A3 A41378700244 3.75 不适用 不适用 不适用1378700245 30 不适用 不适用 不适用1378700304 1.2 不适用 不适用 不适用1378700305 不适用 56 不适用 不适用1378700344 不适用 11 不适用 不适用1378700345 不适用 不适用 0.53 不适用1378700364 不适用 不适用 不适用 41378700365 14.5 不适用 不适用 不适用1378700384 144 不适用 不适用 10

希望你能和我分享一些技巧.谢谢.

解决方案

如果 tagID 值预先知道，一种方法是使用条件聚合

SELECT TimeSeconds,COALESCE(MAX(CASE WHEN TagID = 'A1' THEN Value END), 'n/a') A1,COALESCE(MAX(CASE WHEN TagID = 'A2' THEN Value END), 'n/a') A2,COALESCE(MAX(CASE WHEN TagID = 'A3' THEN Value END), 'n/a') A3,COALESCE(MAX(CASE WHEN TagID = 'A4' THEN Value END), 'n/a') A4从表 1GROUP BY 时间秒

或者如果您可以使用 NULL 值而不是 'n/a'

SELECT TimeSeconds,MAX(CASE WHEN TagID = 'A1' THEN Value END) A1,MAX(CASE WHEN TagID = 'A2' THEN Value END) A2,MAX(CASE WHEN TagID = 'A3' THEN Value END) A3,MAX(CASE WHEN TagID = 'A4' THEN Value END) A4从表 1GROUP BY 时间秒

或使用 PIVOT

SELECT TimeSeconds, A1, A2, A3, A4从(SELECT TimeSeconds、TagID、值从表 1) s枢(MAX(Value) FOR TagID IN (A1, A2, A3, A4)) p

输出(带有NULLs):

<前>时间秒 A1 A2 A3 A4----------- ------- ------ ----- -----1378700244 3.75 NULL NULL NULL1378700245 30.00 NULL NULL NULL1378700304 1.20 NULL NULL NULL1378700305 NULL 56.00 NULL NULL1378700344 NULL 11.00 NULL NULL1378700345 NULL NULL 0.53 NULL1378700364 4.00 NULL NULL NULL1378700365 14.50 NULL NULL NULL1378700384 144.00 NULL NULL 10.00

<小时>

如果您必须动态计算 TagID 值，请使用动态 SQL

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(TagID)从表 1按 1 订购FOR XML PATH(''), 类型).value('.', 'NVARCHAR(MAX)'),1,1,'')SET @sql = '选择时间秒数，' + @cols + '从(SELECT TimeSeconds、TagID、值从表 1) s枢(MAX(Value) FOR TagID IN (' + @cols + ')) p'执行(@sql)

Apart from writing the cursor reading each rows and populating it into columns, any other alternative if I need to transpose each rows into columns ?

TimeSeconds TagID Value
1378700244  A1    3.75
1378700245  A1    30
1378700304  A1    1.2
1378700305  A2    56
1378700344  A2    11
1378700345  A3    0.53
1378700364  A1    4
1378700365  A1    14.5
1378700384  A1    144
1378700384  A4    10

The number of columns are not fixed.

Output : I just assigned n/a as a placeholder for no data in that intersection.

TimeSec     A1    A2    A3    A4
1378700244  3.75  n/a   n/a   n/a
1378700245  30    n/a   n/a   n/a
1378700304  1.2   n/a   n/a   n/a
1378700305  n/a   56    n/a   n/a
1378700344  n/a   11    n/a   n/a
1378700345  n/a   n/a   0.53  n/a
1378700364  n/a   n/a   n/a   4
1378700365  14.5  n/a   n/a   n/a
1378700384  144   n/a   n/a   10

Hope you can share with me some tips. Thanks.

解决方案

One way to do it if tagID values are known upfront is to use conditional aggregation

SELECT TimeSeconds,
       COALESCE(MAX(CASE WHEN TagID = 'A1' THEN Value END), 'n/a') A1,
       COALESCE(MAX(CASE WHEN TagID = 'A2' THEN Value END), 'n/a') A2,
       COALESCE(MAX(CASE WHEN TagID = 'A3' THEN Value END), 'n/a') A3,
       COALESCE(MAX(CASE WHEN TagID = 'A4' THEN Value END), 'n/a') A4
  FROM table1
 GROUP BY TimeSeconds

or if you're OK with NULL values instead of 'n/a'

SELECT TimeSeconds,
       MAX(CASE WHEN TagID = 'A1' THEN Value END) A1,
       MAX(CASE WHEN TagID = 'A2' THEN Value END) A2,
       MAX(CASE WHEN TagID = 'A3' THEN Value END) A3,
       MAX(CASE WHEN TagID = 'A4' THEN Value END) A4
  FROM table1
 GROUP BY TimeSeconds

or with PIVOT

SELECT TimeSeconds, A1, A2, A3, A4
  FROM
(
  SELECT TimeSeconds, TagID, Value
    FROM table1
) s
PIVOT
(
  MAX(Value) FOR TagID IN (A1, A2, A3, A4)
) p

Output (with NULLs):

TimeSeconds A1      A2     A3    A4
----------- ------- ------ ----- -----
1378700244  3.75    NULL   NULL  NULL
1378700245  30.00   NULL   NULL  NULL
1378700304  1.20    NULL   NULL  NULL
1378700305  NULL    56.00  NULL  NULL
1378700344  NULL    11.00  NULL  NULL
1378700345  NULL    NULL   0.53  NULL
1378700364  4.00    NULL   NULL  NULL
1378700365  14.50   NULL   NULL  NULL
1378700384  144.00  NULL   NULL  10.00

---

If you have to figure TagID values out dynamically then use dynamic SQL

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(TagID)
            FROM Table1
            ORDER BY 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @sql = 'SELECT TimeSeconds, ' + @cols + '
              FROM
            (
              SELECT TimeSeconds, TagID, Value
                FROM table1
            ) s
            PIVOT
            (
              MAX(Value) FOR TagID IN (' + @cols + ')
            ) p'

EXECUTE(@sql)

这篇关于SQL Server:将行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！