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

Recommended Answers

Refer to this thread

there are two solutions in there.

Jump to Post

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

Jump to Post

All 6 Replies

Output for the Example which i have given was not there

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

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

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

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

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

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.