How to Efficiently Convert SQL Server Table Columns into Rows?

Convert SQL Server table columns to rows

Converting table columns to rows is a very useful data processing technique. This article describes several ways to accomplish this task in SQL Server.

UNPIVOT function

The UNPIVOT function explicitly converts columns into rows, allowing for flexible data reorganization. For example, to convert the 'Indicator1' to 'Indicator150' column in the example table schema:

<code class="language-sql">select id, entityId,
  indicatorname,
  indicatorvalue
from yourtable
unpivot
(
  indicatorvalue
  for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;</code>

Use CROSS APPLY and UNION ALL

Alternatively, multiple subqueries can be combined using CROSS APPLY and UNION ALL:

<code class="language-sql">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);</code>

Use CROSS APPLY and VALUES clauses

In supported versions of SQL Server, the CROSS APPLY and VALUES clauses can be used:

<code class="language-sql">select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  values
  ('Indicator1', Indicator1),
  ('Indicator2', Indicator2),
  ('Indicator3', Indicator3),
  ('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);</code>

Dynamic SQL for large number of columns

For tables with large numbers of columns that need to be untwisted, consider using dynamic SQL to programmatically generate the query:

<code class="language-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;</code>

The above is the detailed content of How to Efficiently Convert SQL Server Table Columns into Rows?. For more information, please follow other related articles on the PHP Chinese website!