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)