Simple way to transpose columns and rows in SQL

Question

How do I simply switch columns with rows in SQL  Is there any simple command to transpose   ie turn this result           Paul    John    Tim     Eric Red     1       5       1       3 Green   8       4       3       5 Blue    2       2       9       1   into this           Red    Green   Blue Paul    1       8       2 John    5       4       2 Tim     1       3       9 Eric    3       5       1   PIVOT seems too complex for this scenario

User · Answer

This way Convert all Data From Filelds Columns  In Table To Record  Row    Declare  TableName   nvarchar  128  Declare  ExecStr    nvarchar max  Declare  Where      nvarchar max  Set  TableName    myTableName  --Enter Filtering If Exists Set  Where       --Set  ExecStr   N Select   From   quotename  TableName   Where --Exec  ExecStr   Drop Table If Exists  tmp Col2Row  Create Table  tmp Col2Row  Field Name nvarchar 128  Not Null  Field Value nvarchar max  Null    Set  ExecStr   N  Insert Into  tmp Col2Row  Field Name   Field Value    Select  ExecStr     Select N Select     C name      Convert nvarchar max    quotename C name       From     quotename  TableName   Where Char 10    Union All            from sys columns as C          where  C object id   object id  TableName             for xml path      Select  ExecStr   Left  ExecStr Len  ExecStr -Len   Union All     --Print  ExecStr Exec   ExecStr   Select   From  tmp Col2Row Go

User · Answer

Based on this 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  --                                               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   You can test it with the table provided with this command   exec SQLTranspose  yourTable    color

User · Answer

This normally requires you to know ALL the column AND row labels beforehand   As you can see in the query below  the labels are all listed in their entirely in both the UNPIVOT and the  re PIVOT operations   MS SQL Server 2012 Schema Setup   create table tbl       color varchar 10   Paul int  John int  Tim int  Eric int   insert tbl select       Red   1  5  1  3 union all select      Green   8  4  3  5 union all select      Blue   2  2  9  1    Query 1   select   from tbl unpivot  value for name in   Paul   John   Tim   Eric    up pivot  max value  for color in   Red   Green   Blue    p   Results     NAME   RED   GREEN   BLUE   -----------------------------   Eric     3       5      1     John     5       4      2     Paul     1       8      2      Tim     1       3      9     Additional Notes    Given a table name  you can determine all the column names from sys columns or FOR XML trickery using local-name    You can also build up the list of distinct colors  or values for one column  using FOR XML  The above can be combined into a dynamic sql batch to handle any table

User · Answer

I m doing UnPivot first and storing the results in CTE and using the CTE in Pivot operation   Demo   with cte as   select  Paul  as Name  color  Paul as Value   from yourTable  union all  select  John  as Name  color  John as Value   from yourTable  union all  select  Tim  as Name  color  Tim as Value   from yourTable  union all  select  Eric  as Name  color  Eric as Value   from yourTable     select Name   Red    Green    Blue  from   select   from cte   as src pivot      max Value    for color IN   Red    Green    Blue     as Dtpivot

User · Answer

I was able to use Paco Zarate s solution and it works beautifully   I did have to add one line   SET ANSI WARNINGS ON    but that may be something unique to the way I used it or called it   There is a problem with my usage and I hope someone can help me with it   The solution works only with an actual SQL table   I tried it with a temporary table and also an in-memory  declared  table but it doesn t work with those   So in my calling code I create a table on my SQL database and then call SQLTranspose   Again  it works great   It s just what I want   Here s my problem   In order for the overall solution to be truly dynamic I need to create that table where I temporarily store the prepared information that I m sending to SQLTranspose  on the fly   and then delete that table once SQLTranspose is called   The table deletion is presenting a problem with my ultimate implementation plan   The code needs to run from an end-user application  a button on a Microsoft Access form menu    When I use this SQL process  create a SQL table  call SQLTranspose  delete SQL table  the end user application hits an error because the SQL account used does not have the rights to drop a table     So I figure there are a few possible solutions    Find a way to make SQLTranspose work with a temporary table or a declared table variable  Figure out another method for the transposition of rows and columns that doesn t require an actual SQL table  Figure out an appropriate method of allowing the SQL account used by my end users  to drop a table   It s a single shared SQL account coded into my Access application   It appears that permission is a dbo-type privilege that cannot be granted    I recognize that some of this may warrant another  separate thread and question   However  since there is a possibility that one solution may be simply a different way to do the transposing of rows and columns I ll make my first post here in this thread     EDIT  I also did replace sum value  with max value  in the 6th line from the end  as Paco suggested   EDIT   I figured out something that works for me   I don t know if it s the best answer or not     I have a read-only user account that is used to execute strored procedures and therefore generate reporting output from a database   Since the SQLTranspose function I created will only work with a  legitimate  table  not a declared table and not a temporary table  I had to figure out a way for a read-only user account to create  and then later delete  a table     I reasoned that for my purposes it s okay for the user account to be allowed to create a table   The user still could not delete the table though  My solution was to create a schema where the user account is authorized   Then whenever I create  use  or delete that table refer it with the schema specified   I first issued this command from a  sa  or  sysadmin  account  CREATE SCHEMA ro AUTHORIZATION   When any time I refer to my  tmpoutput  table I specify it like this example   drop table ro tmpoutput

User · Answer

