SQL Server Columns to Rows

Question

Looking for elegant  or any  solution to convert columns to rows   Here is an example  I have a table with the following schema    ID   EntityID   Indicator1   Indicator2   Indicator3       Indicator150    Here is what I want to get as the result    ID   EntityId   IndicatorName   IndicatorValue    And the result values will be   1 1  Indicator1   Value of Indicator 1 for entity 1  2 1  Indicator2   Value of Indicator 2 for entity 1  3 1  Indicator3   Value of Indicator 3 for entity 1  4 2  Indicator1   Value of Indicator 1 for entity 2    And so on    Does this make sense  Do you have any suggestions on where to look and how to get it done in T-SQL

User · Answer

I needed a solution to convert columns to rows in Microsoft SQL Server, without knowing the colum names (used in trigger) and without dynamic sql (dynamic sql is too slow for use in a trigger).

I finally found this solution, which works fine:

SELECT
    insRowTbl.PK,
    insRowTbl.Username,
    attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
    attr.insRow.value('.', 'nvarchar(max)') as FieldValue 
FROM ( Select      
          i.ID as PK,
          i.LastModifiedBy as Username,
          convert(xml, (select i.* for xml raw)) as insRowCol
       FROM inserted as i
     ) as insRowTbl
CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow)

As you can see, I convert the row into XML (Subquery select i,* for xml raw, this converts all columns into one xml column)

Then I CROSS APPLY a function to each XML attribute of this column, so that I get one row per attribute.

Overall, this converts columns into rows, without knowing the column names and without using dynamic sql. It is fast enough for my purpose.

(Edit: I just saw Roman Pekar answer above, who is doing the same. I used the dynamic sql trigger with cursors first, which was 10 to 100 times slower than this solution, but maybe it was caused by the cursor, not by the dynamic sql. Anyway, this solution is very simple an universal, so its definitively an option).

I am leaving this comment at this place, because I want to reference this explanation in my post about the full audit trigger, that you can find here: https://stackoverflow.com/a/43800286/4160788

User · Answer

You can use the UNPIVOT function to convert the columns into rows   select id  entityId    indicatorname    indicatorvalue from yourtable unpivot     indicatorvalue   for indicatorname in  Indicator1  Indicator2  Indicator3    unpiv    Note  the datatypes of the columns you are unpivoting must be the same so you might have to convert the datatypes prior to applying the unpivot    You could also use CROSS APPLY with UNION ALL to convert the columns   select id  entityid    indicatorname    indicatorvalue from yourtable cross apply     select  Indicator1   Indicator1 union all   select  Indicator2   Indicator2 union all   select  Indicator3   Indicator3 union all   select  Indicator4   Indicator4    c  indicatorname  indicatorvalue     Depending on your version of SQL Server you could even use CROSS APPLY with the VALUES clause   select id  entityid    indicatorname    indicatorvalue from yourtable cross apply     values     Indicator1   Indicator1       Indicator2   Indicator2       Indicator3   Indicator3       Indicator4   Indicator4    c  indicatorname  indicatorvalue     Finally  if you have 150 columns to unpivot and you don t want to hard-code the entire query  then you could generate the sql statement using dynamic SQL   DECLARE  colsUnpivot AS NVARCHAR MAX       query  AS NVARCHAR MAX   select  colsUnpivot      stuff  select     quotename C column name             from information schema columns as C            where C table name    yourtable  and                  C column name like  Indicator              for xml path       1  1       set  query       select id  entityId          indicatorname          indicatorvalue      from yourtable      unpivot                indicatorvalue         for indicatorname in      colsunpivot            u   exec sp executesql  query

User · Answer

