Apart from writing the cursor reading each rows and populating it into columns, any other alternative if I need to transpose each rows into columns ?
除了将光标写入每行并将其填充到列中之外，如果我需要将每行转置为列，还有其他任何替代方法吗？
TimeSeconds TagID Value
1378700244 A1 3.75
1378700245 A1 30
1378700304 A1 1.2
1378700305 A2 56
1378700344 A2 11
1378700345 A3 0.53
1378700364 A1 4
1378700365 A1 14.5
1378700384 A1 144
1378700384 A4 10
The number of columns are not fixed.
列数不固定。
Output : I just assigned n/a as a placeholder for no data in that intersection.
输出：我刚刚将n / a指定为占位符，表示该交叉点中没有数据。
TimeSec A1 A2 A3 A4
1378700244 3.75 n/a n/a n/a
1378700245 30 n/a n/a n/a
1378700304 1.2 n/a n/a n/a
1378700305 n/a 56 n/a n/a
1378700344 n/a 11 n/a n/a
1378700345 n/a n/a 0.53 n/a
1378700364 n/a n/a n/a 4
1378700365 14.5 n/a n/a n/a
1378700384 144 n/a n/a 10
Hope you can share with me some tips. Thanks.
希望您能与我分享一些提示。谢谢。
5 个解决方案
#1
34
One way to do it if tagID values are known upfront is to use conditional aggregation
如果预先知道tagID值，则使用条件聚合的一种方法是使用条件聚合
SELECT TimeSeconds,
COALESCE(MAX(CASE WHEN TagID = 'A1' THEN Value END), 'n/a') A1,
COALESCE(MAX(CASE WHEN TagID = 'A2' THEN Value END), 'n/a') A2,
COALESCE(MAX(CASE WHEN TagID = 'A3' THEN Value END), 'n/a') A3,
COALESCE(MAX(CASE WHEN TagID = 'A4' THEN Value END), 'n/a') A4
FROM table1
GROUP BY TimeSeconds
or if you're OK with NULL values instead of 'n/a'
或者如果你没有使用NULL值而不是'n / a'
SELECT TimeSeconds,
MAX(CASE WHEN TagID = 'A1' THEN Value END) A1,
MAX(CASE WHEN TagID = 'A2' THEN Value END) A2,
MAX(CASE WHEN TagID = 'A3' THEN Value END) A3,
MAX(CASE WHEN TagID = 'A4' THEN Value END) A4
FROM table1
GROUP BY TimeSeconds
or with PIVOT
或者是PIVOT
SELECT TimeSeconds, A1, A2, A3, A4
FROM
(
SELECT TimeSeconds, TagID, Value
FROM table1
) s
PIVOT
(
MAX(Value) FOR TagID IN (A1, A2, A3, A4)
) p
Output (with NULLs):
输出（带NULL）：
TimeSeconds A1 A2 A3 A4 ----------- ------- ------ ----- ----- 1378700244 3.75 NULL NULL NULL 1378700245 30.00 NULL NULL NULL 1378700304 1.20 NULL NULL NULL 1378700305 NULL 56.00 NULL NULL 1378700344 NULL 11.00 NULL NULL 1378700345 NULL NULL 0.53 NULL 1378700364 4.00 NULL NULL NULL 1378700365 14.50 NULL NULL NULL 1378700384 144.00 NULL NULL 10.00
If you have to figure TagID values out dynamically then use dynamic SQL
如果必须动态计算TagID值，请使用动态SQL
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(TagID)
FROM Table1
ORDER BY 1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @sql = 'SELECT TimeSeconds, ' + @cols + '
FROM
(
SELECT TimeSeconds, TagID, Value
FROM table1
) s
PIVOT
(
MAX(Value) FOR TagID IN (' + @cols + ')
) p'
EXECUTE(@sql)
#2
9
SQL Server has a PIVOT command that might be what you are looking for.
SQL Server有一个可能正在寻找的PIVOT命令。
select * from Tag
pivot (MAX(Value) for TagID in ([A1],[A2],[A3],[A4])) as TagTime;
If the columns are not constant, you'll have to combine this with some dynamic SQL.
如果列不是常量，则必须将其与某些动态SQL结合使用。
DECLARE @columns AS VARCHAR(MAX);
DECLARE @sql AS VARCHAR(MAX);
select @columns = substring((Select DISTINCT ',' + QUOTENAME(TagID) FROM Tag FOR XML PATH ('')),2, 1000);
SELECT @sql =
'SELECT *
FROM TAG
PIVOT
(
MAX(Value)
FOR TagID IN( ' + @columns + ' )) as TagTime;';
execute(@sql);
#3
9
Another option that may be suitable in this situation is using XML
在这种情况下可能适合的另一个选择是使用XML
The XML option to transposing rows into columns is basically an optimal version of the PIVOT in that it addresses the dynamic column limitation.
将行转换为列的XML选项基本上是PIVOT的最佳版本，因为它解决了动态列限制。
The XML version of the script addresses this limitation by using a combination of XML Path, dynamic T-SQL and some built-in functions (i.e. STUFF, QUOTENAME).
该脚本的XML版本通过结合使用XML Path，动态T-SQL和一些内置函数（即STUFF，QUOTENAME）来解决此限制。
Vertical expansion
垂直扩展
Similar to the PIVOT and the Cursor, newly added policies are able to be retrieved in the XML version of the script without altering the original script.
与PIVOT和Cursor类似，可以在脚本的XML版本中检索新添加的策略，而无需更改原始脚本。
Horizontal expansion
水平扩展
Unlike the PIVOT, newly added documents can be displayed without altering the script.
与PIVOT不同，可以在不更改脚本的情况下显示新添加的文档。
Performance breakdown
性能细分
In terms of IO, the statistics of the XML version of the script is almost similar to the PIVOT – the only difference is that the XML has a second scan of dtTranspose table but this time from a logical read – data cache.
就IO而言，脚本的XML版本的统计信息几乎与PIVOT类似 - 唯一的区别是XML有第二次扫描dtTranspose表，但这次是从逻辑读取 - 数据缓存。
You can find some more about these solutions (including some actual T-SQL exmaples) in this article: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
您可以在本文中找到有关这些解决方案的更多信息（包括一些实际的T-SQL exmaples）：https：//www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
#4
1
Based on the solution from bluefeet here is a stored procedure that uses dynamic sql to generate the transposed table. It requires that all the fields are numeric except for the transposed column (the column that will be the header in the resulting table):
基于bluefeet的解决方案，这里有一个存储过程，它使用动态sql生成转置表。除了转置列（将在结果表中作为标题的列）之外，它要求所有字段都是数字：
/****** Object: StoredProcedure [dbo].[SQLTranspose] Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for transposing.
-- Parameters: @TableName - Table to transpose
-- @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
-- table and FIeldToTranspose should be written using single quotes
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose]
-- Add the parameters for the stored procedure here
@TableName NVarchar(MAX) = '',
@FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@queryPivot AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@columnToPivot as NVARCHAR(MAX),
@tableToPivot as NVARCHAR(MAX),
@colsResult as xml
select @tableToPivot = @TableName;
select @columnToPivot = @FieldNameTranspose
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot
for xml path('')), 1, 1, '')
set @queryPivot = 'SELECT @colsResult = (SELECT '',''
+ quotename('+@columnToPivot+')
from '+@tableToPivot+' t
where '+@columnToPivot+' <> ''''
FOR XML PATH(''''), TYPE)'
exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out
select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query
= 'select name, rowid, '+@colsPivot+'
from
(
select '+@columnToPivot+' , name, value, ROW_NUMBER() over (partition by '+@columnToPivot+' order by '+@columnToPivot+') as rowid
from '+@tableToPivot+'
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for '+@columnToPivot+' in ('+@colsPivot+')
) piv
order by rowid'
exec(@query)
END
#5
0
I had a slightly different requirement, whereby I had to selectively transpose columns into rows.
我的要求略有不同，因此我必须有选择地将列转换为行。
The table had columns:
该表有列：
create table tbl (ID, PreviousX, PreviousY, CurrentX, CurrentY)
I needed columns for Previous and Current, and rows for X and Y. A Cartesian product generated on a static table worked nicely, eg:
我需要Previous和Current的列，以及X和Y的行。在静态表上生成的笛卡尔积可以很好地工作，例如：
select
ID,
max(case when metric='X' then PreviousX
case when metric='Y' then PreviousY end) as Previous,
max(case when metric='X' then CurrentX
case when metric='Y' then CurrentY end) as Current
from tbl inner join
/* Cartesian product - transpose by repeating row and
picking appropriate metric column for period */
( VALUES (1, 'X'), (2, 'Y')) AS x (sort, metric) ON 1=1
group by ID
order by ID, sort
#1
34
One way to do it if tagID values are known upfront is to use conditional aggregation
如果预先知道tagID值，则使用条件聚合的一种方法是使用条件聚合
SELECT TimeSeconds,
COALESCE(MAX(CASE WHEN TagID = 'A1' THEN Value END), 'n/a') A1,
COALESCE(MAX(CASE WHEN TagID = 'A2' THEN Value END), 'n/a') A2,
COALESCE(MAX(CASE WHEN TagID = 'A3' THEN Value END), 'n/a') A3,
COALESCE(MAX(CASE WHEN TagID = 'A4' THEN Value END), 'n/a') A4
FROM table1
GROUP BY TimeSeconds
or if you're OK with NULL values instead of 'n/a'
或者如果你没有使用NULL值而不是'n / a'
SELECT TimeSeconds,
MAX(CASE WHEN TagID = 'A1' THEN Value END) A1,
MAX(CASE WHEN TagID = 'A2' THEN Value END) A2,
MAX(CASE WHEN TagID = 'A3' THEN Value END) A3,
MAX(CASE WHEN TagID = 'A4' THEN Value END) A4
FROM table1
GROUP BY TimeSeconds
or with PIVOT
或者是PIVOT
SELECT TimeSeconds, A1, A2, A3, A4
FROM
(
SELECT TimeSeconds, TagID, Value
FROM table1
) s
PIVOT
(
MAX(Value) FOR TagID IN (A1, A2, A3, A4)
) p
Output (with NULLs):
输出（带NULL）：
TimeSeconds A1 A2 A3 A4 ----------- ------- ------ ----- ----- 1378700244 3.75 NULL NULL NULL 1378700245 30.00 NULL NULL NULL 1378700304 1.20 NULL NULL NULL 1378700305 NULL 56.00 NULL NULL 1378700344 NULL 11.00 NULL NULL 1378700345 NULL NULL 0.53 NULL 1378700364 4.00 NULL NULL NULL 1378700365 14.50 NULL NULL NULL 1378700384 144.00 NULL NULL 10.00
If you have to figure TagID values out dynamically then use dynamic SQL
如果必须动态计算TagID值，请使用动态SQL
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(TagID)
FROM Table1
ORDER BY 1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @sql = 'SELECT TimeSeconds, ' + @cols + '
FROM
(
SELECT TimeSeconds, TagID, Value
FROM table1
) s
PIVOT
(
MAX(Value) FOR TagID IN (' + @cols + ')
) p'
EXECUTE(@sql)
#2
9
SQL Server has a PIVOT command that might be what you are looking for.
SQL Server有一个可能正在寻找的PIVOT命令。
select * from Tag
pivot (MAX(Value) for TagID in ([A1],[A2],[A3],[A4])) as TagTime;
If the columns are not constant, you'll have to combine this with some dynamic SQL.
如果列不是常量，则必须将其与某些动态SQL结合使用。
DECLARE @columns AS VARCHAR(MAX);
DECLARE @sql AS VARCHAR(MAX);
select @columns = substring((Select DISTINCT ',' + QUOTENAME(TagID) FROM Tag FOR XML PATH ('')),2, 1000);
SELECT @sql =
'SELECT *
FROM TAG
PIVOT
(
MAX(Value)
FOR TagID IN( ' + @columns + ' )) as TagTime;';
execute(@sql);
#3
9
Another option that may be suitable in this situation is using XML
在这种情况下可能适合的另一个选择是使用XML
The XML option to transposing rows into columns is basically an optimal version of the PIVOT in that it addresses the dynamic column limitation.
将行转换为列的XML选项基本上是PIVOT的最佳版本，因为它解决了动态列限制。
The XML version of the script addresses this limitation by using a combination of XML Path, dynamic T-SQL and some built-in functions (i.e. STUFF, QUOTENAME).
该脚本的XML版本通过结合使用XML Path，动态T-SQL和一些内置函数（即STUFF，QUOTENAME）来解决此限制。
Vertical expansion
垂直扩展
Similar to the PIVOT and the Cursor, newly added policies are able to be retrieved in the XML version of the script without altering the original script.
与PIVOT和Cursor类似，可以在脚本的XML版本中检索新添加的策略，而无需更改原始脚本。
Horizontal expansion
水平扩展
Unlike the PIVOT, newly added documents can be displayed without altering the script.
与PIVOT不同，可以在不更改脚本的情况下显示新添加的文档。
Performance breakdown
性能细分
In terms of IO, the statistics of the XML version of the script is almost similar to the PIVOT – the only difference is that the XML has a second scan of dtTranspose table but this time from a logical read – data cache.
就IO而言，脚本的XML版本的统计信息几乎与PIVOT类似 - 唯一的区别是XML有第二次扫描dtTranspose表，但这次是从逻辑读取 - 数据缓存。
You can find some more about these solutions (including some actual T-SQL exmaples) in this article: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
您可以在本文中找到有关这些解决方案的更多信息（包括一些实际的T-SQL exmaples）：https：//www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
#4
1
Based on the solution from bluefeet here is a stored procedure that uses dynamic sql to generate the transposed table. It requires that all the fields are numeric except for the transposed column (the column that will be the header in the resulting table):
基于bluefeet的解决方案，这里有一个存储过程，它使用动态sql生成转置表。除了转置列（将在结果表中作为标题的列）之外，它要求所有字段都是数字：
/****** Object: StoredProcedure [dbo].[SQLTranspose] Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for transposing.
-- Parameters: @TableName - Table to transpose
-- @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
-- table and FIeldToTranspose should be written using single quotes
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose]
-- Add the parameters for the stored procedure here
@TableName NVarchar(MAX) = '',
@FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@queryPivot AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@columnToPivot as NVARCHAR(MAX),
@tableToPivot as NVARCHAR(MAX),
@colsResult as xml
select @tableToPivot = @TableName;
select @columnToPivot = @FieldNameTranspose
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot
for xml path('')), 1, 1, '')
set @queryPivot = 'SELECT @colsResult = (SELECT '',''
+ quotename('+@columnToPivot+')
from '+@tableToPivot+' t
where '+@columnToPivot+' <> ''''
FOR XML PATH(''''), TYPE)'
exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out
select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query
= 'select name, rowid, '+@colsPivot+'
from
(
select '+@columnToPivot+' , name, value, ROW_NUMBER() over (partition by '+@columnToPivot+' order by '+@columnToPivot+') as rowid
from '+@tableToPivot+'
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for '+@columnToPivot+' in ('+@colsPivot+')
) piv
order by rowid'
exec(@query)
END
#5
0
I had a slightly different requirement, whereby I had to selectively transpose columns into rows.
我的要求略有不同，因此我必须有选择地将列转换为行。
The table had columns:
该表有列：
create table tbl (ID, PreviousX, PreviousY, CurrentX, CurrentY)
I needed columns for Previous and Current, and rows for X and Y. A Cartesian product generated on a static table worked nicely, eg:
我需要Previous和Current的列，以及X和Y的行。在静态表上生成的笛卡尔积可以很好地工作，例如：
select
ID,
max(case when metric='X' then PreviousX
case when metric='Y' then PreviousY end) as Previous,
max(case when metric='X' then CurrentX
case when metric='Y' then CurrentY end) as Current
from tbl inner join
/* Cartesian product - transpose by repeating row and
picking appropriate metric column for period */
( VALUES (1, 'X'), (2, 'Y')) AS x (sort, metric) ON 1=1
group by ID
order by ID, sort