Prev: auto - indenting in code editor
Next: Table variable populated from stored procedure [SS2000]
From: Hugo Kornelis on 25 Jan 2010 07:45
On Mon, 25 Jan 2010 02:56:46 -0800 (PST), chintu4uin(a)gmail.com wrote:

>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:-
(snip)

Hi chintu4uin,

I the future, please post as CREATE TABLE and INSERT statements. That
would have saved me a lot of time!

>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.

The alphabetical order you are getting is a coincidence. Since there is
no ORDER BY, it will return results in any order the optimizer sees fit.

Here is a query that uses an ORDER BY to force the correct order. I have
also taken the liberty to fix the error that caused the trailing comma.

SELECT
SOAItemTaxSOANo,
STUFF(CAST((SELECT ',' + TaxDescription FROM vwSOAPreview t2
WHERE t2.SOAItemTaxSOANo = vwSOAPreview.SOAItemTaxSOANo
ORDER BY t2.TaxOrderDisplay
FOR XML PATH(''), TYPE) AS VARCHAR(8000)), 1, 1, '')
AS 'TaxDescriptions'
FROM
vwSOAPreview
GROUP BY
SOAItemTaxSOANo;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Hugo Kornelis on 25 Jan 2010 18:37
On Mon, 25 Jan 2010 10:27:01 -0800 (PST), chintu4uin(a)gmail.com wrote:

>Hi Hugo Kornelis & Paul Shapiro thanx for u r reply..
>Hi Hugo Kornelis the query u posted is not working it is giving error
>"Invalid column name 'TaxOrderDisplay'."

Hi chintu4uin,

The table data you posted used three headers:
>> > SOANo �| TaxOrderDisplay | TaxDescription|

The query uses one different column name: SOAItemTaxSOANo instead of
SOANo. I was able to figure this out, so I kept SOAItemTaxSOANo in the
corrected version of the query.

The query you posted does not reference TaxOrderDisplay anywhere. I only
could assume that this column name, like TaxDescription, was correct in
your post. It obviously isn't. As Michael Coles said, you have to
identify the actual name of this column and than substitute it.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 | 
Pages: 1
Prev: auto - indenting in code editor
Next: Table variable populated from stored procedure [SS2000]