如何将 SQL 查询结果转换为数据透视表格式？

Question

如何将 SQL 查询结果转换为数据透视表格式？

4

我将尝试以交叉表格的格式呈现简单的连接查询结果。

下方是示例查询：

if object_id('tempdb.dbo.#t') is not null      
    drop table #t    

create table #t (
    EmpName nvarchar(1000),
    CaseNumber nvarchar(1000),
    [WeekDay]   nvarchar(1000)
)

insert into #t values ('Sam','SM100','Monday')
insert into #t values ('Sam','SM100','Wednesday')
insert into #t values ('Sam','SM100','Thursday')
insert into #t values ('Peter','PT200','Monday')
insert into #t values ('Peter','PT200','Tuesday')
insert into #t values ('Peter','PT200','Wednesday')
insert into #t values ('Peter','PT200','Thursday')
insert into #t values ('Peter','PT200','Friday')
insert into #t values ('Peter','PT200','Saturday')
insert into #t values ('Peter','PT200','Sunday')

select * from #t

-- Expected output tabular format

从John的查询语句开始...

;with cte as
(
Select * 
 From (Select *,Val=1 From #t) src
 Pivot (sum(Val) for weekday in ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday]) )pvt
 )
 select EmpName, CaseNumber, 
 Case When Sunday = 1 then 'Yes' when isnull(Sunday,'') = '' then 'No' end Sunday,
 Case When Monday = 1 then 'Yes' when isnull(Monday,'') = '' then 'No' end Monday,
 Case When Tuesday = 1 then 'Yes' when isnull(Tuesday,'') = '' then 'No' end Tuesday,
 Case When Wednesday = 1 then 'Yes' when isnull(Wednesday,'') = '' then 'No' end Wednesday,
 Case When Thursday = 1 then 'Yes' when isnull(Thursday,'') = '' then 'No' end Thursday,
 Case When Friday = 1 then 'Yes' when isnull(Friday,'') = '' then 'No' end Friday,
 Case When Saturday = 1 then 'Yes' when isnull(Saturday,'') = '' then 'No' end Saturday 
  from cte

我更喜欢不使用过多的条件语句...

- goofyui

也许你需要使用 PIVOT。 - Eric

2个回答

3

像这样吗？

例子

Select EmpName
      ,CaseNumber
      ,[Sunday]   = IsNull([Sunday],'No')
      ,[Monday]   = IsNull([Monday],'No')
      ,[Tuesday]  = IsNull([Tuesday],'No')
      ,[Wednesday]= IsNull([Wednesday],'No')
      ,[Thursday] = IsNull([Thursday],'No')
      ,[Friday]   = IsNull([Friday],'No')
      ,[Saturday] = IsNull([Saturday],'No')
 From  (Select *,Val='Yes' From #t) src
 Pivot (max(Val) for weekday in ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday]) )pvt

返回值

- John Cappelletti

谢谢。这非常有帮助。我可以使用 case when val = 1 then yes when isnull(val,'') = '' then no end 来代替 val = 1，以保持 Yes 或 No，而不是值。 - goofyui

@goofyui 不确定您想要的结果是什么。但您可以将Val ='Yes'和sum(Val)更改为max(Val)。 - John Cappelletti

1

@goofyui 很高兴能帮忙 :) - John Cappelletti

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- GMB · Accepted Answer

您可以使用“条件聚合”：

SELECT
    EmpName,
    MAX(CASE WHEN WeekDay = 'Sunday'    THEN CaseNumber END) AS Sunday,
    MAX(CASE WHEN WeekDay = 'Monday'    THEN CaseNumber END) AS Monday,
    MAX(CASE WHEN WeekDay = 'Tuesday'   THEN CaseNumber END) AS Tuesday,
    MAX(CASE WHEN WeekDay = 'Wednesday' THEN CaseNumber END) AS Wednesday,
    MAX(CASE WHEN WeekDay = 'Thursday'  THEN CaseNumber END) AS Thursday,
    MAX(CASE WHEN WeekDay = 'Friday'    THEN CaseNumber END) AS Friday,
    MAX(CASE WHEN WeekDay = 'Saturday'  THEN CaseNumber END) AS Saturday
FROM #t 
GROUP BY EmpName

在 DB Fiddle 上演示，使用您的样本数据：

员工姓名 | 星期日 | 星期一 | 星期二 | 星期三 | 星期四 | 星期五 | 星期六
:------ | :----- | :----- | :------ | :-------- | :------- | :----- | :-------
Peter   | PT200  | PT200  | PT200   | PT200     | PT200    | PT200  | PT200   
Sam     | null   | SM100  | null    | SM100     | SM100    | null   | null

请注意，此处只是翻译，不包括任何解释。