SQL Query Needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query Needed

Author

Topic

Jamuna
Starting Member

7 Posts

Posted - 2011-06-13 : 02:56:40

Hi ,
I need your help regarding an sql select query,

I have
written a view which combines tables which result in the following output with respect to a particular ID value,
----------------------
ProductLine |ItemName
---------------------
Door | A
BigDoor | B
BigDoor | C
BigDoor | D
-----------------------

I need a query to display the result like the following

----------------------
ProductLine |ItemName
---------------------
Door | A
BigDoor | B,C,D
-----------------------

For same Productline column the itemNames should appear as comma seperated valus,I wrote a query for this but its displaying all ItemNames.

My query is like this:

select ProdLineName,
COALESCE(STUFF((SELECT ', '+ ItemName FROM ViewMR WHERE MRID = 6
and ProdLineName in (select ProdLineName FROM ViewMR group by ProdLineName,ID having COUNT(ProdLineName)>1 )
ORDER BY ProdLineName FOR XML PATH('')),1,2,''),'') as Item
FROM ViewMR where ID=6

Can anybody please help?

jamuna

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-13 : 03:12:05

try


select 	ProdLineName,
	COALESCE(STUFF((SELECT 	', ' + ItemName 
			FROM 	ViewMR x
			WHERE 	x.MRID = mr.ID
			ORDER BY ItemName FOR XML PATH('')),1,2,''),'') as Item
FROM 	ViewMR mr
where 	ID 	= 6 
group by ProdLineName

KH
[spoiler]Time is always against us[/spoiler]

Jamuna
Starting Member

7 Posts

Posted - 2011-06-13 : 03:21:38

The Result for the above query is as follows:

BigDoor | A,B,C,D
Door | A,B,C,D

but I need the like this:

Door | A
BigDoor | B,C,D

Hope you understood,Anyway thanks for the quick reply..

jamuna

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-13 : 03:23:13

try


select 	ProdLineName,
	COALESCE(STUFF((SELECT 	', ' + ItemName 
			FROM 	ViewMR x
			WHERE 	x.ProdLineName = mr.ProdLineName
			ORDER BY ItemName FOR XML PATH('')),1,2,''),'') as Item
FROM 	ViewMR mr
where 	ID 	= 6 
group by ProdLineName

KH
[spoiler]Time is always against us[/spoiler]

Jamuna
Starting Member

7 Posts

Posted - 2011-06-13 : 03:28:19

Thank you so much,i got the result,Thanks for the help

jamuna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources