How to Efficiently Convert Columns to Rows in SQL Server?

SQL Server: Convert columns to rows

In SQL Server, you may need to convert a table containing multiple indicator columns into a structure of rows for each indicator and its corresponding value. This can be achieved through several techniques.

UNPIVOT function

The UNPIVOT function is a straightforward way to convert columns into rows. It converts the specified columns into rows, with the original column names becoming new rows labeled "indicatorname". The value of the original column becomes the row labeled "indicatorvalue".

<code class="language-sql">SELECT id, entityId,
  indicatorname,
  indicatorvalue
FROM yourtable
UNPIVOT
(
  indicatorvalue
  FOR indicatorname IN (Indicator1, Indicator2, Indicator3)
) unpiv;</code>

Use UNION ALL’s CROSS APPLY

Another way is to combine CROSS APPLY with 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>

CROSS APPLY using VALUES clause

If your SQL Server version supports it, you can also utilize the VALUES clause with CROSS APPLY:

<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 tables with many indicator columns, manually generating the necessary SQL statements can be tedious. Dynamic SQL can automate this process:

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

These technologies provide flexible methods of converting columns into rows in SQL Server and can adapt to different table structures and data needs.

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