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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy