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

User · Answer

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/

User · Answer

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  lt table gt    lt FieldToTranspose gt  --        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  lt  gt   columnToPivot         for xml path       1  1         set  queryPivot    SELECT  colsResult    SELECT                               quotename    columnToPivot                      from    tableToPivot   t                   where    columnToPivot    lt  gt                 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

User · Answer

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

User · Answer

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

User · Answer

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] SQL Server : Transpose rows to columns

Examples related to sql

Examples related to tsql

Examples related to sql-server-2005