Need help in 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 2000 Forums

SQL Server Development (2000)

Need help in Query

Author

Topic

oceanboy
Starting Member

44 Posts

Posted - 2009-04-02 : 04:07:52

Hi Guys,

I have 3 tables as the following:
People
------
User_ID
User_Name

Product
-------
Product_ID
Product_Name

Product_Owned
--------------
User_Id
Product_ID
Amount_Owned

With this structure, I would like to list out

User_ID, User_Name, Product X, Product Y

Where X and Y are specific product_id. So that I see among all the users who own (or not own) X and Y

Thanks!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 06:54:36

try this by dynamic cross tab
see this link
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

dsindo
Starting Member

45 Posts

Posted - 2009-04-02 : 16:41:12

/* create the function */
CREATE FUNCTION get_products(
@user_id varchar(1000)
)
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @Ret varchar(8000)
SELECT @Ret=COALESCE(@Ret+',','') + product_id FROM product_owned
WHERE user_id=@user_id
RETURN @Ret
END

/* then use the function */

SELECT DISTINCT a.user_id,
b.user_name
dbo.get_products(a.user_id)
FROM product_owned a
LEFT JOIN people b on a.user_id=b.user_id

dsindo
Starting Member

45 Posts

Posted - 2009-04-02 : 16:47:00

/* use this select instead */

SELECT DISTINCT a.user_id,
b.user_name,
dbo.get_products(a.user_id)
FROM product_owned a
LEFT JOIN people b on a.user_id=b.user_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources