如何设计用于从行创建动态列的查询 [英] How to design Query for creating dynamic columns from rows
问题描述
我有数据
表 1
ID Name
-----------
1 n1
2 n2
3 n4
表 2
FID YearS Val
----------------------
1 2008 Up
1 2009 Down
1 2010 Up
2 2000 Up
2 2001 Down
2 2002 Up
2 2003 Up
3 2009 Down
3 2010 Up
我想以下列格式返回数据:
I want to return data in following format:
ID Yr1 Val1 Yr2 Val2 Yr3 Val3 Yr4 Val4
--------------------------------------------------------
1 2008 Up 2009 Down 2010 Up NULL Null
2 2000 Up 2001 Down 2002 Up 2003 Up
3 2009 Down 2010 Up NULL NULL NULL Null
根据 ID 的最大列数，我想创建列名，然后转换列中的行.这可以使用 sql 查询吗?
Based on maximum no of columns for ID i want to create column names and then convert rows in columns. Is this possible to do using a sql query?
推荐答案
我创建了一个名为Table2"的表格，其中包含您在上面表格 2 标题下显示的数据.
I have created a table called "Table2", containing the data you have shown above under your Table 2 heading.
这是我在 SQL Server 2008 中使用的 SQL.
Here is the SQL I used in SQL Server 2008.
WITH RankedValues AS
(
SELECT
FID AS ID,
YearS,
ROW_NUMBER() OVER(PARTITION BY FID ORDER BY YearS) AS YearSRank,
Val
FROM
Table2
)
SELECT
ID,
MAX((CASE WHEN YearSRank = 1 THEN YearS ELSE 0 END)) AS Yr1,
MAX((CASE WHEN YearSRank = 1 THEN Val ELSE '' END)) AS Val1,
MAX((CASE WHEN YearSRank = 2 THEN YearS ELSE 0 END)) AS Yr2,
MAX((CASE WHEN YearSRank = 2 THEN Val ELSE '' END)) AS Val2,
MAX((CASE WHEN YearSRank = 3 THEN YearS ELSE 0 END)) AS Yr3,
MAX((CASE WHEN YearSRank = 3 THEN Val ELSE '' END)) AS Val3,
MAX((CASE WHEN YearSRank = 4 THEN YearS ELSE 0 END)) AS Yr4,
MAX((CASE WHEN YearSRank = 4 THEN Val ELSE '' END)) AS Val4
FROM
RankedValues
GROUP BY
ID
上述 SQL 将导致:
The above SQL will result in this:
ID Yr1 Val1 Yr2 Val2 Yr3 Val3 Yr4 Val4
---------------------------------------------------------------------
1 2008 Up 2009 Down 2010 Up 0
2 2000 Up 2001 Down 2002 Up 2003 Up
3 2009 Down 2010 Up 0 0
您没有看到 NULL 值的原因是每个 CASE 语句中的 ELSE.如果您更喜欢 NULL 值，只需根据需要删除 ELSE 0 和 ELSE ''.
The reason you do not see NULL values is because of the ELSE in each CASE statement.
If you rather have NULL values, simply remove the ELSE 0 and ELSE '' as required.
我目前不知道是否可以使这个通用，例如:处理未知数量的不同 FID，因为这也意味着生成列名(Yr1、al1、Yr2 等)一般.
I do not know at this time if it is possible to make this generic, e.g.: process an unknown amount of distinct FIDs, as this would also mean generating the column names (Yr1, al1, Yr2,etc..) generically.
您可以使用动态 SQL 实现这一点，但由于我不是动态 SQL 的忠实粉丝，因此我会尝试寻找另一种处理方法.
You could propably achieve this with dynamic SQL but as I'm not a big fan of dynamic SQL I would try and look into another way of dealing with that.
-- 编辑(为完整性添加了枢轴方法)--
-- Edit (Added pivot approach for completness)--
我查看了 Joe Stefanelli 发布的链接，并根据您的要求添加了以下 SQL.虽然我不喜欢动态 SQL 的想法，但在这个特定实例中我找不到任何其他方法.
I looked at the link Joe Stefanelli posted and I added the SQL below for your requirement. Though I do not like the idea of dynamic SQL I was unable to find any other way in this specific instance.
DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT @years = STUFF((
SELECT DISTINCT
'],[' + ltrim(str(YearS))
FROM Table2
ORDER BY '],[' + ltrim(str(YearS))
FOR XML PATH('')), 1, 2, '') + ']'
SET @query =
'SELECT * FROM
(
SELECT FID AS ID,YearS,Val
FROM Table2
) AS t
PIVOT (MAX(Val) FOR YearS IN (' + @years + ')) AS pvt'
EXECUTE (@query)
结果如下:
ID 2000 2001 2002 2003 2008 2009 2010
---------------------------------------------------------
1 NULL NULL NULL NULL Up Down Up
2 Up Down Up Up NULL NULL NULL
3 NULL NULL NULL NULL NULL Down Up
根据你最喜欢的格式和方法，至少你有你的选择.
Depending which format and approach you like best, at least you have your options lined out.
这篇关于如何设计用于从行创建动态列的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
