The following columns are available in the table PO: Po#,styel#,color and quantity, where the same po# may have different style#, the same po# and style# may have different color, now the requirement is: po# and style# The color of the same record is calculated with quantity, inserted into a new table, such as:
po# style# Color Quantity
A Red 10
A Blue 20
B Red 10
B Blue 30
To get the table as follows:
po# style# Color Quantity
A Red,blue 30
B Red 10
B Blue 30
You can get all the data you want to insert into the new table as follows:
Select po#,style#, (case COLOR when "then" ELSE Left (Color,len (COLOR)-1) END) color,quantity from (SELECT DISTINCT po#, Style#,colorlist as COLOR, (select SUM (Quantity) from PO WHERE po#=b.po# and style#=b.style#) quantityfrom (select PO #,style#, (SELECT color+ ', ' from PO WHERE po#=a.po# and style#=a.style# for XML PATH (")) as ColorList from P O as A) B) C
Row record merging in SQL Server