SELECT LOCAL,(CASE WHEN PRODUCTS='A' TEHN NUMBER ELSE 0 END )AS A,
(CASE WHEN PRODUCTS='B' TEHN NUMBER ELSE 0 END )AS B,
(CASE WHEN PRODUCTS='C' TEHN NUMBER ELSE 0 END )AS C,
(CASE WHEN PRODUCTS='D' TEHN NUMBER ELSE 0 END )AS D,
FROM table
(CASE WHEN PRODUCTS='B' TEHN NUMBER ELSE 0 END )AS B,
(CASE WHEN PRODUCTS='C' TEHN NUMBER ELSE 0 END )AS C,
(CASE WHEN PRODUCTS='D' TEHN NUMBER ELSE 0 END )AS D,
FROM table
create table aaa(LOCAL varchar (4),PRODUCTS varchar (5),NUMBER int )insert into aaa
select '北京','A', 10 union
select '北京','B', 12 union
select '北京','D', 123 union
select '上海','A', 257 union
select '上海','C', 142 union
select '天津','D', 214 union
select '北京','A', 20 union
select '北京','B', 20 union
select '北京','D', 123 union
select '上海','A', 76969 union
select '上海','C', 2536declare @str2 varchar(8000)
set @str2=''
select @str2=@str2+',sum(case when products='''+products+''' then number else 0 end) '+''''+products+''''
from aaa group by products
exec ('select local '+@str2+' from aaa group by local')
----- ----- ----- ------ -----
北京 30 32 0 123
上海 77226 0 2678 0
天津 0 0 0 214
就不会了!!
不好意思
为何group by后不跟哦local呢?