need help for a Query - 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)

need help for a Query

Author

Topic

massih
Starting Member

9 Posts

Posted - 2011-10-02 : 06:24:51

hi everyone, i was hoping u can help me with my problem

i have 3 tables
1. list of foods :: tblFood
2. list of buyer :: tblBuyer
3. list of foods that buyers have bought :: tblSoldFood

what i want to do is to get a query that give me the following result:

buyername, food name, bought_food_1,bought_food_2,....

in other word i want all the foods each buyer bought to be shown in the same record

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 07:42:43

you want the food details to be coming in same column or in different columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

massih
Starting Member

9 Posts

Posted - 2011-10-02 : 08:02:47

Same columns

quote:
Originally posted by visakh16

you want the food details to be coming in same column or in different columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 14:04:55

it will be something like below


SELECT b.buyername,
STUFF((SELECT ',' + f.foodname FROM tblSoldFood sf JOIN tblFood f ON f.FoodID= sf.foodID WHERE sf.BuyerID = b.BuyerID FOR XML PATH('')),1,1,'')
FROM tblBuyer b

I dont know your column names so column names used above are indicative ones. Make sure you replace them with actual names in your query
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

massih
Starting Member

9 Posts

Posted - 2011-10-02 : 22:38:13

thanks it works perfectly
just the problem is: i get the food list in an xml format but i just want them to be in a plain format
either like this:
column: [buyer][food list separated by comma]
or
column: [buyer][food1][food2][food3],...
thx again

quote:
Originally posted by visakh16

it will be something like below

SELECT b.buyername,
STUFF((SELECT ',' + f.foodname FROM tblSoldFood sf JOIN tblFood f ON f.FoodID= sf.foodID WHERE sf.BuyerID = b.BuyerID FOR XML PATH('')),1,1,'')
FROM tblBuyer b

I dont know your column names so column names used above are indicative ones. Make sure you replace them with actual names in your query
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 01:06:52

quote:
Originally posted by massih

thanks it works perfectly
just the problem is: i get the food list in an xml format but i just want them to be in a plain format
either like this:
column: [buyer][food list separated by comma]
or
column: [buyer][food1][food2][food3],...
thx again

quote:
Originally posted by visakh16

it will be something like below

SELECT b.buyername,
STUFF((SELECT ',' + f.foodname FROM tblSoldFood sf JOIN tblFood f ON f.FoodID= sf.foodID WHERE sf.BuyerID = b.BuyerID FOR XML PATH('')),1,1,'')
FROM tblBuyer b

I dont know your column names so column names used above are indicative ones. Make sure you replace them with actual names in your query
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Nope you should get it as comma seperated form itself. Post your used query if its different from what i posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

massih
Starting Member

9 Posts

Posted - 2011-10-03 : 01:16:06

here is my query from what u said
SELECT pe_lname, STUFF
((SELECT ',' + sf.fo_name AS MX1
FROM dbo.food AS sf INNER JOIN
dbo.foodpeople AS f ON f.fo_id = sf.fo_id
WHERE (f.pe_id = '1') FOR XML PATH('')), 1, 1, '') AS MX2
FROM dbo.people AS b

here is the result
pa_lname | MX1
terry | MX1>,food1</MX1><MX1>,food3</MX1>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 01:21:18

remove the alias inside. it should be


SELECT pe_lname, STUFF
((SELECT ',' + sf.fo_name FROM dbo.food AS sf INNER JOIN
dbo.foodpeople AS f ON f.fo_id = sf.fo_id
WHERE (f.pe_id = '1')
AND f.buy_id=b.buy_id
 FOR XML PATH('')), 1, 1, '') AS MX1
FROM dbo.people AS b

make sure you add the condition in blue replacing columnnames with your actual buyer id column from dbo.people and dbo.foodpeople

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

massih
Starting Member

9 Posts

Posted - 2011-10-03 : 01:26:51

1. it doesnt let me delete MX1, after executing the query it will be back again(AS Expr1 instead of AS MX1)

2. i got the same result, just the xml tag has changed
Expr1>,food1</Expr1><Expr1>,food3</Expr1>

massih
Starting Member

9 Posts

Posted - 2011-10-03 : 01:28:32

i am not sure exactly but i guess the FOR XML PATH('')
make the result in tags

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 01:30:55

quote:
Originally posted by massih

1. it doesnt let me delete MX1, after executing the query it will be back again(AS Expr1 instead of AS MX1)

2. i got the same result, just the xml tag has changed
Expr1>,food1</Expr1><Expr1>,food3</Expr1>

which editor are you using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

massih
Starting Member

9 Posts

Posted - 2011-10-03 : 01:33:38

I am using vs.net 2010

it worked this way
SELECT pe_lname, STUFF
((SELECT ',' + sf.fo_name AS [text()]
FROM dbo.food AS sf INNER JOIN
dbo.foodpeople AS f ON f.fo_id = sf.fo_id
WHERE (f.pe_id = '1') FOR XML PATH('')), 1, 1, '') AS MX2
FROM dbo.people AS b

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 01:39:38

use SQL Server management studio. its working for me.


SELECT pe_lname, STUFF
((SELECT ',' + sf.fo_nameFROM dbo.food AS sf INNER JOIN
dbo.foodpeople AS f ON f.fo_id = sf.fo_id
WHERE (f.pe_id = '1')
AND f.buy_id=b.buy_id
 FOR XML PATH('')), 1, 1, '') AS MX2
FROM dbo.people AS b

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

massih
Starting Member

9 Posts

Posted - 2011-10-03 : 01:43:15

thanks alot buddy, it is working now
just a question is there anyway to get the list of foods in a seperate cells i mean
[person name][food1][food3][food8]
instead of
[person name][food1,food3,food8]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 01:55:47

yep you can get it. but question is whether you'll be certain on maximum number of food a buyer can buy? if not, you have to use dynamic sql.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

massih
Starting Member

9 Posts

Posted - 2011-10-03 : 01:59:24

yes maximum is 10, how to get it?

quote:
Originally posted by visakh16

yep you can get it. but question is whether you'll be certain on maximum number of food a buyer can buy? if not, you have to use dynamic sql.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 02:11:37

[code]
SELECT pe_lname,[1],[2],[3],...[9],[10]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY pe_lname ORDER BY sf.fo_name) AS rn,
pe_lname,sf.fo_name
FROM dbo.food AS sf
INNER JOIN dbo.foodpeople AS f
ON f.fo_id = sf.fo_id
INNER JOIN dbo.people AS b
ON f.buy_id=b.buy_id
WHERE (f.pe_id = '1')
)t
PIVOT (MAX(fo_name) FOR Rn IN ([1],[2],[3],...[10]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources