如何在多达 49 个字段上进行 PIVOT? [英] How to PIVOT over up to 49 fields?

问题描述

我有一个包含过程数据的表.一次遭遇最多可以有 49 个条目.我想为每个帐户输出一行并包括程序、日期和提供者.为此，我尝试编写 CASE 语句:

I have a table that contains procedure data. There can be up to 49 entries for a given encounter. I want to output one row for each account and include the procedure, date and provider. I've tried writing CASE statements to this end:

Select DISTINCT [Encounter Number], 
CASE When [Encounter Proc Sequence] = '1' Then [Procedure Code (Enctr)] 
END as 'Proc1',
Case When [Encounter Proc Sequence] = '1' Then [Date of Service]
END as 'SvcDate1',
CASE When [Encounter Proc Sequence] = '1' Then [Surgeon]
END as 'Surgeon1',
CASE When [Encounter Proc Sequence] = '2' Then [Procedure Code (Enctr)] 
END as 'Proc2',
Case When [Encounter Proc Sequence] = '2' Then [Date of Service]
END as 'SvcDate2',
CASE When [Encounter Proc Sequence] = '2' Then [Surgeon]
END as 'Surgeon2',
CASE When [Encounter Proc Sequence] = '3' Then [Procedure Code (Enctr)] 
END as 'Proc3',
Case When [Encounter Proc Sequence] = '3' Then [Date of Service]
END as 'SvcDate3',
CASE When [Encounter Proc Sequence] = '3' Then [Surgeon]
END as 'Surgeon3',
CASE When [Encounter Proc Sequence] = '4' Then [Procedure Code (Enctr)] 
END as 'Proc4',
Case When [Encounter Proc Sequence] = '4' Then [Date of Service]
END as 'SvcDate4',
CASE When [Encounter Proc Sequence] = '4' Then [Surgeon]
END as 'Surgeon4',
CASE When [Encounter Proc Sequence] = '5' Then [Procedure Code (Enctr)] 
END as 'Proc5',
Case When [Encounter Proc Sequence] = '5' Then [Date of Service]
END as 'SvcDate5',
CASE When [Encounter Proc Sequence] = '5' Then [Surgeon]
END as 'Surgeon5',
CASE When [Encounter Proc Sequence] = '6' Then [Procedure Code (Enctr)] 
END as 'Proc6',
Case When [Encounter Proc Sequence] = '6' Then [Date of Service]
END as 'SvcDate6',
CASE When [Encounter Proc Sequence] = '6' Then [Surgeon]
END as 'Surgeon6',
CASE When [Encounter Proc Sequence] = '7' Then [Procedure Code (Enctr)] 
END as 'Proc7',
Case When [Encounter Proc Sequence] = '7' Then [Date of Service]
END as 'SvcDate7',
CASE When [Encounter Proc Sequence] = '7' Then [Surgeon]
END as 'Surgeon7'
from EncounterProc
where [Date of Service] between '20090101' and '20091231'
and [Procedure Code (ENCTR)] is not null
Group by [Encounter Number], [Encounter Proc Sequence],[Procedure Code (ENCTR)], [Date of     
Service], Surgeon

查询输出显示了一些重复的行，并且查询没有将过程 3(例如)放置在 Proc3 列中.
我将如何为这种情况设置 PIVOT 语法?

The query output shows some duplicated rows and the query isn't placing procedure 3 (for example) in the Proc3 column.
How would I setup the PIVOT syntax for this scenario?

更新:我已阅读有关 PIVOT 的 MSDN 库文档，以及评论中共享的链接.我尝试使这些资源适应我的需要，如下所示:

UPDATE: I've read the MSDN Library documentation on PIVOT, and also the link shared in Comments. I took a stab at adapting those sources to my need as follows:

