大师,我有一个像下面的桌子
Id Name Source Value
1 a Dx C
2 b Rx G
3 C Tx H
我还有其他3个表,例如T1,T2和T3,其结构如下。基于父表中的“源”列,我需要获取“数据列”并显示为结果。如果多个记录匹配,则所有记录都以逗号分隔
T1
Id Data
Dx 123
DX 011
T2
Id Data
Rx 456
Rx 022
T3
Id Data
Tx 789
我需要像T-SQL中的输出
Id Name Source Value Data
1 a Dx C 123,011
2 b Rx G 456 ,022
3 C Tx H 789
当没有成功时,我尝试了Case。需要输入
试试这个:
DECLARE @t TABLE(ID INT, Name CHAR(1), Source CHAR(2), Value CHAR(1))
DECLARE @t1 TABLE(ID CHAR(2), Data NVARCHAR(20))
DECLARE @t2 TABLE(ID CHAR(2), Data NVARCHAR(20))
DECLARE @t3 TABLE(ID CHAR(2), Data NVARCHAR(20))
INSERT INTO @t VALUES
(1, 'a', 'Dx', 'C'),
(2, 'b', 'Rx', 'G'),
(3, 'c', 'Tx', 'H')
INSERT INTO @t1 VALUES('Dx', '1231')
INSERT INTO @t1 VALUES('Dx', '1232')
INSERT INTO @t1 VALUES('Dx', '1233')
INSERT INTO @t2 VALUES('Rx', '4561')
INSERT INTO @t2 VALUES('Rx', '4562')
INSERT INTO @t3 VALUES('Tx', '789')
SELECT t.ID ,
t.Name ,
t.Source ,
t.Value ,
COALESCE(c1.Data1, c2.Data2, c3.Data3) AS Data
FROM @t t
OUTER APPLY (SELECT STUFF((SELECT ',' + Data
FROM @t1 t1 WHERE t.Source = t1.ID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'') AS Data1) c1
OUTER APPLY (SELECT STUFF((SELECT ',' + Data
FROM @t2 t2 WHERE t.Source = t2.ID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'') AS Data2) c2
OUTER APPLY (SELECT STUFF((SELECT ',' + Data
FROM @t3 t3 WHERE t.Source = t3.ID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'') AS Data3) c3
输出:
ID Name Source Value Data
1 a Dx C 1231,1232,1233
2 b Rx G 4561,4562
3 c Tx H 789
带有CASE
表达式的版本:
SELECT t.ID ,
t.Name ,
t.Source ,
t.Value ,
o.Data
FROM @t t
OUTER APPLY ( SELECT CASE t.Source
WHEN 'Dx' THEN STUFF((SELECT
',' + Data
FROM @t1 t1
WHERE t.Source = t1.ID
FOR XML PATH('') ,
TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
WHEN 'Rx' THEN STUFF((SELECT
',' + Data
FROM @t2 t2
WHERE t.Source = t2.ID
FOR XML PATH('') ,
TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
WHEN 'Tx' THEN STUFF((SELECT
',' + Data
FROM @t3 t3
WHERE t.Source = t3.ID
FOR XML PATH('') ,
TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
END AS DATA
) o
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句