我有一个具有以下结构的表。我想转置它。
BookId Status
----------------------
123A Perfect
123B Restore
123C Lost
123D Perfect
123A Perfect
123B Restore
123A Lost
123B Restore
我需要转置表看起来像这样。
Output
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
是无效的。我收到这个错误
我对枢轴的手不多。但尝试以下
select *
from
(select SellerAddress, ApplicationStatus
from [Farm_For_Books].[dbo].[Orders]) src
pivot
(sum(ApplicationStatus)
for SellerAddress in ([1], [2], [3])
) piv;
conditional aggregation
可能会被使用
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 演示
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)