asked
More help on Concatenation
Kev was able to give me the following solution to my original question... 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! So I ran the following... select distinct PackageCode + ( select ''+Finish from yourtable order by [Rank] for xml path('')) from yourtable works great... now when i have more data like (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 bs-100-24s-0101.......BoldCC............U21............2 bs-100-24s-0101.......BoldCC............U25............3 bs-100-24s-0101.......BoldCC............LM14..........4 Unfortunately when i run this, it puts all of the finishes under each partnumber... (there are so many more part numbers and package combinations...) What i need to each package code/partnumber combo to have only it's assigned finishes... Thoughts?
if these answers are helpful, please show that by clicking on the thumbs up next to the answer. If either answer solved the problem, indicate that by clicking on the check box next to that answer.
You are real close, you just need to correlate the subquery and join it to the outer. The following will work. SELECT DISTINCT y.PackageCode + ( SELECT '' + t.Finish FROM yourtable t WHERE t.packagecode = y.packagecode ORDER BY [Rank] FOR XML PATH('') ) FROM dbo.yourtable y
@SirSQL is heading in the right direction, but I think (reading between the lines) you also need to join on PartNumber. declare @yourtable table ( Partnumber varchar(20),PackageCode varchar(10),Finish varchar(10),[Rank] int) insert into @yourtable select 'bs-100-24s-0101','FormCC','U20',2 insert into @yourtable select 'bs-100-24s-0101','FormCC','U22',3 insert into @yourtable select 'bs-100-24s-0101','FormCC','LM17',4 insert into @yourtable select 'bs-100-24s-0101','BoldCC','U21',2 insert into @yourtable select 'bs-100-24s-0101','BoldCC','U25',3 insert into @yourtable select 'bs-100-24s-0101','BoldCC','LM14',4 insert into @yourtable select 'cs-100-24s-0101','FormCC','U20',2 insert into @yourtable select 'cs-100-24s-0101','FormCC','U22',3 insert into @yourtable select 'cs-100-24s-0101','FormCC','LM17',4 insert into @yourtable select 'cs-100-24s-0101','BoldCC','U21',2 insert into @yourtable select 'cs-100-24s-0101','BoldCC','U25',3 insert into @yourtable select 'cs-100-24s-0101','BoldCC','LM14',4 select distinct Partnumber, Packagecode, PackageCode + (select ''+Finish from @yourtable yt1 where yt1.Partnumber = yt2.Partnumber and yt1.Packagecode = yt2.Packagecode order by [Rank] for xml path('')) as Finishes from @yourtable yt2 gives 4 rows, which is the number of partnumber/packagecode combinations : Partnumber Packagecode Finishes -------------------- ----------- ------------------ bs-100-24s-0101 BoldCC BoldCCU21U25LM14 bs-100-24s-0101 FormCC FormCCU20U22LM17 cs-100-24s-0101 BoldCC BoldCCU21U25LM14 cs-100-24s-0101 FormCC FormCCU20U22LM17
14 People are following this question.
