How to Efficiently Convert SQL Server Table Columns into Rows?
Jan 21, 2025 pm 07:17 PMConvert 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:
select id, entityId, indicatorname, indicatorvalue from yourtable unpivot ( indicatorvalue for indicatorname in (Indicator1, Indicator2, Indicator3) ) unpiv;
Use CROSS APPLY and UNION ALL
Alternatively, multiple subqueries can be combined using 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);
Use CROSS APPLY and VALUES clauses
In supported versions of SQL Server, the CROSS APPLY and VALUES clauses can be used:
select id, entityid, indicatorname, indicatorvalue from yourtable cross apply ( values ('Indicator1', Indicator1), ('Indicator2', Indicator2), ('Indicator3', Indicator3), ('Indicator4', Indicator4) ) c (indicatorname, indicatorvalue);
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:
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;
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!

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Reduce the use of MySQL memory in Docker

How do you alter a table in MySQL using the ALTER TABLE statement?

How to solve the problem of mysql cannot open shared library

What is SQLite? Comprehensive overview

Run MySQl in Linux (with/without podman container with phpmyadmin)

Running multiple MySQL versions on MacOS: A step-by-step guide

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?

How do I configure SSL/TLS encryption for MySQL connections?
