sql – 如何设计用于从行创建动态列的查询

前端之家收集整理的这篇文章主要介绍了sql – 如何设计用于从行创建动态列的查询前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有数据

表格1

  1. ID Name
  2. -----------
  3. 1 n1
  4. 2 n2
  5. 3 n4

表2

  1. FID YearS Val
  2. ----------------------
  3. 1 2008 Up
  4. 1 2009 Down
  5. 1 2010 Up
  6. 2 2000 Up
  7. 2 2001 Down
  8. 2 2002 Up
  9. 2 2003 Up
  10. 3 2009 Down
  11. 3 2010 Up

我想以下列格式返回数据:

  1. ID Yr1 Val1 Yr2 Val2 Yr3 Val3 Yr4 Val4
  2. --------------------------------------------------------
  3. 1 2008 Up 2009 Down 2010 Up NULL Null
  4. 2 2000 Up 2001 Down 2002 Up 2003 Up
  5. 3 2009 Down 2010 Up NULL NULL NULL Null

基于ID的最大列数,我想创建列名,然后转换列中的行.这可以使用SQL查询吗?

解决方法

我创建了一个名为“Table2”的表,其中包含您在表2标题显示的数据.

这是我在sql Server 2008中使用的sql.

  1. WITH RankedValues AS
  2. (
  3. SELECT
  4. FID AS ID,YearS,ROW_NUMBER() OVER(PARTITION BY FID ORDER BY YearS) AS YearSRank,Val
  5. FROM
  6. Table2
  7. )
  8. SELECT
  9. 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
  10. FROM
  11. RankedValues
  12. GROUP BY
  13. ID

上面的sql将导致:

  1. ID Yr1 Val1 Yr2 Val2 Yr3 Val3 Yr4 Val4
  2. ---------------------------------------------------------------------
  3. 1 2008 Up 2009 Down 2010 Up 0
  4. 2 2000 Up 2001 Down 2002 Up 2003 Up
  5. 3 2009 Down 2010 Up 0 0

您没有看到NULL值的原因是因为每个CASE语句中都有ELSE.
如果您更喜欢NULL值,只需根据需要删除ELSE 0和ELSE”.

我现在还不知道是否有可能使这种通用,例如:处理未知数量的不同FID,因为这也意味着通常生成列名(Yr1,al1,Yr2等).

你可以通过动态sql实现这一点,但由于我不是动态sql的忠实粉丝,我会尝试另一种处理它的方法.

– 编辑(为完整性添加了透视方法) –

我查看了Joe Stefanelli发布的链接,并根据您的要求添加了以下sql.虽然我不喜欢动态sql的想法,但我无法在这个特定实例中找到任何其他方法.

  1. DECLARE @query VARCHAR(4000)
  2. DECLARE @years VARCHAR(2000)
  3.  
  4. SELECT @years = STUFF((
  5. SELECT DISTINCT
  6. '],[' + ltrim(str(YearS))
  7. FROM Table2
  8. ORDER BY '],[' + ltrim(str(YearS))
  9. FOR XML PATH('')),1,2,'') + ']'
  10.  
  11. SET @query =
  12. 'SELECT * FROM
  13. (
  14. SELECT FID AS ID,Val
  15. FROM Table2
  16. ) AS t
  17. PIVOT (MAX(Val) FOR YearS IN (' + @years + ')) AS pvt'
  18.  
  19. EXECUTE (@query)

这将导致以下结果:

  1. ID 2000 2001 2002 2003 2008 2009 2010
  2. ---------------------------------------------------------
  3. 1 NULL NULL NULL NULL Up Down Up
  4. 2 Up Down Up Up NULL NULL NULL
  5. 3 NULL NULL NULL NULL NULL Down Up

根据您最喜欢的格式和方法,至少您可以选择自己的选项.

猜你在找的MsSQL相关文章