SQL 在sql server中高效地将行转换为列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15745042/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 14:34:58 来源:igfitidea点击:
Efficiently convert rows to columns in sql server
提问by tbag
I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is not very fast, and I need to deal with lot of records.
我正在寻找一种在 SQL Server 中将行转换为列的有效方法，听说 PIVOT 不是很快，我需要处理大量记录。
This is my example:
这是我的例子：
-------------------------------
| Id | Value | ColumnName |
-------------------------------
| 1 | John | FirstName |
| 2 | 2.4 | Amount |
| 3 | ZH1E4A | PostalCode |
| 4 | Fork | LastName |
| 5 | 857685 | AccountNumber |
-------------------------------
This is my result:
这是我的结果：
---------------------------------------------------------------------
| FirstName |Amount| PostalCode | LastName | AccountNumber |
---------------------------------------------------------------------
| John | 2.4 | ZH1E4A | Fork | 857685 |
---------------------------------------------------------------------
How can I build the result?
我怎样才能建立结果？
回答by Taryn
There are several ways that you can transform data from multiple rows into columns.
有多种方法可以将数据从多行转换为列。
Using PIVOT
使用 PIVOT
In SQL Server you can use the PIVOTfunction to transform the data from rows to columns:
在 SQL Server 中，您可以使用该PIVOT函数将数据从行转换为列：
select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
select value, columnname
from yourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;
See Demo.
见演示。
Pivot with unknown number of columnnames
具有未知数量的枢轴 columnnames
If you have an unknown number of columnnamesthat you want to transpose, then you can use dynamic SQL:
如果您columnnames要转置的数量未知，则可以使用动态 SQL：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
from yourtable
group by ColumnName, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p '
exec sp_executesql @query;
See Demo.
见演示。
Using an aggregate function
使用聚合函数
If you do not want to use the PIVOTfunction, then you can use an aggregate function with a CASEexpression:
如果不想使用该PIVOT函数，则可以使用带有CASE表达式的聚合函数：
select
max(case when columnname = 'FirstName' then value end) Firstname,
max(case when columnname = 'Amount' then value end) Amount,
max(case when columnname = 'PostalCode' then value end) PostalCode,
max(case when columnname = 'LastName' then value end) LastName,
max(case when columnname = 'AccountNumber' then value end) AccountNumber
from yourtable
See Demo.
见演示。
Using multiple joins
使用多个连接
This could also be completed using multiple joins, but you will need some column to associate each of the rows which you do not have in your sample data. But the basic syntax would be:
这也可以使用多个连接来完成，但您需要一些列来关联示例数据中没有的每一行。但基本语法是：
select fn.value as FirstName,
a.value as Amount,
pc.value as PostalCode,
ln.value as LastName,
an.value as AccountNumber
from yourtable fn
left join yourtable a
on fn.somecol = a.somecol
and a.columnname = 'Amount'
left join yourtable pc
on fn.somecol = pc.somecol
and pc.columnname = 'PostalCode'
left join yourtable ln
on fn.somecol = ln.somecol
and ln.columnname = 'LastName'
left join yourtable an
on fn.somecol = an.somecol
and an.columnname = 'AccountNumber'
where fn.columnname = 'Firstname'
回答by Bartosz X
as pivoting data is still a hot one I decided to add something form me. This is rather a method than just a single script but gives you much more possibilities. First of all There are 3 scripts you need to deploy:
1) User defined TABLE type [ColumnActionList] -> holds data as parameter
2) SP [proc_PivotPrepare] -> prepares our data
3) SP [proc_PivotExecute] -> execute the script
由于旋转数据仍然很热门，我决定添加一些东西。这是一种方法，而不仅仅是单个脚本，而是为您提供了更多的可能性。首先，您需要部署 3 个脚本：1) 用户定义的 TABLE 类型 [ ColumnActionList] -> 将数据作为参数 2) SP [ proc_PivotPrepare] -> 准备我们的数据 3) SP [ proc_PivotExecute] -> 执行脚本
CREATE TYPE [dbo].[ColumnActionList] AS TABLE(
[ID] [smallint] NOT NULL,
[ColumnName] [nvarchar](128) NOT NULL,
[Action] [nchar](1) NOT NULL
);
GO
CREATE PROCEDURE [dbo].[proc_PivotPrepare]
(
@DB_Name nvarchar(128),
@TableName nvarchar(128)
)
AS
----------------------------------------------------------------------------------------------------
-----| Author: Bartosz
----------------------------------------------------------------------------------------------------
SELECT @DB_Name = ISNULL(@DB_Name,db_name())
DECLARE @SQL_Code nvarchar(max)
DECLARE @MyTab TABLE (ID smallint identity(1,1), [Column_Name] nvarchar(128), [Type] nchar(1), [Set Action SQL] nvarchar(max));
SELECT @SQL_Code = 'SELECT [<| SQL_Code |>] = '' '' '
+ 'UNION ALL '
+ 'SELECT ''----------------------------------------------------------------------------------------------------'' '
+ 'UNION ALL '
+ 'SELECT ''-----| Declare user defined type [ID] / [ColumnName] / [PivotAction] '' '
+ 'UNION ALL '
+ 'SELECT ''----------------------------------------------------------------------------------------------------'' '
+ 'UNION ALL '
+ 'SELECT ''DECLARE @ColumnListWithActions ColumnActionList;'''
+ 'UNION ALL '
+ 'SELECT ''----------------------------------------------------------------------------------------------------'' '
+ 'UNION ALL '
+ 'SELECT ''-----| Set [PivotAction] (''''S'''' as default) to select dimentions and values '' '
+ 'UNION ALL '
+ 'SELECT ''-----|'''
+ 'UNION ALL '
+ 'SELECT ''-----| ''''S'''' = Stable column || ''''D'''' = Dimention column || ''''V'''' = Value column '' '
+ 'UNION ALL '
+ 'SELECT ''----------------------------------------------------------------------------------------------------'' '
+ 'UNION ALL '
+ 'SELECT ''INSERT INTO @ColumnListWithActions VALUES ('' + CAST( ROW_NUMBER() OVER (ORDER BY [NAME]) as nvarchar(10)) + '', '' + '''''''' + [NAME] + ''''''''+ '', ''''S'''');'''
+ 'FROM [' + @DB_Name + '].sys.columns '
+ 'WHERE object_id = object_id(''[' + @DB_Name + ']..[' + @TableName + ']'') '
+ 'UNION ALL '
+ 'SELECT ''----------------------------------------------------------------------------------------------------'' '
+ 'UNION ALL '
+ 'SELECT ''-----| Execute sp_PivotExecute with parameters: columns and dimentions and main table name'' '
+ 'UNION ALL '
+ 'SELECT ''----------------------------------------------------------------------------------------------------'' '
+ 'UNION ALL '
+ 'SELECT ''EXEC [dbo].[sp_PivotExecute] @ColumnListWithActions, ' + '''''' + @TableName + '''''' + ';'''
+ 'UNION ALL '
+ 'SELECT ''----------------------------------------------------------------------------------------------------'' '
EXECUTE SP_EXECUTESQL @SQL_Code;
GO
CREATE PROCEDURE [dbo].[proc_PivotExecute]
(
@ColumnListWithActions ColumnActionList ReadOnly
,@TableName nvarchar(128)
)
AS
--#######################################################################################################################
--###| Author: Bartosz
--#######################################################################################################################
--#######################################################################################################################
--###| Step 1 - Select our user-defined-table-variable into temp table
--#######################################################################################################################
IF OBJECT_ID('tempdb.dbo.#ColumnListWithActions', 'U') IS NOT NULL DROP TABLE #ColumnListWithActions;
SELECT * INTO #ColumnListWithActions FROM @ColumnListWithActions;
--#######################################################################################################################
--###| Step 2 - Preparing lists of column groups as strings:
--#######################################################################################################################
DECLARE @ColumnName nvarchar(128)
DECLARE @Destiny nchar(1)
DECLARE @ListOfColumns_Stable nvarchar(max)
DECLARE @ListOfColumns_Dimension nvarchar(max)
DECLARE @ListOfColumns_Variable nvarchar(max)
--############################
--###| Cursor for List of Stable Columns
--############################
DECLARE ColumnListStringCreator_S CURSOR FOR
SELECT [ColumnName]
FROM #ColumnListWithActions
WHERE [Action] = 'S'
OPEN ColumnListStringCreator_S;
FETCH NEXT FROM ColumnListStringCreator_S
INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ListOfColumns_Stable = ISNULL(@ListOfColumns_Stable, '') + ' [' + @ColumnName + '] ,';
FETCH NEXT FROM ColumnListStringCreator_S INTO @ColumnName
END
CLOSE ColumnListStringCreator_S;
DEALLOCATE ColumnListStringCreator_S;
--############################
--###| Cursor for List of Dimension Columns
--############################
DECLARE ColumnListStringCreator_D CURSOR FOR
SELECT [ColumnName]
FROM #ColumnListWithActions
WHERE [Action] = 'D'
OPEN ColumnListStringCreator_D;
FETCH NEXT FROM ColumnListStringCreator_D
INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ListOfColumns_Dimension = ISNULL(@ListOfColumns_Dimension, '') + ' [' + @ColumnName + '] ,';
FETCH NEXT FROM ColumnListStringCreator_D INTO @ColumnName
END
CLOSE ColumnListStringCreator_D;
DEALLOCATE ColumnListStringCreator_D;
--############################
--###| Cursor for List of Variable Columns
--############################
DECLARE ColumnListStringCreator_V CURSOR FOR
SELECT [ColumnName]
FROM #ColumnListWithActions
WHERE [Action] = 'V'
OPEN ColumnListStringCreator_V;
FETCH NEXT FROM ColumnListStringCreator_V
INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ListOfColumns_Variable = ISNULL(@ListOfColumns_Variable, '') + ' [' + @ColumnName + '] ,';
FETCH NEXT FROM ColumnListStringCreator_V INTO @ColumnName
END
CLOSE ColumnListStringCreator_V;
DEALLOCATE ColumnListStringCreator_V;
SELECT @ListOfColumns_Variable = LEFT(@ListOfColumns_Variable, LEN(@ListOfColumns_Variable) - 1);
SELECT @ListOfColumns_Dimension = LEFT(@ListOfColumns_Dimension, LEN(@ListOfColumns_Dimension) - 1);
SELECT @ListOfColumns_Stable = LEFT(@ListOfColumns_Stable, LEN(@ListOfColumns_Stable) - 1);
--#######################################################################################################################
--###| Step 3 - Preparing table with all possible connections between Dimension columns excluding NULLs
--#######################################################################################################################
DECLARE @DIM_TAB TABLE ([DIM_ID] smallint, [ColumnName] nvarchar(128))
INSERT INTO @DIM_TAB
SELECT [DIM_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'D';
DECLARE @DIM_ID smallint;
SELECT @DIM_ID = 1;
DECLARE @SQL_Dimentions nvarchar(max);
IF OBJECT_ID('tempdb.dbo.##ALL_Dimentions', 'U') IS NOT NULL DROP TABLE ##ALL_Dimentions;
SELECT @SQL_Dimentions = 'SELECT ID = ROW_NUMBER() OVER (ORDER BY ' + @ListOfColumns_Dimension + '), ' + @ListOfColumns_Dimension
+ ' INTO ##ALL_Dimentions '
+ ' FROM (SELECT DISTINCT' + @ListOfColumns_Dimension + ' FROM ' + @TableName
+ ' WHERE ' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @DIM_ID) + ' IS NOT NULL ';
SELECT @DIM_ID = @DIM_ID + 1;
WHILE @DIM_ID <= (SELECT MAX([DIM_ID]) FROM @DIM_TAB)
BEGIN
SELECT @SQL_Dimentions = @SQL_Dimentions + 'AND ' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @DIM_ID) + ' IS NOT NULL ';
SELECT @DIM_ID = @DIM_ID + 1;
END
SELECT @SQL_Dimentions = @SQL_Dimentions + ' )x';
EXECUTE SP_EXECUTESQL @SQL_Dimentions;
--#######################################################################################################################
--###| Step 4 - Preparing table with all possible connections between Stable columns excluding NULLs
--#######################################################################################################################
DECLARE @StabPos_TAB TABLE ([StabPos_ID] smallint, [ColumnName] nvarchar(128))
INSERT INTO @StabPos_TAB
SELECT [StabPos_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'S';
DECLARE @StabPos_ID smallint;
SELECT @StabPos_ID = 1;
DECLARE @SQL_MainStableColumnTable nvarchar(max);
IF OBJECT_ID('tempdb.dbo.##ALL_StableColumns', 'U') IS NOT NULL DROP TABLE ##ALL_StableColumns;
SELECT @SQL_MainStableColumnTable = 'SELECT ID = ROW_NUMBER() OVER (ORDER BY ' + @ListOfColumns_Stable + '), ' + @ListOfColumns_Stable
+ ' INTO ##ALL_StableColumns '
+ ' FROM (SELECT DISTINCT' + @ListOfColumns_Stable + ' FROM ' + @TableName
+ ' WHERE ' + (SELECT [ColumnName] FROM @StabPos_TAB WHERE [StabPos_ID] = @StabPos_ID) + ' IS NOT NULL ';
SELECT @StabPos_ID = @StabPos_ID + 1;
WHILE @StabPos_ID <= (SELECT MAX([StabPos_ID]) FROM @StabPos_TAB)
BEGIN
SELECT @SQL_MainStableColumnTable = @SQL_MainStableColumnTable + 'AND ' + (SELECT [ColumnName] FROM @StabPos_TAB WHERE [StabPos_ID] = @StabPos_ID) + ' IS NOT NULL ';
SELECT @StabPos_ID = @StabPos_ID + 1;
END
SELECT @SQL_MainStableColumnTable = @SQL_MainStableColumnTable + ' )x';
EXECUTE SP_EXECUTESQL @SQL_MainStableColumnTable;
--#######################################################################################################################
--###| Step 5 - Preparing table with all options ID
--#######################################################################################################################
DECLARE @FULL_SQL_1 NVARCHAR(MAX)
SELECT @FULL_SQL_1 = ''
DECLARE @i smallint
IF OBJECT_ID('tempdb.dbo.##FinalTab', 'U') IS NOT NULL DROP TABLE ##FinalTab;
SELECT @FULL_SQL_1 = 'SELECT t.*, dim.[ID] '
+ ' INTO ##FinalTab '
+ 'FROM ' + @TableName + ' t '
+ 'JOIN ##ALL_Dimentions dim '
+ 'ON t.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = 1) + ' = dim.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = 1);
SELECT @i = 2
WHILE @i <= (SELECT MAX([DIM_ID]) FROM @DIM_TAB)
BEGIN
SELECT @FULL_SQL_1 = @FULL_SQL_1 + ' AND t.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @i) + ' = dim.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @i)
SELECT @i = @i +1
END
EXECUTE SP_EXECUTESQL @FULL_SQL_1
--#######################################################################################################################
--###| Step 6 - Selecting final data
--#######################################################################################################################
DECLARE @STAB_TAB TABLE ([STAB_ID] smallint, [ColumnName] nvarchar(128))
INSERT INTO @STAB_TAB
SELECT [STAB_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName]
FROM #ColumnListWithActions WHERE [Action] = 'S';
DECLARE @VAR_TAB TABLE ([VAR_ID] smallint, [ColumnName] nvarchar(128))
INSERT INTO @VAR_TAB
SELECT [VAR_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName]
FROM #ColumnListWithActions WHERE [Action] = 'V';
DECLARE @y smallint;
DECLARE @x smallint;
DECLARE @z smallint;
DECLARE @FinalCode nvarchar(max)
SELECT @FinalCode = ' SELECT ID1.*'
SELECT @y = 1
WHILE @y <= (SELECT MAX([ID]) FROM ##FinalTab)
BEGIN
SELECT @z = 1
WHILE @z <= (SELECT MAX([VAR_ID]) FROM @VAR_TAB)
BEGIN
SELECT @FinalCode = @FinalCode + ', [ID' + CAST((@y) as varchar(10)) + '.' + (SELECT [ColumnName] FROM @VAR_TAB WHERE [VAR_ID] = @z) + '] = ID' + CAST((@y + 1) as varchar(10)) + '.' + (SELECT [ColumnName] FROM @VAR_TAB WHERE [VAR_ID] = @z)
SELECT @z = @z + 1
END
SELECT @y = @y + 1
END
SELECT @FinalCode = @FinalCode +
' FROM ( SELECT * FROM ##ALL_StableColumns)ID1';
SELECT @y = 1
WHILE @y <= (SELECT MAX([ID]) FROM ##FinalTab)
BEGIN
SELECT @x = 1
SELECT @FinalCode = @FinalCode
+ ' LEFT JOIN (SELECT ' + @ListOfColumns_Stable + ' , ' + @ListOfColumns_Variable
+ ' FROM ##FinalTab WHERE [ID] = '
+ CAST(@y as varchar(10)) + ' )ID' + CAST((@y + 1) as varchar(10))
+ ' ON 1 = 1'
WHILE @x <= (SELECT MAX([STAB_ID]) FROM @STAB_TAB)
BEGIN
SELECT @FinalCode = @FinalCode + ' AND ID1.' + (SELECT [ColumnName] FROM @STAB_TAB WHERE [STAB_ID] = @x) + ' = ID' + CAST((@y+1) as varchar(10)) + '.' + (SELECT [ColumnName] FROM @STAB_TAB WHERE [STAB_ID] = @x)
SELECT @x = @x +1
END
SELECT @y = @y + 1
END
SELECT * FROM ##ALL_Dimentions;
EXECUTE SP_EXECUTESQL @FinalCode;
--#######################################################################################################################
From executing the first query (by passing source DB and table name) you will get a pre-created execution query for the second SP, all you have to do is define is the column from your source: + Stable + Value (will be used to concentrate values based on that) + Dim (column you want to use to pivot by)
从执行第一个查询（通过传递源数据库和表名），您将获得第二个 SP 的预先创建的执行查询，您所要做的就是定义来自源的列：+ 稳定 + 值（将被使用基于此集中值）+ Dim（您要用于透视的列）
Names and datatypes will be defined automatically!
名称和数据类型将自动定义！
I cant recommend it for any production environments but does the job for adhoc BI requests.
我不能将它推荐用于任何生产环境，但它可以用于临时 BI 请求。
