主页 / dba / 问题 / 1181
Accepted
kacalapy
Asked: 2011-02-12 08:34:47 +0800 CST

实施 PIVOT 查询

  • 772

如何旋转以下 SELECT 语句：

         SELECT sc.CID,
                sc.CodeName     as OverviewText,
                scRAG.CodeName  as RAGStatusText 
           FROM StatusCode      sc
LEFT OUTER JOIN ProjectOverview po 
             ON sc.CID          = po.ProjectOverviewCID 
            AND po.ProjectId    = 180
LEFT OUTER JOIN StatusCode      scRAG 
             ON po.RAGStatusCID = scRAG.CID
          WHERE sc.SCID         = 18

这样的语句产生如下结果集：

CID OverviewText    RAGStatusText
--- ------------    -------------
153 Cost            Green
154 Requirements    Yellow
155 Schedule        NULL
156 Technical       NULL
157 Testing         NULL

但是，我希望它返回包含 10 个值的一行，如下所示：

 ----------------------------------------------------------------------------------------------
 | Cost | Green | Requirements | Yellow | Schedule | NULL | Technical | NULL | Testing | NULL |
 ----------------------------------------------------------------------------------------------

我可以转向CID吗？

sql-server t-sql

1 个回答

  1. Best Answer

    我不确定这是否正是您想要的。但它给了你预期的结果。请注意，您可以根据需要重命名列。

    SET NOCOUNT ON

declare @t table(
    cid int,
    OverviewText varchar(15),
    RAGStatusText varchar(15)
 )

insert into @t values (153, 'Cost',           'Green');
insert into @t values (154, 'Requirements',   'Yellow');
insert into @t values (155, 'Schedule',       'NULL');
insert into @t values (156, 'Technical',      'NULL');
insert into @t values (157, 'Testing',        'NULL');


Select 
    [153|1] as [xyz],
    [153|2],
    [154|1],
    [154|2],
    [155|1],
    [155|2],
    [156|1],
    [156|2],
    [157|1],
    [157|2]

FROM (
select CAST(cid as varchar) + '|1' type, OverviewText  text from @t
Union
select CAST(cid as varchar) + '|2' type, RAGStatusText text from @t
) as SourceTable
Pivot(
    min(text)
    for type in ([153|1],[153|2], [154|1],[154|2], [155|1],[155|2], [156|1],[156|2], [157|1],[157|2])
) as PivotTable;
    • 3

相关问题