Using DISTINCT to Generate Combined Column List with Different Order by Column


  
 
  

 Create table Prods (prc_id int, prc_NAME varchar(10), prc_idgrp int)
Insert into Prods values(1,'D',25),(2,'A',25),(3,'C',25),(4,'B',25)
 


 declare @xlist varchar(1000)
 

;with mycte as (
Select distinct prc_ID, prc_NAME from PRODS
)

 SELECT @xlist=Stuff(( SELECT ',' + quotename(Cast(prc_ID as varchar(5)),']')
  FROM mycte
 Order by prc_NAME            
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')   
 

 

Print @xlist
 drop table Prods
 

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s