求一个SQL统计语句。。

[javapopo]

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斤

[LongRui888]

--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)
/*
col1	A1	重量	A2	重量	A3	重量
code1	A1	10克	A2	20克	A3	30克
code2	A1	50克	A2	60克	A3	70斤
code3	0	0	0	0	A3	10斤
*/
    

[Joylwu]

create table tb_test1
(id	int,
 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斤
*/

[LongRui888]

修改一下：

--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)
/*
col1	A1	重量	A2	重量	A3	重量
code1	A1	10克	A2	20克	A3	30克
code2	A1	50克	A2	60克	A3	70斤
code3	A1	0	A2	0	A3	10斤
*/

[Joylwu]

引用 4 楼 yupeigu 的回复:

修改一下：

--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)
/*
col1	A1	重量	A2	重量	A3	重量
code1	A1	10克	A2	20克	A3	30克
code2	A1	50克	A2	60克	A3	70斤
code3	A1	0	A2	0	A3	10斤
*/

以上第34、35行是多余的