求一个SQL统计语句。。
tb1
id col1 col2 col3
11 A1 10 克
11 A2 20 克
11 A3 30 克
12 A1 50 克
12 A2 60 克
12 A3 70 斤
13 A3 10 斤
tb2
id col1
11 code1
12 code2
13 code3
tb1.id=tb2.id
需要的结果
------------------------
代码 A1 重量 A2 重量 A3 重量
code1 A1 10克 A2 20克 A3 30克
code2 A1 50克 A2 60克 A3 70斤
code3 A1 0 A2 0 A3 10斤
[解决办法]
--drop table tb1,tb2
--go
create table tb1(id int,col1 varchar(10), col2 int, col3 varchar(10))
insert into tb1
select 11 ,'A1', 10 ,'克' union all
select 11 ,'A2', 20 ,'克' union all
select 11 ,'A3', 30 ,'克' union all
select 12 ,'A1', 50 ,'克' union all
select 12 ,'A2', 60 ,'克' union all
select 12 ,'A3', 70 ,'斤' union all
select 13 ,'A3', 10 ,'斤'
create table tb2 (id int, col1 varchar(10))
insert into tb2
select 11 ,'code1' union all
select 12 ,'code2' union all
select 13 ,'code3'
go
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql +
',max(case when tb1.col1 = '''+tb1.col1+
''' then tb1.col1 else ''0'' end) as ['+tb1.col1+']' +
',max(case when tb1.col1 = '''+tb1.col1+
''' then cast(tb1.col2 as varchar)+tb1.col3 else ''0'' end) as [重量]'
from tb1
inner join tb2
on tb1.id = tb2.id
group by tb1.col1
select @sql = 'select tb2.col1' + @sql+
'from tb1
inner join tb2
on tb1.id = tb2.id
group by tb2.col1
'
exec(@sql)
/*
col1A1重量A2重量A3重量
code1A110克A220克A330克
code2A150克A260克A370斤
code30000A310斤
*/
create table tb_test1
(idint,
col1 nvarchar(255),
col2 nvarchar(255),
col3 nvarchar(255)
)
go
insert into tb_test1
select 11,'A1','10',N'克' union all
select 11,'A2','20',N'克' union all
select 11,'A3','30',N'克' union all
select 12,'A1','50',N'克' union all
select 12,'A2','60',N'克' union all
select 12,'A3','70',N'斤' union all
select 13,'A3','10',N'斤'
go
create table tb_test2
(
id int,
col1 nvarchar(255)
)
go
insert into tb_test2
select '11','code1' union all
select '12','code2' union all
select '13','code3'
--查询
with tt as
(
select a.id,b.col1 as code,a.col1,a.col2,a.col3
from tb_test1 as a join tb_test2 as b on a.id=b.id
)
select code as '代码',
'A1' as A1,
max(case col1 when 'A1' then cast(col2 as nvarchar(255))+col3 else '0' end) as '重量',
'A2' as A2,
max(case col1 when 'A2' then cast(col2 as nvarchar(255))+col3 else '0' end) as '重量',
'A3' as A3,
max(case col1 when 'A3' then cast(col2 as nvarchar(255))+col3 else '0' end) as '重量'
from tt
group by code
----drop table tb1,tb2
----go
--create table tb1(id int,col1 varchar(10), col2 int, col3 varchar(10))
--insert into tb1
--select 11 ,'A1', 10 ,'克' union all
--select 11 ,'A2', 20 ,'克' union all
--select 11 ,'A3', 30 ,'克' union all
--select 12 ,'A1', 50 ,'克' union all
--select 12 ,'A2', 60 ,'克' union all
--select 12 ,'A3', 70 ,'斤' union all
--select 13 ,'A3', 10 ,'斤'
--create table tb2 (id int, col1 varchar(10))
--insert into tb2
--select 11 ,'code1' union all
--select 12 ,'code2' union all
--select 13 ,'code3'
--go
/*
代码 A1 重量 A2 重量 A3 重量
code1 A1 10克 A2 20克 A3 30克
code2 A1 50克 A2 60克 A3 70斤
code3 A1 0 A2 0 A3 10斤
*/
IF OBJECT_ID('tempdb..#t','u')IS NOT NULL
DROP TABLE #t
SELECT a.id,a.col1 NAME ,CAST(a.col2 AS varchar)+a.col3 AS 重量 ,b.col1 代码 INTO #t
FROM tb1 a left JOIN tb2 b ON a.id=b.id
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(NAME)+'=max(case when [NAME]='+quotename(NAME,'''')+' then [NAME] else '+quotename(NAME,'''')+' end)'
+','+quotename('重量')+'=max(case when [NAME]='+quotename(NAME,'''')+' then [重量] else ''0'' end)'
from #t group by NAME
exec('select [代码]'+@s+' from #t group by [代码]')
/*
代码 A1 重量 A2 重量 A3 重量
---------- ---------- ---------------------------------------- ---------- ---------------------------------------- ---------- ----------------------------------------
code1 A1 10克 A2 20克 A3 30克
code2 A1 50克 A2 60克 A3 70斤
code3 A1 0 A2 0 A3 10斤
*/
--drop table tb1,tb2
--go
create table tb1(id int,col1 varchar(10), col2 int, col3 varchar(10))
insert into tb1
select 11 ,'A1', 10 ,'克' union all
select 11 ,'A2', 20 ,'克' union all
select 11 ,'A3', 30 ,'克' union all
select 12 ,'A1', 50 ,'克' union all
select 12 ,'A2', 60 ,'克' union all
select 12 ,'A3', 70 ,'斤' union all
select 13 ,'A3', 10 ,'斤'
create table tb2 (id int, col1 varchar(10))
insert into tb2
select 11 ,'code1' union all
select 12 ,'code2' union all
select 13 ,'code3'
go
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql +
',max(case when tb1.col1 = '''+tb1.col1+
''' then tb1.col1 else '''+tb1.col1+''' end) as ['+tb1.col1+']' +
',max(case when tb1.col1 = '''+tb1.col1+
''' then cast(tb1.col2 as varchar)+tb1.col3 else ''0'' end) as [重量]'
from tb1
inner join tb2
on tb1.id = tb2.id
group by tb1.col1
select @sql = 'select tb2.col1' + @sql+
'from tb1
inner join tb2
on tb1.id = tb2.id
group by tb2.col1
'
exec(@sql)
/*
col1A1重量A2重量A3重量
code1A110克A220克A330克
code2A150克A260克A370斤
code3A10A20A310斤
*/
