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?

Answer 1

DECLARE @TableName nvarchar(50)
DECLARE column_to_row CURSOR FOR

--List of tables that we want to unpivot columns as row
SELECT DISTINCT t.name FROM sys.tables t
JOIN sys.schemas s ON t.schema_id=t.schema_id
WHERE t.name like '%_CT%'
AND s.name='cdc'

OPEN  column_to_row
FETCH NEXT FROM column_to_row INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @script nvarchar(max) = null
DECLARE @columns nvarchar(2000) = null

-- keep the table's column list
select @columns = COALESCE(@columns + ',','') + c.name from sys.tables  t
join sys.columns c on t.object_id = c.object_id
where t.name = @TableName

set @script = 'SELECT '+@columns+' FROM [cdc].['+@TableName+'] (nolock)'
--print (@script)
exec (@script)

FETCH NEXT FROM column_to_row INTO @TableName
END
CLOSE column_to_row
DEALLOCATE column_to_row

Here is another method for columns to rows, how many table and how many columns you have, is not important. Just set the parameters, and get the result. I wrote this, because sometimes I need the result of a table A (which is column result set), as fields of another table B (which has to be row fields). In that case I don't know how many fields I have set for my table B.

Answer 2

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.

UPDATE
As 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;

Answer 3

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;

Answer 4

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

Answer 5

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;