两个表合并数据 求解
表 T_AQQ
IDMCLASSMyCon
1L-1测试撒阿斯顿
2L-1背后是否是否
3L-2看看是的废卡萨斯
4L-3SADASD删除是否
5L-1控件库v刹小
表T_SQQ
MCLASSSNO
L-1223
L-234
L-3876
如何得到这样的表
MCLASSSNOMyCon
L-1223测试撒阿斯顿 背后是否是否 控件库v刹小
L-234看看是的废卡萨斯
....
改怎么查询？谢谢
[解决办法]
CREATE TABLE #t_AQQ
(ID INT,MCLASS VARCHAR(20),MYCON VARCHAR(20))
INSERT INTO #t_AQQ
SELECT '1','L-1','测试撒阿斯顿'
UNION ALL
SELECT '2','L-1','背后是否是否'
UNION ALL
SELECT '3','L-2','看看是的废卡萨斯'
UNION ALL
SELECT '4','L-3','SADASD删除是否'
UNION ALL
SELECT '5','L-1','控件库v刹小'
CREATE TABLE #t_SQQ
(MCLASS VARCHAR(20),SNO VARCHAR(20))
INSERT INTO #t_SQQ
SELECT 'L-1','223'
UNION ALL
SELECT 'L-2','34'
UNION ALL
SELECT 'L-3','876'
SELECT t1.MCLASS,
#t_SQQ.SNO,
mycon=STUFF((SELECT ','+[mycon] FROM #t_AQQ t WHERE t.MCLASS=t1.MCLASS FOR XML PATH('')), 1, 1, '')
FROM #t_AQQ t1
JOIN #t_SQQ ON t1.MCLASS = #t_SQQ.MCLASS
GROUP BY t1.MCLASS,#t_SQQ.SNO
use tempdb
if object_id ('T_AQQ') is not null drop table T_AQQ
if object_id ('T_SQQ') is not null drop table T_SQQ
create table T_AQQ(ID int,MCLASS varchar(20),MyCon varchar(max))
insert into T_AQQ
select 1,'L-1','A' union all
select 2,'L-1','B' union all
select 3,'L-2','C' union all
select 4,'L-3','D' union all
select 5,'L-1','E'
create table T_SQQ(MCLASS varchar(20),SNO int)
insert into T_SQQ
select 'L-1',100 union all
select 'L-2',200 union all
select 'L-3',300
;
with T as
(
select T_AQQ.*,T_SQQ.SNO
from T_AQQ join T_SQQ on T_AQQ.MCLASS = T_SQQ.MCLASS
)
select MCLASS,SNO,(select MyCon+' ' from T as b where a.MCLASS = b.MCLASS for xml path('')) as MyCon
from T as a
group by a.MCLASS,a.SNO
