How to Convert Columns to Rows in SQL Server (T-SQL)?

Several methods to convert columns into rows in SQL Server

This article describes how to convert column data in a SQL Server table into row data. Assume your table structure is as follows:

[ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150]

The goal is to convert the data into the following structure:

[ID] [EntityId] [IndicatorName] [IndicatorValue]

Example of target results:

<code>1 1 'Indicator1' 'Indicator 1 的值'
2 1 'Indicator2' 'Indicator 2 的值'
3 1 'Indicator3' 'Indicator 3 的值'
4 2 'Indicator1' 'Indicator 1 的值'</code>

Solution:

The following methods can achieve column-to-row conversion:

1. Use UNPIVOT function

The UNPIVOT function efficiently converts columns into rows:

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

Please ensure that the data types of the columns involved in the conversion are consistent.

2. Use CROSS APPLY and UNION ALL

Another way is to use CROSS APPLY and UNION ALL:

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

3. Use CROSS APPLY and VALUES (applicable to newer versions of SQL Server)

In newer SQL Server versions, you can use the CROSS APPLY and VALUES clauses:

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

4. Use dynamic SQL (suitable for large number of Indicator columns)

For large tables with a large number of Indicator columns, you can use dynamic SQL to automatically generate queries:

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(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = 'SELECT id, entityId, indicatorname, indicatorvalue
              FROM yourtable
              UNPIVOT
              (
                indicatorvalue
                FOR indicatorname IN (' + @colsUnpivot + ')
              ) u';

EXEC sp_executesql @query;

Which method you choose depends on the size of your table and your SQL Server version. For fewer columns, the first three methods are sufficient; for tables with a large number of Indicator columns, the dynamic SQL method is more efficient. Remember to replace yourtable with your actual table name.

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