我有数据
表格1
@H_404_4@ID Name ----------- 1 n1 2 n2 3 n4表2
@H_404_4@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我想以下列格式返回数据：
@H_404_4@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查询吗？
解决方法
我创建了一个名为“Table2”的表,其中包含您在表2标题下显示的数据.
@H_404_4@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将导致：
@H_404_4@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”.
我现在还不知道是否有可能使这种通用,例如：处理未知数量的不同FID,因为这也意味着通常生成列名(Yr1,al1,Yr2等).
你可以通过动态sql实现这一点,但由于我不是动态sql的忠实粉丝,我会尝试另一种处理它的方法.
我查看了Joe Stefanelli发布的链接,并根据您的要求添加了以下sql.虽然我不喜欢动态sql的想法,但我无法在这个特定实例中找到任何其他方法.
@H_404_4@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,Val FROM Table2 ) AS t PIVOT (MAX(Val) FOR YearS IN (' + @years + ')) AS pvt' EXECUTE (@query)这将导致以下结果：
@H_404_4@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根据您最喜欢的格式和方法,至少您可以选择自己的选项.
