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 a 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

Try using the UNPIVOT function to convert the columns into rows like this:

select id, entityId,

  indicatorname,

  indicatorvalue

from yourtable

unpivot

(

  indicatorvalue

  for indicatorname in (Indicator1, Indicator2, Indicator3)

) unpiv;

Note: The column's datatype that you are unpivoting should be the same. Therefore, you may have to convert the datatypes prior to applying the unpivot.

• You can use CROSS APPLY with UNION ALL to convert the columns this way:

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 the version of SQL Server you can also use CROSS APPLY with the VALUES clause this way:

select id, entityid,

  indicatorname,

  indicatorvalue

from yourtable

cross apply

(

  values

  ('Indicator1', Indicator1),

  ('Indicator2', Indicator2),

  ('Indicator3', Indicator3),

  ('Indicator4', Indicator4)

) c (indicatorname, indicatorvalue);

• Ultimately, if you have 150 columns to unpivot and you don't want to hard-code the entire query, then you can generate the sql statement using dynamic SQL this way:

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, '')