Select [Encounter Number],
[1] as Proc1,
[Date1] as SvcDate1,
[MD1] as MD1,
[2] as Proc2,
[Date2] as SvcDate2,
[MD2] as MD2,
[3] as Proc3,
[Date3] as SvcDate3,
[MD3] as MD3,
[4] as Proc4,
[Date4] as SvcDate4,
[MD4] as MD4
From
(Select * From
(SELECT [Encounter Number]
        ,[Procedure Code (Enctr)]
        ,Row_Number() OVER ( Partition By [Encounter Number] Order By 
                    [Encounter Number], [Procedure Code (Enctr)] ) AS RowNumber
        FROM EncounterProc)
PIVOT ( MAX([Procedure Code (Enctr)] ) for RowNumber IN 
( [1], [Date1], [MD1],[2], [Date2], [MD2], [3], [Date3], [MD3], [4], [Date4],   
      [MD4]))

当我运行这个查询时，SSMS 给了我以下错误:

When I run this query, SSMS gives me the following error:

Incorrect syntax near the keyword 'PIVOT'.

更新 2:根据 Stuart 的评论，我已将查询修改为:

Update2: Based on Stuart's comment, I've revised the query to be:

Select [Encounter Number],
p.[1] as Proc1,
p.[Date1] as SvcDate1,
p.[MD1] as MD1,
p.[2] as Proc2,
p.[Date2] as SvcDate2,
p.[MD2] as MD2,
p.[3] as Proc3,
p.[Date3] as SvcDate3,
p.[MD3] as MD3,
p.[4] as Proc4,
p.[Date4] as SvcDate4,
p.[MD4] as MD4
From
(Select * From
(SELECT [Encounter Number]
        ,[Procedure Code (Enctr)] 
        ,[Date of Service]
        ,[Surgeon]
        ,Row_Number() OVER ( Partition By [Encounter Number] Order By   
                     [Encounter Number], [Encounter Proc Sequence] ) AS RowNumber
        FROM EncounterProc) p
PIVOT ( MAX([Procedure Code (Enctr)] ) for RowNumber IN 
( [1], [Date1], [MD1],[2], [Date2], [MD2], [3], [Date3], [MD3], [4], [Date4], 
    [MD4]) )

我现在收到一个新的错误说明:

I now receive a new error stating:

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.

指向 PIVOT 语句的右括号.

which points to the closing parenthesis of the PIVOT statement.

必须采取什么措施来修复语法错误?

What must be done to fix the syntax error?

推荐答案

您的第一个查询是最接近的查询，只是您没有将每个 case 都包含在聚合函数中.此外，您似乎不需要 group by 子句中的某些列:

Your first query is the closest one, except that you did not enclosed every case into aggreagate function. Also it seems that you do not need some columns in group by clause:

select
    [Encounter Number], 
    Proc1 = max(CASE When [Encounter Proc Sequence] = '1' Then [Procedure Code (Enctr)] END),
    SvcDate1 = max(Case When [Encounter Proc Sequence] = '1' Then [Date of Service] END),
    Surgeon1 = max(CASE When [Encounter Proc Sequence] = '1' Then [Surgeon] END),
    Proc2 = max(CASE When [Encounter Proc Sequence] = '2' Then [Procedure Code (Enctr)] END),
    SvcDate2 = max(Case When [Encounter Proc Sequence] = '2' Then [Date of Service] END),
    Surgeon2 = max(CASE When [Encounter Proc Sequence] = '2' Then [Surgeon] END),
    Proc3 = max(CASE When [Encounter Proc Sequence] = '3' Then [Procedure Code (Enctr)] END),
    SvcDate3 = max(Case When [Encounter Proc Sequence] = '3' Then [Date of Service] END),
    Surgeon3 = max(CASE When [Encounter Proc Sequence] = '3' Then [Surgeon] END)
    --- etc.
from EncounterProc
where [Date of Service] between '20090101' and '20091231'
    and [Procedure Code (ENCTR)] is not null
group by [Encounter Number], [Encounter Proc Sequence]

这篇关于如何在多达 49 个字段上进行 PIVOT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！