我有两个像t1和t2的表。11
ID class name
-- -----------
1 basket weaving
2 Underwater Basketing
t2
ID POS DAYS
-- ---- -----
1 1 M
1 2 T
1 3 W
1 4 TH
1 5 F
2 1 M
2 2 W
2 3 F
我想从两个表中获取以下输出。
1 class DAYS
-- ----- -----
1 BASKET WEAVING MTWTHF
2 Underwater basketing MWF
查询1
SELECT ID, [class name], STUFF(List, 1 ,2, '') [DAYS]
FROM Table1 T1 CROSS APPLY
(
SELECT ', ' + [DAYS] [text()]
FROM Table2
WHERE ID = T1.ID
FOR XML PATH('')
) T2(List)
结果
ID class name DAYS
-- ----- -----
1 BASKET WEAVING M,T,W,TH,F
2 Underwater basketing M,W,F
如果您不希望使用逗号,它将为您提供以逗号分隔的日期列表,您可以使用以下
Query2
SELECT ID, [class name], List [DAYS]
FROM Table1 T1 CROSS APPLY
(
SELECT [DAYS] [text()]
FROM Table2
WHERE ID = T1.ID
FOR XML PATH('')
) T2(List)
结果
ID class name DAYS
-- ----- -----
1 BASKET WEAVING MTWTHF
2 Underwater basketing MWF
查询3
SELECT ID, [class name], STUFF(List, 1 ,2, '') [DAYS], STUFF(T3.Rooms, 1 ,2, '') [Rooms]
FROM Table1 T1 CROSS APPLY
(
SELECT ', ' + [DAYS] [text()]
FROM Table2
WHERE ID = T1.ID
FOR XML PATH('')
) T2(List)
CROSS APPLY
(
SELECT '/' + Rooms [text()]
FROM Table2
WHERE ID = T1.ID
FOR XML PATH('')
) T3(Rooms)
您可以通过执行以下操作来添加另一列.....
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句