June 15, 2011 at 7:34 pm
Does anyone know how to turn a result set with four rows into one row comma delimited?
For example, turn
Value
--------
line1
line2
line3
line4
into
Vaue
------
line1, line2, line3, line4
There isn't a common value between the four rows (such as a category id), and every other time I've concatenated rows, there has been some common element.
I thought FOR XML PATH would be a good way to go. I've gotten all of the values to all be in one row, but with the XML tags. Is there a way to remove the tags?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
June 15, 2011 at 7:57 pm
Like this:
; with cte (items) as
( select 'line1'
union all select 'line2'
union all select 'line3'
union all select 'line4'
)
select ','+items
from cte
for xml path(''), type
June 16, 2011 at 6:38 am
I never realized that concatenating a literal (e.g. ',' or even '') to the field name causes FOR XML PATH to remove the XML tags.
Thanks.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
June 16, 2011 at 6:49 am
So the question is, why are the tags for JobStatus not hidden when all of the rest are?
CREATE TABLE [dbo].[SisJobs](
[Name] [nvarchar](128) NOT NULL,
[LastRunDate] [datetime] NULL,
[JobStatusCode] [int] NOT NULL
)
INSERT INTO SisJobs
SELECT 'Test', GETDATE(), 231
SELECT '' + Name, '' + CASE JobStatusCode
WHEN 230 THEN 'Success'
WHEN 231 THEN 'Running'
WHEN 232 THEN 'Fail'
END AS JobStatus,
'' + LastRunDate
FROM SisJobs
ORDER BY Name
FOR XML PATH (''), TYPE
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply