My favorite SQL Server command is: STUFF. What an awesome name for a command. During a recent data conversion I had to combine many duplicate part records in the Part table, but retain the values from all of the duplicate part rows. I used STUFF to collapse the rows:
Select Distinct
[CustomerPartNo]
,[CustomerNo]
,(Stuff((Select ', ' + CustomerDescription From [PARTS] T2
Where T2.[CustomerPartNo] = T1.[CustomerPartNo]
and T2.[CustomerNo] = T2.[CustomerNo] FOR XML PATH('')),1,2,''))
From [PARTS] T1
Technorati Tags:
SQL Server