SQL行转列功能操作
我的数据如下：
天使宝贝系列2011059000
天使宝贝系列2011068000
爱恋一生系列2011056000
爱恋一生系列201106500
简约风尚系列2011055000
简约风尚系列201106600
浪漫田园系列201105600
浪漫田园系列201106600
要求查询出来结果为：
201105 201106 201107 ...
天使宝贝系列9000 8000
爱恋一生系列6000 500
简约风尚系列5000 600
浪漫田园系列600 600
[解决办法]
见精华 很多
[解决办法]
if object_id('tempdb.dbo.#t') is not null drop table #t
create table #t (category varchar(12),code varchar(12),qty int)
insert into #t
select '天使宝贝系列',201105,9000 union all
select '天使宝贝系列',201106,8000 union all
select '爱恋一生系列',201105,6000 union all
select '爱恋一生系列',201106,500 union all
select '简约风尚系列',201105,5000 union all
select '简约风尚系列',201106,600 union all
select '浪漫田园系列',201105,600 union all
select '浪漫田园系列',201106,600
select * from #t
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + '['+code+']' from #T group by code
exec ('select * from (select * from #T) a pivot (max(qty) for code in (' + @sql + ')) b')
category201105201106
爱恋一生系列6000500
简约风尚系列5000600
浪漫田园系列600600
天使宝贝系列90008000
create table #tb
(string nvarchar(50),date nvarchar(10),amt int)
insert #tb
select '天使宝贝系列','201105', 9000 union all
select '天使宝贝系列','201106', 8000 union all
select '爱恋一生系列','201105', 6000 union all
select '爱恋一生系列','201106', 500 union all
select '简约风尚系列','201105', 5000 union all
select '简约风尚系列','201106', 600 union all
select '浪漫田园系列','201105', 600 union all
select '浪漫田园系列','201107', 545 union all
select '简约风尚系列','201107', 777 union all
select '简约风尚系列','201108', 888 union all
select '浪漫田园系列','201108', 999 union all
select '浪漫田园系列','201107', 654
declare @sql as nvarchar(4000)
set @sql='select string'
select @sql=@sql+',sum(case when date='''+date+''' then amt else 0 end) as '''+date+''''
from (select distinct date from #tb) as T
set @sql=@sql+' from #tb group by string'
exec(@sql)