Just to help new readers  I ve created an example to better understand  bluefeet s answer about UNPIVOT    SELECT id          entityId          indicatorname          indicatorvalue   FROM  VALUES          1  1   Value of Indicator 1 for entity 1    Value of Indicator 2 for entity 1    Value of Indicator 3 for entity 1             2  1   Value of Indicator 1 for entity 2    Value of Indicator 2 for entity 2    Value of Indicator 3 for entity 2             3  1   Value of Indicator 1 for entity 3    Value of Indicator 2 for entity 3    Value of Indicator 3 for entity 3             4  2   Value of Indicator 1 for entity 4    Value of Indicator 2 for entity 4    Value of Indicator 3 for entity 4            AS Category ID  EntityId  Indicator1  Indicator2  Indicator3  UNPIVOT       indicatorvalue     FOR indicatorname IN  Indicator1  Indicator2  Indicator3    UNPIV

User · Answer

well If you have 150 columns then I think that UNPIVOT is not an option  So you could use xml trick   with CTE1 as       select ID  EntityID   select t   for xml raw  row    type  as Data     from temp1 as t    CTE2 as       select          C id  C EntityID           F C value  local-name       nvarchar 128    as IndicatorName           F C value       nvarchar max    as IndicatorValue     from CTE1 as c         outer apply c Data nodes  row      as F C    select   from CTE2 where IndicatorName like  Indicator     sql fiddle demo  You could also write dynamic SQL  but I like xml more - for dynamic SQL you have to have permissions to select data directly from table and that s not always an option   UPDATEAs there a big flame in comments  I think I ll add some pros and cons of xml dynamic SQL  I ll try to be as objective as I could and not mention elegantness and uglyness  If you got any other pros and cons  edit the answer or write in comments  cons   it s not as fast as dynamic SQL  rough tests gave me that xml is about 2 5 times slower that dynamic  it was one query on  250000 rows table  so this estimate is no way exact   You could compare it yourself if you want  here s sqlfiddle example  on 100000 rows it was 29s  xml  vs 14s  dynamic   may be it could be harder to understand for people not familiar with xpath    pros   it s the same scope as your other queries  and that could be very handy  A few examples come to mind  you could query inserted and deleted tables inside your trigger  not possible with dynamic at all   user don t have to have permissions on direct select from table  What I mean is if you have stored procedures layer and user have permissions to run sp  but don t have permissions to query tables directly  you still could use this query inside stored procedure  you could query table variable you have populated in your scope  to pass it inside the dynamic SQL you have to either make it temporary table instead or create type and pass it as a parameter into dynamic SQL   you can do this query inside the function  scalar or table-valued   It s not possible to use dynamic SQL inside the functions

User · Answer

Just because I did not see it mentioned   If 2016   here is yet another option to dynamically unpivot data without actually using Dynamic SQL   Example  Declare  YourTable Table   ID  varchar 50   Col1  varchar 50   Col2  varchar 50   Insert Into  YourTable Values    1  A   B     2  R   C     3  X   D    Select A  ID         Item    B  Key         Value   B  Value   From   YourTable A  Cross Apply   Select                    From  OpenJson  Select A   For JSON Path Without Array Wrapper                     Where  Key  not in   ID   Other   Columns   ToExclude                  B   Returns  ID  Item    Value 1   Col1    A 1   Col2    B 2   Col1    R 2   Col2    C 3   Col1    X 3   Col2    D

User · Answer

DECLARE  TableName varchar max  NULL SELECT  TableName COALESCE  TableName         t TABLE CATALOG      t TABLE SCHEMA     o Name   FROM sysindexes AS i   INNER JOIN sysobjects AS o ON i id   o id   INNER JOIN INFORMATION SCHEMA TABLES T ON T TABLE NAME o name  WHERE i indid  lt  2   AND OBJECTPROPERTY o id  IsMSShipped     0   AND i rowcnt  gt 350   AND o xtype    TF   ORDER BY o name ASC   print  tablename   You can get list of tables which has rowcounts  350   You can see at the solution list of table as row

User · Answer

The opposite of this is to flatten a column into a csv eg  SELECT STRING AGG   value       FROM STRING SPLIT  Akio Hiraku Kazuo

[sql] SQL Server : Columns to Rows

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to unpivot