表A
unit 0 0-80 80-160
来凤县 124 25 299
建始县 233 18 346
咸丰县 94 26 338表B
分数段 来凤县 建始县 咸丰县
0 124 233 94
0-80 25 18 26
80-16 299 346 338
求一SQL语句将表A查询成表B的形式小弟在此先谢过了
unit 0 0-80 80-160
来凤县 124 25 299
建始县 233 18 346
咸丰县 94 26 338表B
分数段 来凤县 建始县 咸丰县
0 124 233 94
0-80 25 18 26
80-16 299 346 338
求一SQL语句将表A查询成表B的形式小弟在此先谢过了
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([unit] nvarchar(3),[0] int,[0-80] int,[80-160] int)
Insert #A
select N'来凤县',124,25,299 union all
select N'建始县',233,18,346 union all
select N'咸丰县',94,26,338
Godeclare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)
select
@s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)',
@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'='''+case when @s2 is not null then 'union all select' else ' select ' end+N' [分数段]='''+quotename(Name,'''')+'''''',
@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+'+'',''+quotename(unit)+''=''+quotename('+quotename(Name)+','''''''') from #A ',
@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)
from
Tempdb..syscolumns
where
id=object_id('Tempdb..#A') and Name not in('unit')
print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句
exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')
這是調用系統表動態生成語句,每一列用一個變量傳參
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
unit varchar(10),
[0] int,
[0-80] int,
[80-160] int
)
go
insert into tb
select '来凤县',124,25,299 union all
select '建始县',233,18,346 union all
select '咸丰县',94,26,338
go
--方法一
with cte as
(
select unit,分数=[0],分数段='0' from tb
union all
select unit,[0-80],'0-80' from tb
union all
select unit,[80-160],'80-160' from tb
)
select 分数段,
来凤县=sum(case when unit='来凤县' then 分数 else 0 end),
建始县=sum(case when unit='建始县' then 分数 else 0 end),
咸丰县=sum(case when unit='咸丰县' then 分数 else 0 end)
from cte group by 分数段
--方法二
with cte as
(
select unit,分数=[0],分数段='0' from tb
union all
select unit,[0-80],'0-80' from tb
union all
select unit,[80-160],'80-160' from tb
)
select 分数段,
来凤县,
建始县,
咸丰县
from cte
pivot
(
sum(分数)
for unit in (来凤县,建始县,咸丰县)
) as pvt
还是自身不行啊
print @s+' '+@s2+' '+@s3+' exec('+@s4+')' --显示执行语句這一段可以看到生成的語句,為了動態和字符數的限制用了動態多變量你一層層編譯變量,可以嘗試理解這种寫法