[sql] SQL Server : Transpose rows to columns

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.

Another option that may be suitable in this situation is using XML

The XML option to transposing rows into columns is basically an optimal version of the PIVOT in that it addresses the dynamic column limitation. 

The XML version of the script addresses this limitation by using a combination of XML Path, dynamic T-SQL and some built-in functions (i.e. STUFF, QUOTENAME).

Vertical expansion

Similar to the PIVOT and the Cursor, newly added policies are able to be retrieved in the XML version of the script without altering the original script.

Horizontal expansion

Unlike the PIVOT, newly added documents can be displayed without altering the script.

Performance breakdown

In terms of IO, the statistics of the XML version of the script is almost similar to the PIVOT – the only difference is that the XML has a second scan of dtTranspose table but this time from a logical read – data cache.

You can find some more about these solutions (including some actual T-SQL exmaples) in this article: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

I had a slightly different requirement, whereby I had to selectively transpose columns into rows.

The table had columns:

create table tbl (ID, PreviousX, PreviousY, CurrentX, CurrentY)

I needed columns for Previous and Current, and rows for X and Y. A Cartesian product generated on a static table worked nicely, eg:

select 
    ID,
    max(case when metric='X' then PreviousX
        case when metric='Y' then PreviousY end) as Previous,
    max(case when metric='X' then CurrentX
        case when metric='Y' then CurrentY end) as Current
from tbl inner join
    /* Cartesian product - transpose by repeating row and 
    picking appropriate metric column for period */
    ( VALUES (1, 'X'), (2, 'Y')) AS x (sort, metric) ON 1=1
group by ID
order by ID, sort

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 has a PIVOT command that might be what you are looking for.

select * from Tag
pivot (MAX(Value) for TagID in ([A1],[A2],[A3],[A4])) as TagTime;

If the columns are not constant, you'll have to combine this with some dynamic SQL.

DECLARE @columns AS VARCHAR(MAX);
DECLARE @sql AS VARCHAR(MAX);

select @columns = substring((Select DISTINCT ',' + QUOTENAME(TagID) FROM Tag FOR XML PATH ('')),2, 1000);

SELECT @sql =

'SELECT *
FROM TAG
PIVOT 
(
  MAX(Value) 
  FOR TagID IN( ' + @columns + ' )) as TagTime;';

 execute(@sql);

Based on the solution from bluefeet here is a stored procedure that uses dynamic sql to generate the transposed table. It requires that all the fields are numeric except for the transposed column (the column that will be the header in the resulting table):

/****** Object:  StoredProcedure [dbo].[SQLTranspose]    Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for     transposing.
-- Parameters: @TableName - Table to transpose
--             @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
--        table and FIeldToTranspose should be written using single quotes
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose] 
  -- Add the parameters for the stored procedure here
  @TableName NVarchar(MAX) = '', 
  @FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  DECLARE @colsUnpivot AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @queryPivot  AS NVARCHAR(MAX),
  @colsPivot as  NVARCHAR(MAX),
  @columnToPivot as NVARCHAR(MAX),
  @tableToPivot as NVARCHAR(MAX), 
  @colsResult as xml

  select @tableToPivot = @TableName;
  select @columnToPivot = @FieldNameTranspose


  select @colsUnpivot = stuff((select ','+quotename(C.name)
       from sys.columns as C
       where C.object_id = object_id(@tableToPivot) and
             C.name <> @columnToPivot 
       for xml path('')), 1, 1, '')

  set @queryPivot = 'SELECT @colsResult = (SELECT  '','' 
                    + quotename('+@columnToPivot+')
                  from '+@tableToPivot+' t
                  where '+@columnToPivot+' <> ''''
          FOR XML PATH(''''), TYPE)'

  exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out

  select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')

  set @query 
    = 'select name, rowid, '+@colsPivot+'
        from
        (
          select '+@columnToPivot+' , name, value, ROW_NUMBER() over (partition by '+@columnToPivot+' order by '+@columnToPivot+') as rowid
          from '+@tableToPivot+'
          unpivot
          (
            value for name in ('+@colsUnpivot+')
          ) unpiv
        ) src
        pivot
        (
          sum(value)
          for '+@columnToPivot+' in ('+@colsPivot+')
        ) piv
        order by rowid'
  exec(@query)
END