Concatenate Mutiple Rows Data to Single RowColumn comma separated Data
in [Using MS SQL Server]
|
Prev: Interpolation of position waypoints in SQL Server query
Next: Concatenate Mutiple Rows Data to Single RowColumn comma separated Data
From: chintu4uin on 25 Jan 2010 06:01 Hi friends I m using SQL 2005 I want to concatenate multiple rows into single row with comma separated. I tried using for XML and all but what I m looking for I m not getting that My Table structure:- Table A SOANo | TaxOrderDisplay | TaxDescription| 2009001 | 1 | Excise 2009001 | 2 | Edu Cess on Exc 2009001 | 3 | S.HS.EduCess-Ex 2009002 | 1 | Excise 2009002 | 2 | Edu Cess on Exc 2009002 | 3 | S.HS.EduCess-Ex 2009002 | 4 | VAT 2009003 | 1 | Excise 2009003 | 2 | Edu Cess on Exc 2009003 | 3 | S.HS.EduCess-Ex 2009003 | 4 | VAT 2009003 | 5 | Octori Output Should ----------------------- 2009001 | Excise,Edu Cess on Exc,S.HS.EduCess-Ex, 2009002 | Excise,Edu Cess on Exc,S.HS.EduCess-Ex,VAT, 2009003 | Excise,Edu Cess on Exc,S.HS.EduCess-Ex,VAT,Octori but I m getting output like this ------------------------------------------- 2009001 | Edu Cess on Exc,Excise,S.HS.EduCess-Ex, 2009002 | Edu Cess on Exc,Excise,S.HS.EduCess-Ex,VAT, 2009003 | Edu Cess on Exc,Excise,Octroi,S.HS.EduCess-Ex,VAT, I m using the below query -------------------------------------------------------------------------------------------------- SELECT SOAItemTaxSOANo, STUFF(CAST((SELECT distinct TaxDescription + ',' FROM vwSOAPreview t2 WHERE t2.SOAItemTaxSOANo = vwSOAPreview.SOAItemTaxSOANo FOR XML PATH(''), TYPE) AS VARCHAR(8000)), 1, 0, '') AS 'TaxDescriptions' FROM vwSOAPreview GROUP BY SOAItemTaxSOANo -------------------------------------------------------------------------------------------------- My problem is that Tax description should come in order of their TaxOrderDisplay ID but I m getting it in alphabetical order which I dont want. Please frnd reply u r suggestion for the same where I m going wrong... |