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

表格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

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

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查询吗？

解决方法

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

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将导致：

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的想法,但我无法在这个特定实例中找到任何其他方法.

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)

这将导致以下结果：

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!