October 5, 2015 at 8:03 pm
Hi,
I'll say up front I am far from a T-SQL expert but I like to think I am quite intuitive 😀
Anyhow, I have a requirement where I have the following separate tables:
Table A:
FldA FldB
34
35
43
53
54
55
64
74
75
Table B:
FldC FldD
1Break Begin
2Break End
3Out
4In
5Dept
Desired Result:
FldA FldD
3 In;Dept
4 Out
5 Out;In;Dept
6 In
7 In;Dept
I have played around with the newly discovered 'for xml path' but I can't quite seem to get the sql syntax right. I'd appreciate anyone who has an appropriate SQL Select based solution to this.
TIA
October 5, 2015 at 8:24 pm
Quick solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TableA TABLE
(
FldA INT NOT NULL
,FldB INT NOT NULL
);
INSERT INTO @TableA(FldA,FldB)
VALUES (3,4)
,(3,5)
,(4,3)
,(5,3)
,(5,4)
,(5,5)
,(6,4)
,(7,4)
,(7,5)
;
DECLARE @TableB TABLE
(
FldC INT NOT NULL
,FldD VARCHAR(20) NOT NULL
);
INSERT INTO @TableB(FldC,FldD)
VALUES (1,'Break Begin')
,(2,'Break End' )
,(3,'Out' )
,(4,'In' )
,(5,'Dept' )
;
SELECT DISTINCT
A.FldA
,STUFF(
(SELECT
CHAR(44) + TB.FldD
FROM @TableA TA
INNER JOIN @TableB TB
ON TA.FldB = TB.FldC
where TA.FldA = A.FldA
ORDER BY TA.FldA,TA.FldB
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(100)'),1,1,'') AS FldD
FROM @TableA A;
Results
FldA FldD
----------- -------------
3 In,Dept
4 Out
5 Out,In,Dept
6 In
7 In,Dept
October 5, 2015 at 9:23 pm
Worked a treat thanks for your quick reply. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply