一主从表，主表一条记录对应子表多条记录
如
主表A
id name value
1 a s子表
id ss yy
1 as 21
1 23 12
……想要的结果
id name value ss yy ss1 yy1 ……
1 a ss as 21 23 12 ……请问该怎么写？
不是把多条记录合并到一个字段里面，而是子表有多少个数据就显示多少个字段。
如
主表A
id name value
1 a s子表
id ss yy
1 as 21
1 23 12
……想要的结果
id name value ss yy ss1 yy1 ……
1 a ss as 21 23 12 ……请问该怎么写？
不是把多条记录合并到一个字段里面，而是子表有多少个数据就显示多少个字段。
[code=SQL][/c
create table t_main(id int,name varchar(10),value varchar(10))
create table t_main_detail(id int,ss varchar(10),yy varchar(10))insert into t_main
select 1,'a','aaa'
insert into t_main_detail
select 1,'ccccc','cccccc'
union all
select 1,'ddddd','dddddd'
union all
select 1,'eeeee','eeeeee'
with tmp_rn
as
(select *,row_number()over(partition by id order by ss) rn from t_main_detail)select * from t_main a
left join (select * from tmp_rn where rn=1) b on a.id=b.id
left join (select * from tmp_rn where rn=2) c on a.id=c.id
left join (select * from tmp_rn where rn=3) d on a.id=d.id ode]
insert into a values(1 , 'a' , 's') create table b(id int, ss varchar(10), yy int)
insert into b values(1 , 'as' , 21 )
insert into b values(1 , '23' , 12 )declare @sql varchar(8000)
set @sql = 'select t1.name , t1.value , t2.* from a t1 , (select id '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then ss else '''' end) [ss' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then yy else 0 end) [yy' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = (select count(1) from b where id = t.id and ss < t.ss) + 1 from b t) m) as k
set @sql = @sql + ' from (select t.* , px = (select count(1) from b where id = t.id and ss < t.ss) + 1 from b t) m group by id) t2 where t1.id = t2.id'
exec(@sql) drop table a , b/*
name value id ss1 yy1 ss2 yy2
---------- ---------- ----------- ---------- ----------- ---------- -----------
a s 1 23 12 as 21
*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(1),[value] varchar(1))
insert [A]
select 1,'a','s'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[ss] varchar(2),[yy] int)
insert [B]
select 1,'as',21 union all
select 1,'23',12declare @sql varchar(max)select @sql = isnull(@sql+',
','')+'max(case rowid when '+ltrim(rowid)+' then ss else '''' end) as [ss'+ltrim(rowid)+'],
max(case rowid when '+ltrim(rowid)+' then yy else '''' end) as [yy'+ltrim(rowid)+']'
from
(select row_number() over (partition by t.id order by ss) rowid ,
t.*,r.ss,r.yy from [A] t join [B] r on t.id = r.id) hselect @sql = 'select id,name,value,
'+@sql+'
from (select row_number() over (partition by t.id order by ss) rowid ,
t.*,r.ss,r.yy from [A] t join [B] r on t.id = r.id) h
group by id,name,value'exec(@sql)
id name value ss1 yy1 ss2 yy2
----------- ---- ----- ---- ----------- ---- -----------
1 a s 23 12 as 21(1 行受影响)
主表：
RecordID
hC+StbBtTwGNTeCLiRZoWg
S08kN7oETJ6eI3RsF/Wfkw
XBvmLJ+YRrq5GBXx4nvphg
子表：
SelfID JoinID TestNo DestroyLoad LoadValue DestroyLoad1 LoadValue1
27417 hC+StbBtTwGNTeCLiRZoWg 1 537.64 23.9 NULL NULL
27418 hC+StbBtTwGNTeCLiRZoWg 2 547.24 24.3 NULL NULL
27419 hC+StbBtTwGNTeCLiRZoWg 3 539.57 24.0 NULL NULL
27426 S08kN7oETJ6eI3RsF/Wfkw 1 445.53 19.8 NULL NULL
27427 S08kN7oETJ6eI3RsF/Wfkw 2 437.25 19.4 NULL NULL
27428 S08kN7oETJ6eI3RsF/Wfkw 3 464.19 20.6 NULL NULL
27429 XBvmLJ+YRrq5GBXx4nvphg 1 423.88 18.8 NULL NULL
27430 XBvmLJ+YRrq5GBXx4nvphg 2 417.56 18.6 NULL NULL目的：
把JoinID相同的多行数据变成一行显示，但不合并字段27417 hC+StbBtTwGNTeCLiRZoWg 1 537.64 23.9 NULL NULL 27418 2 547.24 24.3 NULL NULL 27419 3 539.57 24.0 NULL NULL