时间戳

首页 > 解决方案 > 使用条件在 SQL Server 中使用 Pivot 将行转换为列

sql - 使用条件在 SQL Server 中使用 Pivot 将行转换为列

问题描述

我有下表的数据（[DSIMEPPD].dbo.[DARPD.MEP0000]）：

WCLABELNR   WCMCU   WCCO    WCDRYER WCNETT  WCVR01  WCDATETIME          WCDL01
25041       160001  00160   0       1000    19759   2019-12-0522:44:16  P1-P2-P3-P5-SF
25042       160001  00160   0       1000    19759   2019-12-0522:59:00  P1-P2-P3-P5-SF
25043       160001  00160   0       1017    19759   2019-12-0523:58:50  P1-P2-P3-P5-SF
25055       160001  00160   0       1000    19383   2019-12-0612:59:41  NULL
36284       150001  00150   A       1000    19281   2019-12-0522:02:22  EUR BATK SNC ABP
36285       150001  00150   A       787     19281   2019-12-0523:03:58  EUR BATK SNC ABP
36293       150001  00150   A       1002    19282   2019-12-0612:07:29  NULL
37997       140001  00140   J       560     19292   2019-12-0521:53:45  Pangassius F 2000
37998       140001  00140   G       552     19292   2019-12-0522:39:33  Pangassius F 2000
37999       140001  00140   G       551     19292   2019-12-0523:25:18  Pangassius F 2000
38012       140001  00140   P       579     19292   2019-12-0609:50:56  NULL
38013       140001  00140   G       575     19292   2019-12-0610:40:55  NULL
49137       120001  00120   B       1228    19343   2019-12-0523:36:33  P1 VH - P2 - P43
49138       120001  00120   B       1202    19343   2019-12-0523:49:18  P1 VH - P2 - P43
49163       120001  00120   A       1100    19344   2019-12-0611:03:56  NULL

我希望它以数据透视表的形式出现，如下所示：

Date        GentGel GentPep AngGel  AngPep  ISSGel  ISSPep  GerGel  GerPep  Total
2019-12-05  2430        560 1103    1787        3017        8897
2019-12-06  1100        579 575 1002        1000        4256

我第一次尝试：

select left(WCDATETIME,10) as Date,sum(cast(WCNETT as decimal)) as GentGel from  [DSIMEPPD].dbo.[DARPD.MEP0000] where WCMCU=120001 and WCDRYER<>'G' and WCDATETIME between @Date1 and @Date2 group by left(WCDATETIME,10),WCCO 
select left(WCDATETIME,10) as Date,sum(cast(WCNETT as decimal)) as GentPep from  [DSIMEPPD].dbo.[DARPD.MEP0000] where WCMCU=120001 and WCDRYER='G' and WCDATETIME between @Date1 and @Date2 group by left(WCDATETIME,10),WCCO 
select left(WCDATETIME,10) as Date,sum(cast(WCNETT as decimal)) as AngGel from  [DSIMEPPD].dbo.[DARPD.MEP0000] where WCMCU=140001 and WCDRYER<>'G' and WCDATETIME between @Date1 and @Date2 group by left(WCDATETIME,10),WCCO 
select left(WCDATETIME,10) as Date,sum(cast(WCNETT as decimal)) as AngPep from  [DSIMEPPD].dbo.[DARPD.MEP0000] where WCMCU=140001 and WCDRYER='G' and WCDATETIME between @Date1 and @Date2 group by left(WCDATETIME,10),WCCO 
select left(WCDATETIME,10) as Date,sum(cast(WCNETT as decimal)) as ISSGel from  [DSIMEPPD].dbo.[DARPD.MEP0000] where WCMCU=150001 and WCDRYER<>'G' and WCDATETIME between @Date1 and @Date2 group by left(WCDATETIME,10),WCCO 
select left(WCDATETIME,10) as Date,sum(cast(WCNETT as decimal)) as ISSPep from  [DSIMEPPD].dbo.[DARPD.MEP0000] where WCMCU=150001 and WCDRYER='G' and WCDATETIME between @Date1 and @Date2 group by left(WCDATETIME,10),WCCO 
select left(WCDATETIME,10) as Date,sum(cast(WCNETT as decimal)) as GerGel from  [DSIMEPPD].dbo.[DARPD.MEP0000] where WCMCU=160001 and WCDRYER<>'G' and WCDATETIME between @Date1 and @Date2 group by left(WCDATETIME,10),WCCO 
select left(WCDATETIME,10) as Date,sum(cast(WCNETT as decimal)) as GerPep from  [DSIMEPPD].dbo.[DARPD.MEP0000] where WCMCU=160001 and WCDRYER='G' and WCDATETIME between @Date1 and @Date2 group by left(WCDATETIME,10),WCCO

