tsql - SQL Server : Transpose rows to columns

Question

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.

Answer 1

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)