当列类型为 nvarchar 时，透视表以及列值的总和 [英] Pivoting a table along with sum of column value when column type is nvarchar

问题描述

我有一个具有以下结构的表格.我想转置它.

I have a table with following structure. I want to Transpose it.

 BookId    Status   
 ----------------------
 123A      Perfect
 123B      Restore
 123C      Lost
 123D      Perfect
 123A      Perfect
 123B      Restore
 123A      Lost
 123B      Restore

我需要转置表看起来像这样.

I need the transpose table to look something like this.

输出

 BookId    Total  Perfect   Restore  Lost
 -----------------------------------------
  123A      3       2         0        1
  123B      3       0         3        0
  123C      1       0         0        1
  123D      1       1         0        0

我已经试过了

select
    BookId,
    sum('Perfect') as Perfect,
    sum('Restore') as Restore
from 
    [dbo].[Orders]
group by 
    BookId

但由于那些是 nvarchar 值，sum 是无效的.我收到此错误

But as those are nvarchar values, sum is invalid. I am getting this error

我没有太多的手在枢轴上.但尝试遵循

I have not much hands on pivot. But tried following

select *
from 
    (select SellerAddress, ApplicationStatus
     from [Farm_For_Books].[dbo].[Orders]) src
pivot
    (sum(ApplicationStatus)
         for SellerAddress in ([1], [2], [3])
    ) piv;

推荐答案

条件聚合 可能会用到

with Orders( BookId, Status ) as
(
 select '123A','Perfect' union all
 select '123B','Restore' union all
 select '123C','Lost'    union all
 select '123D','Perfect' union all
 select '123A','Perfect' union all
 select '123B','Restore' union all
 select '123A','Lost'    union all
 select '123B','Restore'   
)    
select
    BookId,
    sum(1) as [Total],
    sum(case when Status='Perfect' then 1 else 0 end ) as [Perfect],
    sum(case when Status='Restore' then 1 else 0 end ) as [Restore],
    sum(case when Status='Lost' then 1 else 0 end ) as [Lost]    
from 
    [Orders]
group by BookId;

BookId  Total   Perfect Restore Lost
123A      3        2       0     1
123B      3        0       3     0
123C      1        0       0     1
123D      1        1       0     0

Rextester 演示

这篇关于当列类型为 nvarchar 时，透视表以及列值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！