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!
Use the XML PATH trick.... select distinct PackageCode + ( select ''+Finish from yourtable order by [Rank] for xml path('')) from yourtable
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...
Yes you need to add the PackageCode 'outside' of the XML path query
declare @AllText as varchar(max) set @AllText = '' select @AllText = @AllText + Finish from yourtable order by Rank select 'FormCC' + @AllText
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
13 People are following this question.