I d like to point out few more solutions to transposing columns and rows in SQL   The first one is - using CURSOR  Although the general consensus in the professional community is to stay away from SQL Server Cursors  there are still instances whereby the use of cursors is recommended  Anyway  Cursors present us with another option to transpose rows into columns    Vertical expansion  Similar to the PIVOT  the cursor has the dynamic capability to append more rows as your dataset expands to include more policy numbers  Horizontal expansion  Unlike the PIVOT  the cursor excels in this area as it is able to expand to include newly added document  without altering the script  Performance breakdown  The major limitation of transposing rows into columns using CURSOR is a disadvantage that is linked to using cursors in general     they come at significant performance cost  This is because the Cursor generates a separate query for each FETCH NEXT operation    Another solution of transposing rows into columns is by using XML   The XML solution 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

I like to share the code i m using to transpose a splited text based on  bluefeet answer  In this aproach i m implemented as a procedure in MS SQL 2005  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO --                                               -- Author       ELD  -- Create date  May  5 2016  -- Description  Transpose from rows to columns the user split function  --                                               CREATE PROCEDURE TransposeSplit  InputToSplit VARCHAR 8000        Delimeter VARCHAR 8000        AS BEGIN     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     tempSplitedTable       SELECT  columnToPivot    col number       CREATE TABLE  tempSplitedTable           col number INT          col value VARCHAR 8000                 INSERT INTO  tempSplitedTable           col number          col value               SELECT ROW NUMBER   OVER               ORDER BY                       SELECT 100                                     AS RowNumber          item     FROM  DB   ESCHEME   fnSplit   InputToSplit   Delimeter       SELECT  colsUnpivot   STUFF                   SELECT       quotename C NAME                  FROM  tempdb  sys columns AS C                 WHERE C object id   object id  tempdb       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                     MAX value            for      columnToPivot     in       colsPivot                piv         order by rowid       EXEC   query       DROP TABLE  tempSplitedTable END GO   I m mixing this solution with the information about howto order rows without order by  SQLAuthority com  and the split function on MSDN  social msdn microsoft com   When you execute the prodecure  DECLARE  RC int DECLARE  InputToSplit varchar MAX  DECLARE  Delimeter varchar 1   set  InputToSplit    hello beautiful world  set  Delimeter        EXECUTE  RC    TransposeSplit       InputToSplit     Delimeter GO   you obtaint the next result    name       rowid  1      2          3   col value  1      hello  beautiful  world

User · Answer

Adding to  Paco Zarate s terrific answer above  if you want to transpose a table which has multiple types of columns  then add this to the end of line 39  so it only transposes int columns   and C system type id   56   --56   type int   Here is the full query that is being changed   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 and C system type id   56    --56   type int for xml path       1  1        To find other system type id s  run this   select name  system type id from sys types order by name

User · Answer

There are several ways that you can transform this data  In your original post  you stated that PIVOT seems too complex for this scenario  but it can be applied very easily using both the UNPIVOT and PIVOT functions in SQL Server    However  if you do not have access to those functions this can be replicated using UNION ALL to UNPIVOT and then an aggregate function with a CASE statement to PIVOT   Create Table   CREATE TABLE yourTable  color  varchar 5    Paul  int   John  int   Tim  int   Eric  int    INSERT INTO yourTable       color    Paul    John    Tim    Eric   VALUES       Red   1  5  1  3         Green   8  4  3  5         Blue   2  2  9  1     Union All  Aggregate and CASE Version   select name    sum case when color    Red  then value else 0 end  Red    sum case when color    Green  then value else 0 end  Green    sum case when color    Blue  then value else 0 end  Blue from     select color  Paul value   Paul  name   from yourTable   union all   select color  John value   John  name   from yourTable   union all   select color  Tim value   Tim  name   from yourTable   union all   select color  Eric value   Eric  name   from yourTable   src group by name   See SQL Fiddle with Demo  The UNION ALL performs the UNPIVOT of the data by transforming the columns Paul  John  Tim  Eric into separate rows  Then you apply the aggregate function sum   with the case statement to get the new columns for each color   Unpivot and Pivot Static Version   Both the UNPIVOT and PIVOT functions in SQL server make this transformation much easier   If you know all of the values that you want to transform  you can hard-code them into a static version to get the result   select name   Red    Green    Blue  from     select color  name  value   from yourtable   unpivot         value for name in  Paul  John  Tim  Eric      unpiv   src pivot     sum value    for color in   Red    Green    Blue     piv   See SQL Fiddle with Demo  The inner query with the UNPIVOT performs the same function as the UNION ALL  It takes the list of columns and turns it into rows  the PIVOT then performs the final transformation into columns   Dynamic Pivot Version   If you have an unknown number of columns  Paul  John  Tim  Eric in your example  and then an unknown number of colors to transform you can use dynamic sql to generate the list to UNPIVOT and then PIVOT   DECLARE  colsUnpivot AS NVARCHAR MAX        query  AS NVARCHAR MAX        colsPivot as  NVARCHAR MAX   select  colsUnpivot   stuff  select     quotename C name           from sys columns as C          where C object id   object id  yourtable   and                C name  lt  gt   color           for xml path       1  1       select  colsPivot   STUFF  SELECT                               quotename color                      from yourtable t             FOR XML PATH      TYPE               value       NVARCHAR MAX              1 1       set  query       select name     colsPivot         from                 select color  name  value         from yourtable         unpivot                     value for name in     colsUnpivot              unpiv         src       pivot                 sum value          for color in     colsPivot            piv   exec  query    See SQL Fiddle with Demo  The dynamic version queries both yourtable and then the sys columns table to generate the list of items to UNPIVOT and PIVOT   This is then added to a query string to be executed  The plus of the dynamic version is if you have a changing list of colors and or names this will generate the list at run-time   All three queries will produce the same result     NAME   RED   GREEN   BLUE   -----------------------------   Eric     3       5      1     John     5       4      2     Paul     1       8      2      Tim     1       3      9

[sql] Simple way to transpose columns and rows in SQL?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to pivot