SQL SERVER Query

[Saikumar Adep]

ProductID | id | 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
2009003 | 1 | Excise
2009003 | 2 | Edu Cess on Exc
2009003 | 3 | S.HS.EduCess-Ex
2009003 | 4 | VAT

I want Output Should
-----------------------
2009001 |1,2,3| Excise,Edu Cess on Exc,S.HS.EduCess-Ex,
2009002 |1,2| Excise,Edu Cess on Exc,S.HS.EduCess-Ex,VAT,
2009003 |1,2,3,4| Excise,Edu Cess on Exc,S.HS.EduCess-Ex,VAT,Octori

what will be the sql query to get above output in sql server 2005
please Solve my thread I am in very much need
The above output is possible or not

[jbisono]

Refer to this thread http://www.daniweb.com/web-development/databases/ms-sql/threads/190891

there are two solutions in there.

[Saikumar Adep]

Output for the Example which i have given was not there

[Saikumar Adep]

I also want to know can we concatenate the auto generated incremented Id

[debasisdas]

Lets see what is the code that you are working on to get the desired output.

[Knvn]

Create the functions:

create function idToRow(@ProductID as int)
	returns Varchar(500)
as begin
	
	declare @temp varchar(100)
	set @temp=''
	
	select @temp+=CAST(id as varchar)+',' from yourTableName where ProductID=@ProductID
	
	return @temp
	
end

and TaxDescriptionToRow(same like idToRow), then call it like:

select ProductID,idToRow(ProductID),TaxDescriptionToRow(ProductID) from @table group by ProductID

[crishjeny]

SELECT DISTINCT STUFF( (SELECT ', ' + [category] from [tblCategory] FOR XML PATH('')),1,1,'') as [category] FROM [tblCategory]
MSSQL Merge multiple rows in single row with string
store the row values into a variable using COALESCE in SQL.

DECLARE @str VARCHAR(100)
SELECT @str = COALESCE(@str + ', ', '') + [category]
FROM [tblCategory]
SELECT category= @str