我有一个数据库ordEdit,设计是这样的:
cst_id | quantity | state
105 | 3 | M
109 | 5 | R
109 | 10 | M
105 | 6 | C
105 | 3 | C
109 | 1 | R
105 | 6 | R
109 | 7 | M
我想得到的结果是:
cst_id | M | R | C
105 | 3 | 6 | 9
109 | 17 | 6 | 0
语句的写法:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
固定写法
1
select cst_id ,
2
sum(case when state='M' then quantity end) as M,
3
sum(case when state='R' then quantity end) as R,
4
sum(case when state='C' then quantity end) as C
5
from ordEdit
6
group by cst_id
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
不固定写法(动态SQL)
1
declare @sql varchar(2000)
2
set @sql = 'select cst_id'
3
select @sql = @sql + ' , sum(case state when ''' + state + ''' then quantity else 0 end) [' + state + ']'
4
from (select distinct state from ordEdit) as a
5
set @sql = @sql + ' from ordEdit group by cst_id'
6
exec(@sql)