March 9, 2016 at 3:06 am
Hi Team,
using below query to convert the columns in to comma seperated.
SELECT 1264, isnull(col1, '')+' - '+isnull(col2, '')
+' - '+isnull(col3, '')+' - '+isnull(col4, '')+' - '
+isnull(col5, '')+' - '+isnull(col6, '')
FROM unify WHERE id=100
if col3 value is null, am getting like below. how to avoid (- - ) in middle.
ABC - DEF - - GG - DKF - KDIL
Please suggest.
March 9, 2016 at 3:27 am
Would this work?
SELECT 1264, isnull(col1+' - ', '') +isnull(col2+' - ', '')
+isnull(col3 +' - ', '') + isnull(col4+' - ', '')
+isnull(col5 +' - ', '') + isnull(col6, '')
FROM unify WHERE id=100
-- Gianluca Sartori
March 9, 2016 at 4:09 am
Thank You,
Its working.
March 9, 2016 at 7:22 am
Or this
SELECT 1264,REPLACE( isnull(col1, '')+' - '+isnull(col2, '')
+' - '+isnull(col3, '')+' - '+isnull(col4, '')+' - '
+isnull(col5, '')+' - '+isnull(col6, ''),' - - ',' - ')
FROM unify WHERE id=100
March 9, 2016 at 8:41 am
All of the solutions provided leave open the possibility of a trailing delimiter, which may not be desirable. This (UNTESTED) solution will make sure there are no leading or trailing delimiters. I also used the FOR XML PATH method for my concatenation.
SELECT 1264, STUFF( (
SELECT ' - ' + col1,
' - ' + col2,
' - ' + col3,
' - ' + col4,
' - ' + col5,
' - ' + col6
FOR XML PATH(''), TYPE).value('text()', 'NVARCHAR(4000)'), 1, 3, '')
FROM unify
WHERE id=100
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply