I'm looking for a pure-SQL way (SQL Server 2012 if it matters) to convert row data to columns. I've tried searching for this, and can't find anything for the data format I'm trying to convert, possibly because my SQL knowledge is pretty basic.
我正在寻找一种纯SQL方式（SQL Server 2012，如果重要的话）将行数据转换为列。我已经尝试过搜索这个，并且找不到我想要转换的数据格式，可能是因为我的SQL知识非常基础。
My input data is a list of Release Names and Story Points extracted from our JIRA server, along with the extract date. The table I'm trying to use will actually contain extracts from many different projects, although I'm excluding the ProjectName column from these examples.
我的输入数据是从我们的JIRA服务器中提取的版本名称和故事点列表，以及提取日期。我试图使用的表实际上包含来自许多不同项目的提取，尽管我从这些示例中排除了ProjectName列。
Input Data:
输入数据：
Version Date StoryPoints
1.0 2017-01-01 10
1.0 2017-02-01 10
1.0 2017-03-01 15
1.0 2017-04-01 15
2.0 2017-01-01 10
2.0 2017-02-01 10
2.0 2017-03-01 10
2.0 2017-04-01 10
3.0 2017-01-01 5
3.0 2017-02-01 5
3.0 2017-03-01 5
3.0 2017-04-01 5
Completed 2017-01-01 0
Completed 2017-02-01 5
Completed 2017-03-01 15
Completed 2017-04-01 28
We need to generate a table in either format below, which will be used to create a "burnup" chart in our Confluence wiki. Since each project can have different Version names, we can't hard-code any of the column names below (although "Completed" will exist in all projects).
我们需要以下面任一格式生成一个表格，用于在Confluence wiki中创建“燃耗”图表。由于每个项目可以具有不同的版本名称，因此我们不能对下面的任何列名称进行硬编码（尽管“已完成”将存在于所有项目中）。
Also, even though we will standardize on dates similar to ones in the example, I would prefer to not hard-code any of the date values into the query either.
此外，即使我们将标准化类似于示例中的日期，我也不希望将任何日期值硬编码到查询中。
Output Format #1:
输出格式＃1：
Date 1.0 2.0 3.0 Completed
2017-01-01 10 10 5 0
2017-02-01 10 10 5 5
2017-03-01 15 10 5 15
2017-04-01 15 10 5 28
I recognize that it may be difficult to structure the data that way, so it's possible to use the following output format too, which I can also chart in Confluence (although it's not as intuitive as the above format).
我认识到以这种方式构造数据可能很困难，因此也可以使用以下输出格式，我也可以在Confluence中绘制图表（虽然它不像上面的格式那样直观）。
Output Format #2:
输出格式＃2：
Versions 2017-01-01 2017-02-01 2017-03-01 2017-04-01
1.0 10 10 15 15
2.0 10 10 10 10
3.0 5 5 5 5
Completed 0 5 15 28
Any help is GREATLY appreciated!
任何帮助是极大的赞赏！
2 个解决方案
#1
2
As suggested by Xingzhou Liu, I came up into this.
正如刘星洲所说，我想到了这一点。
Source:
资源：
IF ( OBJECT_ID('tempdb..#TMPtbl') IS NOT NULL )
BEGIN
DROP TABLE #TMPtbl
END
CREATE TABLE #TMPtbl
(
Id INT IDENTITY(1, 1)
PRIMARY KEY ,
[Version] VARCHAR(10) ,
[Date] DATETIME ,
StoryPoints INT
)
INSERT INTO #TMPtbl
( Version, Date, StoryPoints )
VALUES ( '1.0', '2017-01-01', 10 ),
( '1.0', '2017-02-01', 10 ),
( '1.0', '2017-03-01', 15 ),
( '1.0', '2017-04-01', 15 ),
( '2.0', '2017-01-01', 10 ),
( '2.0', '2017-02-01', 10 ),
( '2.0', '2017-03-01', 10 ),
( '2.0', '2017-04-01', 10 ),
( '3.0', '2017-01-01', 5 ),
( '3.0', '2017-02-01', 5 ),
( '3.0', '2017-03-01', 5 ),
( '3.0', '2017-04-01', 5 ),
( 'Completed', '2017-01-01', 0 ),
( 'Completed', '2017-02-01', 5 ),
( 'Completed', '2017-03-01', 15 ),
( 'Completed', '2017-04-01', 28 )
Using STUFF and FOR XML you can get the column dynamically
使用STUFF和FOR XML，您可以动态获取列
DECLARE @StrColVer NVARCHAR(MAX) = STUFF(( SELECT DISTINCT
', '
+ QUOTENAME(CAST(t.[Version] AS VARCHAR(154)))
FROM #TMPtbl t
FOR
XML PATH('')
), 1, 2, '')
DECLARE @StrColSUmVer NVARCHAR(MAX) = STUFF(( SELECT DISTINCT
', ' + 'SUM('
+ QUOTENAME(CAST(t.[Version] AS VARCHAR(154)))
+ ') '
+ QUOTENAME(CAST(t.[Version] AS VARCHAR(154)))
FROM #TMPtbl t
FOR
XML PATH('')
), 1, 2, '')
DECLARE @StrColDate NVARCHAR(MAX) = STUFF(( SELECT DISTINCT
', '
+ QUOTENAME(FORMAT(t.Date,
'yyyy-MM-dd'))
FROM #TMPtbl t
FOR
XML PATH('')
), 1, 2, '')
DECLARE @StrColSumDate NVARCHAR(MAX) = STUFF(( SELECT DISTINCT
', ' + 'SUM('
+ QUOTENAME(FORMAT(t.Date,
'yyyy-MM-dd'))
+ ') '
+ QUOTENAME(FORMAT(t.Date,
'yyyy-MM-dd'))
FROM #TMPtbl t
FOR
XML PATH('')
), 1, 2, '')
Then Pivot and Execute it as a command query string.
然后将其作为命令查询字符串进行透视和执行。
DECLARE @SQL1 NVARCHAR(MAX) = N'SELECT [Date],
' + @StrColSUmVer + '
FROM (
SELECT * FROM #TMPtbl
)P
PIVOT (
SUM(StoryPoints) FOR Version IN (' + @StrColVer + ')
)PVT
GROUP BY pvt.[Date]'
DECLARE @SQL2 NVARCHAR(MAX) = N'SELECT [Version],
' + @StrColSumDate + '
FROM (
SELECT * FROM #TMPtbl
)P
PIVOT (
SUM(StoryPoints) FOR dATE IN (' + @StrColDate + ')
)PVT
GROUP BY pvt.[Version]'
EXEC(@SQL1)
EXEC(@SQL2)
Results
结果
Date 1.0 2.0 3.0 Completed
----------------------- ----------- ----------- ----------- -----------
2017-01-01 00:00:00.000 10 10 5 0
2017-02-01 00:00:00.000 10 10 5 5
2017-03-01 00:00:00.000 15 10 5 15
2017-04-01 00:00:00.000 15 10 5 28
(4 row(s) affected)
Version 2017-01-01 2017-02-01 2017-03-01 2017-04-01
---------- ----------- ----------- ----------- -----------
1.0 10 10 15 15
2.0 10 10 10 10
3.0 5 5 5 5
Completed 0 5 15 28
(4 row(s) affected)
#2
2
SQL Server 2012 requires you to hard code one of the dimensions you are pivoting by into the PIVOT query.
SQL Server 2012要求您将要转移的维度之一硬编码到PIVOT查询中。
One way you could get around it is by building and executing a dynamic query string.
解决这个问题的一种方法是构建和执行动态查询字符串。
#1
2
As suggested by Xingzhou Liu, I came up into this.
正如刘星洲所说，我想到了这一点。
Source:
资源：
IF ( OBJECT_ID('tempdb..#TMPtbl') IS NOT NULL )
BEGIN
DROP TABLE #TMPtbl
END
CREATE TABLE #TMPtbl
(
Id INT IDENTITY(1, 1)
PRIMARY KEY ,
[Version] VARCHAR(10) ,
[Date] DATETIME ,
StoryPoints INT
)
INSERT INTO #TMPtbl
( Version, Date, StoryPoints )
VALUES ( '1.0', '2017-01-01', 10 ),
( '1.0', '2017-02-01', 10 ),
( '1.0', '2017-03-01', 15 ),
( '1.0', '2017-04-01', 15 ),
( '2.0', '2017-01-01', 10 ),
( '2.0', '2017-02-01', 10 ),
( '2.0', '2017-03-01', 10 ),
( '2.0', '2017-04-01', 10 ),
( '3.0', '2017-01-01', 5 ),
( '3.0', '2017-02-01', 5 ),
( '3.0', '2017-03-01', 5 ),
( '3.0', '2017-04-01', 5 ),
( 'Completed', '2017-01-01', 0 ),
( 'Completed', '2017-02-01', 5 ),
( 'Completed', '2017-03-01', 15 ),
( 'Completed', '2017-04-01', 28 )
Using STUFF and FOR XML you can get the column dynamically
使用STUFF和FOR XML，您可以动态获取列
DECLARE @StrColVer NVARCHAR(MAX) = STUFF(( SELECT DISTINCT
', '
+ QUOTENAME(CAST(t.[Version] AS VARCHAR(154)))
FROM #TMPtbl t
FOR
XML PATH('')
), 1, 2, '')
DECLARE @StrColSUmVer NVARCHAR(MAX) = STUFF(( SELECT DISTINCT
', ' + 'SUM('
+ QUOTENAME(CAST(t.[Version] AS VARCHAR(154)))
+ ') '
+ QUOTENAME(CAST(t.[Version] AS VARCHAR(154)))
FROM #TMPtbl t
FOR
XML PATH('')
), 1, 2, '')
DECLARE @StrColDate NVARCHAR(MAX) = STUFF(( SELECT DISTINCT
', '
+ QUOTENAME(FORMAT(t.Date,
'yyyy-MM-dd'))
FROM #TMPtbl t
FOR
XML PATH('')
), 1, 2, '')
DECLARE @StrColSumDate NVARCHAR(MAX) = STUFF(( SELECT DISTINCT
', ' + 'SUM('
+ QUOTENAME(FORMAT(t.Date,
'yyyy-MM-dd'))
+ ') '
+ QUOTENAME(FORMAT(t.Date,
'yyyy-MM-dd'))
FROM #TMPtbl t
FOR
XML PATH('')
), 1, 2, '')
Then Pivot and Execute it as a command query string.
然后将其作为命令查询字符串进行透视和执行。
DECLARE @SQL1 NVARCHAR(MAX) = N'SELECT [Date],
' + @StrColSUmVer + '
FROM (
SELECT * FROM #TMPtbl
)P
PIVOT (
SUM(StoryPoints) FOR Version IN (' + @StrColVer + ')
)PVT
GROUP BY pvt.[Date]'
DECLARE @SQL2 NVARCHAR(MAX) = N'SELECT [Version],
' + @StrColSumDate + '
FROM (
SELECT * FROM #TMPtbl
)P
PIVOT (
SUM(StoryPoints) FOR dATE IN (' + @StrColDate + ')
)PVT
GROUP BY pvt.[Version]'
EXEC(@SQL1)
EXEC(@SQL2)
Results
结果
Date 1.0 2.0 3.0 Completed
----------------------- ----------- ----------- ----------- -----------
2017-01-01 00:00:00.000 10 10 5 0
2017-02-01 00:00:00.000 10 10 5 5
2017-03-01 00:00:00.000 15 10 5 15
2017-04-01 00:00:00.000 15 10 5 28
(4 row(s) affected)
Version 2017-01-01 2017-02-01 2017-03-01 2017-04-01
---------- ----------- ----------- ----------- -----------
1.0 10 10 15 15
2.0 10 10 10 10
3.0 5 5 5 5
Completed 0 5 15 28
(4 row(s) affected)
#2
2
SQL Server 2012 requires you to hard code one of the dimensions you are pivoting by into the PIVOT query.
SQL Server 2012要求您将要转移的维度之一硬编码到PIVOT查询中。
One way you could get around it is by building and executing a dynamic query string.
解决这个问题的一种方法是构建和执行动态查询字符串。