但是我怎样才能把它放在一个具有正确布局的查询中呢？

另一种尝试是：

select Date,[Gent],[Angouleme],[ISS],[Gerona],[Gent]+[Angouleme]+[ISS]+[Gerona] as [TOTAAL] 
FROM (
  SELECT case WCCO when '00120' then 'Gent' when '00140' then 'Angouleme'  when '00150' then 'ISS' when '00160' then 'Gerona' end as WCCO ,
  left(WCDATETIME,10) as Date, 
  sum(cast(WCNETT as decimal)) as Weight 
  from [DSIMEPPD].[dbo].[DARPD.MEP0000] 
  where (WCMCU=120001 or WCMCU=140001 or WCMCU=150001 or WCMCU=160001 or WCMCU=170001) and WCDRYER<>'' 
    and WCDATETIME between @Date1 and @Date2 
  group by WCCO, left(WCDATETIME,10) with rollup
)t 
PIVOT (sum(Weight) For WCCO in ([Gent],[Angouleme],[ISS],[Gerona])) AS p

标签： sqlsql-serverpivotunion

解决方案

解决方案：

select Date,isnull([Gent A],0)as [Gent A],isnull([Gent B],0)as [Gent B],
isnull([Gent A],0)+isnull([Gent B],0) as [Gent],isnull([Angouleme Peptan],0) as [Angouleme Peptan],
isnull([Angouleme Gelatin],0) as [Angouleme Gelatin],isnull([ISS],0) as [ISS],
isnull([Gerona],0) as [Gerona],isnull([Gent A],0)+
isnull([Gent B],0)+isnull([Angouleme Peptan],0)+isnull([Angouleme Gelatin],0)
+isnull([ISS],0)+isnull([Gerona],0) as [TOTAAL] 
FROM (
  SELECT case when WCCO = '00120' and WCDRYER = 'A' then 'Gent A'
         when WCCO = '00120' and WCDRYER = 'B' then 'Gent B'
         when WCCO = '00140' and WCDRYER = 'G' then 'Angouleme Peptan'
         when WCCO = '00140' and WCDRYER <> 'G' then 'Angouleme Gelatin'
         when WCCO = '00150' then 'ISS'
         when WCCO = '00160' then 'Gerona'
   end as WCCO,
   left(WCDATETIME,10) as Date,
   sum(cast(WCNETT as decimal)) as Weight 
   from  [DSIMEPPD].dbo.[DARPD.MEP0000] 
   where (WCMCU=120001 or WCMCU=140001 or WCMCU=150001 or WCMCU=160001 
   or WCMCU=170001) and WCDRYER<>'' 
   and WCDATETIME between @Date1 and @Date2
   group by WCCO, WCDRYER, left(WCDATETIME,10) with rollup
   )t 
   PIVOT (sum(Weight) For WCCO in ([Gent A],[Gent B],[Angouleme Peptan],
   [Angouleme Gelatin],[ISS],[Gerona])) AS p

推荐阅读