22,209
社区成员
发帖
与我相关
我的任务
分享
create table A(id int, 车号 varchar(20))
insert into a
select 1 ,'辽A1111' union all
select 2 ,'辽B2222'
create table B(
id int,A_id int,
车号 varchar(10),
箱号 varchar(10),
封号 varchar(10),
客户 varchar(10))
insert into B
SELECT 1,1,'辽A1111','001','001','张三' UNION ALL
SELECT 2,1,'辽A1111','002','002','李四' UNION ALL
SELECT 3,2,'辽B2222','003','003','王五'
go
if OBJECT_ID('tempdb..#temp') is not null
drop table #temp
select *,
(select count(*) from B where t.A_id = b.A_id and t.id>=b.id) rn
into #temp
from B t
declare @sql varchar(4000)
declare @sql_t varchar(4000)
set @sql = ''
set @sql_t = ''
select @sql = @sql + ',max(case when rn ='+CAST(rn as varchar)+' then 箱号 else '''' end) 箱号'
+ ',max(case when rn ='+CAST(rn as varchar)+' then 封号 else '''' end) 封号'
from #temp
group by rn
select @sql_t = @sql_t + '+max(case when rn ='+CAST(rn as varchar)+' then ''/''+客户 else '''' end)'
from #temp
group by rn
set @sql = 'select a_id as id,车号'+@sql + ',stuff('+stuff(@sql_t,1,1,'')+',1,1,'''') as 客户'+
' from #temp
group by a_id,车号'
exec(@sql)
/*
id 车号 箱号 封号 箱号 封号 客户
1 辽A1111 001 001 002 002 张三/李四
2 辽B2222 003 003 王五
*/
WITH a1 (id,车号) AS
(
SELECT 1,'辽A1111' UNION ALL
SELECT 2,'辽B2222'
)
,a2 (id,表A_id,车号,箱号,封号,客户) AS
(
SELECT 1,1,'辽A1111','001','001','张三' UNION ALL
SELECT 2,1,'辽A1111','002','002','李四' UNION ALL
SELECT 3,2,'辽B2222','003','003','王五'
)
SELECT a.车号,b.箱号,b.封号
,CASE WHEN b.箱号=c.箱号 THEN '' ELSE c.箱号 END 箱号
,CASE WHEN b.封号=c.封号 THEN '' ELSE c.封号 END 封号
,d.客户
FROM a1 a
CROSS APPLY (SELECT TOP 1 箱号,封号 FROM a2 WHERE 表A_id=a.id ORDER BY 箱号) b
CROSS APPLY (SELECT TOP 1 箱号,封号 FROM a2 WHERE 表A_id=a.id ORDER BY 箱号 desc) c
CROSS APPLY (SELECT STUFF((SELECT '/'+客户 FROM a2 WHERE 表A_id=a.id FOR XML PATH('')),1,1,'') 客户) d