Version: 19
Sometimes for presentation purposes it is nice to be able to show a list of values from the database in a comma separated list instead of a table structure. One mechanism to achieve this is with the SQL COALESCE function, another is with the SQL FOR XML function.
Examples:
SELECT EV200_EVT_DESC
FROM dbo.EV200_EVENT_MASTER
WHERE EV200_EVT_TYPE = 'CON'
Will return
Jack Johnson
Taylor Swift
Lady Gaga
Green Day
Coldplay
Using the COALESCE function
DECLARE @EVENTS VARCHAR(MAX)
SELECT @EVENTS = COALESCE(@EVENTS + ', ', '') + EV200_EVT_DESC
FROM dbo.EV200_EVENT_MASTER
WHERE EV200_EVT_TYPE = 'CON'
SELECT @EVENTS
Will return
Jack Johnson, Taylor Swift, Lady Gaga, Green Day, Coldplay
Using the FOR XML function
SELECT STUFF(
(SELECT ', ' + EV200_EVT_DESC
FROM dbo.EV200_EVENT_MASTER
WHERE EV200_EVT_TYPE = 'CON'
FOR XML PATH, TYPE).value('.[1]',
'nvarchar(max)'), 1, 1, '')
Will return
Jack Johnson, Taylor Swift, Lady Gaga, Green Day, Coldplay
Comments
6 comments
Hello,
Thank you for this: it's exactly what I was looking for, but need a little more help...
I need to take this a step further and expand the WHERE to more than one limit (in this case, say, making the TYPE concerts and special events) and then grouping the results by type - so you end up with two unique rows of comma separated lists.
How could I do that?
Zak.
0 upvotes
A simple approach would be to use multiple requests and a UNION:
DECLARE @EVENTS_1 VARCHAR(MAX)
DECLARE @EVENTS_2 VARCHAR(MAX)
SELECT @EVENTS_1 = COALESCE(@EVENTS_1 + ', ', '') + EV200_EVT_DESC
FROM dbo.EV200_EVENT_MASTER
WHERE EV200_EVT_TYPE = 'TYPE1'
SELECT @EVENTS_2 = COALESCE(@EVENTS_2 + ', ', '') + EV200_EVT_DESC
FROM dbo.EV200_EVENT_MASTER
WHERE EV200_EVT_TYPE = 'TYPE2'
SELECT 'Type 1', @EVENTS_1
UNION ALL
SELECT 'Type 2', @EVENTS_2
0 upvotes
Thank you for the quick answer.
I'm guilty of not just asking my real question, so here it is:
What about replacing static WHERE criteria with a dynamic range, and then grouping by that? Task at hand:
Building an EZwriter source for an email template, to pull an Event's Level 3 Function Schedule details and post them as a comma separated list, by day. I have the comma separated list working, using either COALESCE or the XML function, and the overall by day grouping, but the comma separated list is printing all details for all days every time.
And the same idea with bookings: list an event's bookings as a comma separated list, by day.
How do I get the COALESCE details to print each day's info separately?
Thank you,
Zak.
0 upvotes
An approach is to include a child select command within the parent which relies upon data returned from the parent. Specifically, for your booking requirement, the following will produce a daily break down with a list of spaces per day:
DECLARE @ORG_CODE VARCHAR(2) = 'YOUR_ORG_CODE'
DECLARE @EVENT_ID INT = YOUR_EVENT_ID
SELECT
EV803_PARENT.EV803_BKG_DATE,
(
SELECT STUFF(
(SELECT DISTINCT ', ' + EV800_SPACE_DESC
FROM
EV803_SPACE_BKD_DTL AS EV803_CHILD INNER JOIN
EV800_SPACE_MASTER ON
EV803_ORG_CODE = EV800_ORG_CODE AND
EV803_BKD_SPACE = EV800_SPACE_CODE
WHERE
EV803_ORG_CODE = @ORG_CODE AND
EV803_EVT_ID = @EVENT_ID
AND
EV803_CHILD.EV803_BKG_DATE = EV803_PARENT.EV803_BKG_DATE
FOR XML PATH, TYPE).value('.[1]',
'nvarchar(max)'), 1, 1, '')
) AS SPACES
FROM
EV803_SPACE_BKD_DTL AS EV803_PARENT
WHERE
EV803_ORG_CODE = @ORG_CODE AND
EV803_EVT_ID = @EVENT_ID
GROUP BY
EV803_PARENT.EV803_BKG_DATE
0 upvotes
Thanks again for the help: That validated the direction I had been heading with the my Functions query, and helped identify the piece I was missing.
But, two things:
- using @EVENT_ID as a declared variable throws an error "Incorrect syntax near '999999999', even after replacing the temp data with an actual event number. Changing @EVENT_ID to a some other name (e.g. @ID) fixes the problem.
- inserting the @EVENT or !EVENT! ezwriter parameter (to contextually select the event id) throws an error: ' "VALUE" is not a valid function, property, or field. ' What parameter should I be using instead to dynamically select the Event ID?
Thank you,
Zak.
V20.90N
-------
UPDATE: getting rid of the VALUE variable in the FOR XML clause and changing the argument to
FOR XML PATH (''), 1, 1,'')
makes the @EVENT ezwriter parameter usable.
0 upvotes
Hi Zac,
I think I have had a similar issue with these parameters in similar circumstances.
What is happening is that when EZWriter sees the @EVENT string, it replaces it immediately with your event ID. What you need to do is break it up - something like '@EV' + 'ENT'.
Hope that helps.
0 upvotes
Please sign in to leave a comment.