Translate

Friday 8 April 2011

How to transpose table(change columns to rows).

One of most discussed question about TSQL algorithms is „How to transpose (change columns into rows) a table?” Most solutions based on static code which is adapted to your table. For every table, you need to write different code. Some authors propose using PIVOT statement, but it is not very handy to use this because PIVOT uses rows aggregation so avoiding this is always so easy.
Another question is "What if i need not only data change but gets columns/rows names too?” I you read my previous posts so you know which method we will use: XML-based.
This is how it looks:


DECLARE @xml XML ,
    @RowCount BIGINT
CREATE TABLE #Table
    (
      Column#1 INT ,
      Column2 NVARCHAR(MAX) ,
      Column3 DECIMAL(15, 2)
    )
CREATE TABLE #TempTable
    (
      RowID BIGINT ,
      CellId BIGINT ,
      Value NVARCHAR(MAX) ,
      ColumnName NVARCHAR(MAX)
    )
DECLARE @sSQl NVARCHAR(MAX)= 'SELECT (SELECT DISTINCT ColumnName FROM #TempTable WHERE CellId=Cell.CellId) as ColumnName,'
INSERT  INTO #Table
        SELECT  5 ,
                'Column_1_Test_String' ,
                99.99
INSERT  INTO #Table
        SELECT  9 ,
                'Column_2_Test_String' ,
                NULL


SET @xml = ( SELECT * ,
                    Row_Number() OVER ( ORDER BY ( SELECT   1
                                                 ) ) Rn
             FROM   #Table Row
           FOR
             XML AUTO,
                 ROOT('Root') ,
                 ELEMENTS XSINIL
           ) ;
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi),RC AS
   (SELECT COUNT(Row.value('.', 'nvarchar(MAX)')) [RowCount]
   FROM @xml.nodes('Root/Row') AS WTable(Row))
,c AS(
SELECT b.value('local-name(.)','nvarchar(max)') ColumnName,
  b.value('.[not(@xsi:nil = "true")]','nvarchar(max)') Value,
  b.value('../Rn[1]','nvarchar(max)') Rn,
  ROW_NUMBER() OVER (PARTITION BY b.value('../Rn[1]','nvarchar(max)') ORDER BY (SELECT 1)) Cell
FROM
 @xml.nodes('//Root/Row/*[local-name(.)!="Rn"]') a(b)

 ),Cols AS (
 SELECT  DISTINCT c.ColumnName,
  c.Cell
 FROM c
 )
 INSERT INTO #TempTable (CellId,RowID,Value,ColumnName)
 SELECT Cell,Rn,Value,REPLACE(c.ColumnName,'_x0023_','#')
 FROM c
 
SELECT  @sSQL = @sSQl
        + '(SELECT T2.Value FROM #Temptable T2 WHERE T2.CellId=Cell.CellID AND T2.Rowid='
        + CAST(T.RowId AS NVARCHAR) + ') AS Row_' + CAST(T.RowID AS NVARCHAR)
        + ','
FROM    ( SELECT DISTINCT
                    RowId
          FROM      #TempTable
        ) T
SET @sSQl = LEFT(@sSQL, LEN(@sSQL) - 1)
    + ' FROM (SELECT DISTINCT CellId FROM #TempTable) Cell'
EXECUTE sp_Executesql @sSQl
--here you will have your output
DROP TABLE #Table
DROP TABLE #TempTable
Description:
Lines 1-24: declaring variables and filling up source table:

Lines 25-33: writing source table as XML into XML variable










Lines 34-52: filling up ##Temptable (temporary table). It is used for debug purpose only. If you test this solution, you can avoid this and implement it as next CTE in chain of sub-queries.


Lines 54-63: Create dynamic SQL statement to prepare transposed output

Line 64: Executing dynamic SQL. Here we have next output:







Lines 66-67 Dropping temporary tables.

Here I described how you can easily transpose table without knowing nothing about source table structure. Based on code above,you can prepare stored procedure which will get XML generated from table and it will return transposed XML. In such way you can easily transpose any of your tables from any place in your code.

6 comments:

  1. Really helpful script. Thx!

    ReplyDelete
  2. It's really awesome


    Cheers,
    Venkatesan Prabu .J
    www.kaashivinfotech.com

    ReplyDelete
  3. Extremely impressed. Trying to sort out what it all means, because it does not quite work for me. The original table has column names like "Q#1 In which ward are you...", and the # is illegal for XML. I would be grateful for any hints.

    ReplyDelete
    Replies
    1. I have just updated script to handle hash symbol in column name. I hope it will help you.

      Delete
  4. Brilliant - works like a dream, and I am learning lots. Thank you very much

    ReplyDelete
  5. Hi,

    Just came across this, and it is super awesome! Thanks for writing. A quick question.

    Instead of declaring Column#1 as an INT declare it as a FLOAT.

    The returned table will have 5.000000000000000e+000 and 9.000000000000000e+000 as values.

    Is there a way to make that a little more user friendly?

    Martin

    ReplyDelete