How Can I Convert Columns to Rows in SQL Server?

Column to row conversion in SQL Server

Converting database columns into rows is an important technique in data analysis and reorganization. There are several ways to accomplish this conversion in SQL Server, each with its own advantages and considerations.

Method 1: UNPIVOT function

UNPIVOT function natively supports column to row conversion. The syntax of UNPIVOT is as follows:

UNPIVOT (indicatorvalue FOR indicatorname IN (Indicator1, Indicator2, Indicator3, ...)) AS unpiv

Example:

SELECT id, entityId,
  indicatorname,
  indicatorvalue
FROM yourtable
UNPIVOT
(
  indicatorvalue
  FOR indicatorname IN (Indicator1, Indicator2, Indicator3)
) unpiv;

Method 2: Use UNION ALL’s CROSS APPLY

CROSS APPLY using UNION ALL is another way to convert columns into rows. It iteratively combines rows based on the provided 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);

Method 3: Use CROSS APPLY of VALUES

This method utilizes the VALUES clause in CROSS APPLY to specify column names and values:

SELECT id, entityid,
  indicatorname,
  indicatorvalue
FROM yourtable
CROSS APPLY
(
  VALUES
  ('Indicator1', Indicator1),
  ('Indicator2', Indicator2),
  ('Indicator3', Indicator3),
  ('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);

Method 4: Dynamic SQL

For scenarios where you need to unwind a large number of columns, Dynamic SQL can automate this process:

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;

You can efficiently perform column-to-row conversions in SQL Server by choosing the most appropriate method for your specific scenario.

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