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

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.