AnsweredAssumed Answered

Combine rows into comma separated values

Question asked by DonKiser on Sep 29, 2020
Latest reply on Sep 30, 2020 by DonKiser

I have the following query:

Select  erc.Value as [ZONE] ,  el.Name as [LINE]
from [AFDB].[EventFrame].[EventFrame] ef

 

INNER JOIN [AFDB].[Asset].[Element] el ON el.ID = ef.PrimaryReferencedElementID
INNER JOIN [AFDB].[Asset].[ElementAttribute] ea ON ea.ElementID = ef.PrimaryReferencedElementID
INNER JOIN [AFDB].[Data].[Archive] erc on erc.ElementAttributeID = ea.ID
where  ef.StartTime >= '9/20/2020' and ef.EndTime <= '9/27/2020'  and ea.Name like 'ZONE'

Group BY erc.Value, el.Name
OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

 

This produces the following result in SQL Commander

ZONELINE
ZONEALINE1
ZONEALINE2
ZONEALINE3
ZONEBLINE5
ZONEBLINE6
ZONECLINE4

 

What I want the output to be

ZONELINE
ZONEALINE1, LINE2, LINE3
ZONEBLINE5, LINE6
ZONECLINE4

 

Searching around the internet pointed me to using STUFF and FOR XML PATH but I haven't been getting anywhere with that.

 

Select  erc.Value as [ZONE] ,
LINE = STUFF(
(SELECT DISTINCT ', ' + (el.Name)
from [AFDB].[EventFrame].[EventFrame] a
INNER JOIN [AFDB].[Asset].[Element] el ON el.ID = ef.PrimaryReferencedElementID
INNER JOIN [AFDB].[Asset].[ElementAttribute] ea ON ea.ElementID = a.PrimaryReferencedElementID
where  a.StartTime >= '9/20/2020' and a.EndTime <= '9/27/2020'  and ea.Name like 'ZONE'
FOR XML PATH ('')), 1, 1, '')
from [AFDB].[EventFrame].[EventFrame] b
INNER JOIN [AFDB].[Asset].[Element] el ON el.ID = b.PrimaryReferencedElementID
INNER JOIN [AFDB].[Asset].[ElementAttribute] ea ON ea.ElementID = ef.PrimaryReferencedElementID
where  b.StartTime >= '9/20/2020' and b.EndTime <= '9/27/2020'  and ea.Name like 'ZONE'

 

But I get the following error:

[SQL Parser] [Line 9:1] Missing ) at 'FOR'
[Line 9:14] Missing <IDENTIFIER> at '('

 

The output will be the query for a parameter in a SSRS report. The user will select the ZONE from the list and the query will use the LINE value(s) in another query.

Outcomes