I have 3 tables (Contact, ContactBridge, ContactLabel).
ContactLabel table has all Label Names associated with Contact (can be 1 label, can be 50 labels). To access them I have to go through ContactBridge table that stores LabelId.
Goal was to retrieve all Label Names in one row for each Contact.
I was able to figured out the query, however it executes super long (~1000 records takes almost 2 minutes).
declare @Contact table
(ContactId INT, ContactName VARCHAR(100))
INSERT INTO @Contact
VALUES
(78561, 'Tom Cruise'),
(54721, 'Iron Man')
declare @ContactBridge table
(BridgeId INT, ContactId INT, LabelId INT)
INSERT INTO @ContactBridge
VALUES
(1, 78561, 12),
(2, 54721, 34),
(3, 78561, 23),
(4, 54721, 67),
(5, 54721, 78),
(6, 78561, 34),
(7, 78561, 45),
(8, 54721, 56)
declare @ContactLabel table
(LabelId INT, LabelName VARCHAR(100))
INSERT INTO @ContactLabel
VALUES
(12, 'Actor'),
(23, 'Los Angeles'),
(34, 'Rich'),
(45, 'Married'),
(56, 'Single'),
(67, 'New York'),
(78, 'Superhero')
SELECT * FROM @Contact as c
--- Contact Labels in 1 single row
OUTER APPLY (
SELECT STUFF((
(SELECT ', ' labels.LabelName
FROM @ContactBridge AS bridge
JOIN @ContactLabel AS labels
ON labels.LabelId = bridge.LabelId
WHERE bridge.ContactId = c.ContactId
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')),1,1,''
) AS ContactLabels
) AS cl
Is there a way to make query run faster?
CodePudding user response:
use string_agg as follows
select C.ContactId,ContactName,
string_agg(LabelName,',') AS ContactLabels
from @Contact C
join @ContactBridge CB
ON CB.ContactId=C.ContactId
JOIN @ContactLabel CL
ON CL.LabelId=CB.LabelId
group by c.ContactId,ContactName
order by ContactName desc,string_agg(LabelName,',') asc