SQL create concatenated string

Question

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

just wrap each string to concat in a coalesce(comment,'null') statement. — xQbert
– xQbert, Commented Feb 22, 2013 at 16:20

John Woo · Accepted Answer · 2013-02-22 16:24:20Z

2

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

answered Feb 22, 2013 at 16:24

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xQbert · Accepted Answer · 2013-02-22 16:24:10Z

0

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

answered Feb 22, 2013 at 16:24

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Collectives™ on Stack Overflow

SQL create concatenated string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related