question

ryanpair avatar image
ryanpair asked

SQL Concatenation Help

I have data that needs to be concatenated... I have a table like this... (this is the table header) Partnumber.........PackageCode..........Finish.........Rank (this is the data) bs-100-24s-0101.......FormCC............U20............2 bs-100-24s-0101.......FormCC............U22............3 bs-100-24s-0101.......FormCC............LM17..........4 What I need the output to say is simply this.... FormCCU20U22LM17 (which is the package code, with the finishes smashed together by rank...) Please help!
sql-server-2008tsqlconcatenation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Use the XML PATH trick.... select distinct PackageCode + ( select ''+Finish from yourtable order by [Rank] for xml path('')) from yourtable
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ryanpair avatar image ryanpair commented ·
I was able to do this.... select skutemp.FinishName + '' as 'text()' from skutemp for xml path('') this works to show me the finishes concatenated... But if i try to add the packagecode to it them i get packagecode + finish + package code + finish and so on and so forth...
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Yes you need to add the PackageCode 'outside' of the XML path query
0 Likes 0 ·
ryanpair avatar image ryanpair commented ·
Kev, you saved my life. THANK YOU SO MUCH!
0 Likes 0 ·
tanglesoft avatar image
tanglesoft answered
declare @AllText as varchar(max) set @AllText = '' select @AllText = @AllText + Finish from yourtable order by Rank select 'FormCC' + @AllText
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
That won't necessarily build the string in the order expected - see this answer here ( http://ask.sqlservercentral.com/questions/98043/sql-query-help-4.html) and the discussion in the comment
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.