请教一个比较有难度的行转列SQL的写法？
create table test_pgd_emp
(
emp_id varchar(10),
emp_name varchar(20)
)
insert into test_pgd_emp select '0001 ', '张三 '
insert into test_pgd_emp select '0002 ', '李四 '
insert into test_pgd_emp select '0003 ', '王五 '
insert into test_pgd_emp select '0004 ', '赵六 '
insert into test_pgd_emp select '0005 ', '吴七 '
go
create table test_pgd1 --派工单测试表
(
pd_date datetime,--派工日期
kh_name varchar(50),--客户姓名
cx varchar(50),--车型
ht_bh varchar(50),--合同编号
zzbw varchar(50),--制作部位
zzbw_mx varchar(50),--制作部位明细
sj int,--数量
gsde decimal(13,2),--工时定额
sjwgsi datetime,--实际完工时间
xmfzr varchar(50),--项目负责人
xmfzrgsf decimal(13,2),--项目负责人工时费
zzr varchar(50),--制作人
zzrgsf decimal(13,2),--制作人工时费
gsf decimal(13,2),--合计工时费
xz varchar(50),-- 小组
pgbh varchar(50)--派工单编号
)
insert into test_pgd1 select '2007-7-18 11:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '冲压下料 ',1,4, ' ', '00001 ',10, '00001 ',5,80, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 11:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '冲压下料 ',1,4, ' ', '00002 ',null, '00002 ',5,80, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 11:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '冲压下料 ',1,4, ' ', '00003 ',null, '00003 ',5,80, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 11:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '冲压下料 ',1,4, ' ', '00004 ',null, '00004 ',5,80, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 11:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '冲压下料 ',1,4, ' ', '00005 ',null, '00005 ',20,80, '2组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 12:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '折弯 ',1,3, ' ', '00001 ',10, '00001 ',30,100, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 12:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '折弯 ',1,3, ' ', '00002 ',10, '00002 ',30,100, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 12:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '折弯 ',1,3, ' ', '00003 ',null, '00003 ',20,100, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 12:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '折弯 ',1,3, ' ', '00004 ',null, '00004 ',null,100, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 12:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '折弯 ',1,3, ' ', '00005 ',null, '00005 ',null,100, '2组 ', 'HTZZ-52 '
select * from test_pgd1
/*
想要得到这样的工时表（按照ht_bh,zzbw,zzbw_mx进行分组统计）
pd_date kh_name cx ht_bh pgbh zzbw zzbw_mx sj gsde sjwgsj xmfzr zzr gsf
2007-7-18 11:28 苏良良 栏板半挂车 HTZZ-52 HTZZ-52 冲压 冲压下料 1 4 null 张三(1组)10元 张三(1组)5元,李四(1组)5元,王五(1组)5元,赵六(1组)5元,吴七(2组)20元 80
2007-7-18 12:28 苏良良 栏板半挂车 HTZZ-52 HTZZ-52 冲压 折弯 1 3 null 张三(1组)10元,李四(1组)10元 张三(1组)30元,李四(1组)30元,王五(1组)20元 100
要求 ：1.能够按照ht_bh进行查询
2.行转列语句根据记录动态产生,不能写死。
*/
drop table test_pgd_emp
drop table test_pgd1
[解决办法]
用函数解决
create function fn_str(
@ht_bh varchar(50), --合同编号
@zzbw varchar(50), --制作部位
@zzbw_mx varchar(50), --制作部位明细
@type int --1 项目负责人 2 制作人
)
returns varchar(100)
as
begin
declare @r varchar(100)
set @r= ' '
if @Type=1
select @r=@r+ ', '+b.emp_name+ '( '+a.xz+ ') '+cast(a.xmfzrgsf as varchar)+ '元 '
from test_pgd1 a,test_pgd_emp b where a.xmfzr=b.emp_id
and a.ht_bh=@ht_bh and a.zzbw=@zzbw and a.zzbw_mx=@zzbw_mx
else
select @r=@r+ ', '+b.emp_name+ '( '+a.xz+ ') '+cast(a.zzrgsf as varchar)+ '元 '
from test_pgd1 a,test_pgd_emp b where a.xzr=b.emp_id
and a.ht_bh=@ht_bh and a.zzbw=@zzbw and a.zzbw_mx=@zzbw_mx
set @r=stuff(@r,1,1, ' ')
return @r
end
go
--查询
select
max(pd_date) as pd_date,
max(kh_name) as kh_name,
max(cx) as cx,
ht_bh,
max(pgbh) as pgbh,
zzbw,
zzbw_mx,
sum(sj) as sj,
max(gsde) as gsde,
max(sjwgsj) as sjwgsj,
dbo.fn_str(ht_bh,zzbw,zzbw_mx,1) as xmfzr,
dbo.fn_str(ht_bh,zzbw,zzbw_mx,2) as zzr,
max(gsf) as gsf
from test_pgd1
group by ht_bh,zzbw,zzbw_mx
[解决办法]
--创建测试环境
create table test_pgd_emp
(
emp_id varchar(10),
emp_name varchar(20)
)
insert into test_pgd_emp select '00001 ', '张三 '
insert into test_pgd_emp select '00002 ', '李四 '
insert into test_pgd_emp select '00003 ', '王五 '
insert into test_pgd_emp select '00004 ', '赵六 '
insert into test_pgd_emp select '00005 ', '吴七 '
go
create table test_pgd1 --派工单测试表
(
pd_date datetime,--派工日期
kh_name varchar(50),--客户姓名
cx varchar(50),--车型
ht_bh varchar(50),--合同编号
zzbw varchar(50),--制作部位
zzbw_mx varchar(50),--制作部位明细
sj int,--数量
gsde decimal(13,2),--工时定额
sjwgsi datetime,--实际完工时间
xmfzr varchar(50),--项目负责人
xmfzrgsf decimal(13,2),--项目负责人工时费
zzr varchar(50),--制作人
zzrgsf decimal(13,2),--制作人工时费
gsf decimal(13,2),--合计工时费
xz varchar(50),-- 小组
pgbh varchar(50)--派工单编号
)
insert into test_pgd1 select '2007-7-18 11:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '冲压下料 ',1,4, ' ', '00001 ',10, '00001 ',5,80, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 11:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '冲压下料 ',1,4, ' ', '00002 ',null, '00002 ',5,80, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 11:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '冲压下料 ',1,4, ' ', '00003 ',null, '00003 ',5,80, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 11:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '冲压下料 ',1,4, ' ', '00004 ',null, '00004 ',5,80, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 11:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '冲压下料 ',1,4, ' ', '00005 ',null, '00005 ',20,80, '2组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 12:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '折弯 ',1,3, ' ', '00001 ',10, '00001 ',30,100, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 12:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '折弯 ',1,3, ' ', '00002 ',10, '00002 ',30,100, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 12:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '折弯 ',1,3, ' ', '00003 ',null, '00003 ',20,100, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 12:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '折弯 ',1,3, ' ', '00004 ',null, '00004 ',null,100, '1组 ', 'HTZZ-52 '
insert into test_pgd1 select '2007-7-18 12:28:00 ', '苏良良 ', '栏板半挂车 ', 'HTZZ-52 ', '冲压 ', '折弯 ',1,3, ' ', '00005 ',null, '00005 ',null,100, '2组 ', 'HTZZ-52 '
--select * from test_pgd1
GO
--创建函数
Create Function F_Get_pgd_emp(@ht_bh varchar(50), @zzbw varchar(50), @zzbw_mx varchar(50), @Flag Bit)
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ' '
If(@Flag = 0)
Select @S = @S + ', ' + B.emp_name + '( ' + xz + ') ' + Cast(Cast(xmfzrgsf As Int) As Varchar) + '元 ' From test_pgd1 A
Inner Join test_pgd_emp B On A.xmfzr = B.emp_id
Where ht_bh = @ht_bh And zzbw = @zzbw And zzbw_mx = @zzbw_mx And xmfzrgsf Is Not Null
Order By xmfzr
Else
Select @S = @S + ', ' + B.emp_name + '( ' + xz + ') ' + Cast(Cast(zzrgsf As Int) As Varchar) + '元 ' From test_pgd1 A
Inner Join test_pgd_emp B On A.zzr = B.emp_id
Where ht_bh = @ht_bh And zzbw = @zzbw And zzbw_mx = @zzbw_mx And zzrgsf Is Not Null
Order By xmfzr
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
--测试
Select
Max(pd_date) As pd_date,
Max(kh_name) As kh_name,
Max(cx) As cx,
ht_bh,
Max(pgbh) As pgbh,
zzbw,
zzbw_mx,
Max(sj) As sj,
Max(gsde) As gsde,
Max(sjwgsi) As sjwgsi,
dbo.F_Get_pgd_emp(ht_bh, zzbw, zzbw_mx, 0) As xmfzr,
dbo.F_Get_pgd_emp(ht_bh, zzbw, zzbw_mx, 1) As zzr,
Max(gsf) As gsf
From
test_pgd1
Group By
ht_bh,
zzbw,
zzbw_mx
Order By
pd_date
GO
--删除测试环境
Drop Function F_Get_pgd_emp
drop table test_pgd_emp
drop table test_pgd1
--结果
/*
pd_datekh_namecxht_bhpgbhzzbwzzbw_mxsjgsdesjwgsjxmfzrzzrgsf
2007-07-18 11:28:00.000苏良良栏板半挂车HTZZ-52HTZZ-52冲压冲压下料14.001900-01-01 00:00:00.000张三(1组)10元张三(1组)5元,李四(1组)5元,王五(1组)5元,赵六(1组)5元,吴七(2组)20元80.00
2007-07-18 12:28:00.000苏良良栏板半挂车HTZZ-52HTZZ-52冲压折弯13.001900-01-01 00:00:00.000张三(1组)10元,李四(1组)10元张三(1组)30元,李四(1组)30元,王五(1组)20元100.00
*/
