CODEWITHSUNDEEP
sqlsql-server
tam tam
tam tam

Reputation: 1900

SQL create concatenated string

I have a table called service entry and Im trying to create a concatenated string.

ID  ServiceEntryID  PartID  Comment  ServiceTypeIDs  PartDescription
1        2           54      xyz      1               hellothere
2        2           22                               howdy
3        33          54      uhu      1               xyz

Desired String format

PartID~PartDescription~ServiceTypeIDs~Comment

Desired String value

so for service entry ID column with value 2:

54 ~ hellothere ~ 1 ~ xyz | 22 ~ howdy ~ null ~ |

for service entry ID column with value 33:

33 ~ xyz ~ 1 ~ uhu

If the entry does not have a comment then I do not add a null for it whereas if servicetypeids are empty then I will add a null

Upvotes: 0

Views: 1199

Answers (2)

John Woo
John Woo

Reputation: 263883

SELECT
     ServiceEntryID,
     STUFF(
         (SELECT '|' + CAST(PartID AS VARCHAR(5)) + '~' +
                       PartDescription  + '~' +
                       COALESCE(CAST(ServiceTypeIDs AS VARCHAR(5)), 'NULL')  + '~' +
                       COALESCE(Comment, 'NULL')
          FROM TableName
          WHERE ServiceEntryID = a.ServiceEntryID
          FOR XML PATH (''))
          , 1, 1, '')  AS ResultList
FROM TableName AS a
WHERE ServiceEntryID = 2
GROUP BY ServiceEntryID

Upvotes: 2

xQbert
xQbert

Reputation: 35343

Select   coalesce(partID,'NULL') +'~'+ 
coalesce(PartDescription,'NULL') +'~'+ 
 coalesce(ServiceTypeIDs,'NULL') +'~'+
 coalesce(Comment,'NULL') as DesiredStringFormat
FROM yourTableName

The above will substitute the word 'NULL' when any field is NULL.

http://msdn.microsoft.com/en-us/library/ms190349.aspx

Upvotes: 0

Related Questions