Join three tables and convert rows to columns

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)

Join three tables and convert rows to columns

Author

Topic

theskywinner
Starting Member

2 Posts

Posted - 2011-07-02 : 06:46:42

Hello Developers,

I have three tables to JOIN

Product Table

ProductID Name
1 Coin
2 Deck
3 Card
4 Balloon

ProductVariant Table
VariantID ProductID Price
1 1 10
2 2 15
3 3 9
4 4 11

ProductCategory Table
ProductID CategoryID
1 1
1 2
1 3
1 4
2 3
2 2
2 1

I want to join all three tables in such a way that the Four categories of the Product comes as columns.

ProductID Name Price Category1 Category2 Category3 Category4
1 Coin 10 1 2 3 4
2 Deck 15 3 2 1 NULL

Please let me know the solution to achieve the above result.

Thanks in advance.

PLEASE FORGIVE ME FOR CRAMMED TEXT IN TABLES. EACH FIELDS ARE SEPARATED BY A SPACE. I COULDN'T ENABLE HTML MODE AND COULDN'T FIND OPTION FOR THE SAME.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-02 : 07:21:12

If you have only four categories (or if they are known in advance), you can use the PIVOT operator. See http://msdn.microsoft.com/en-us/library/ms177410.aspx , they have the description and an example.

If you have an unknown number of categories, then you will need to use dynamic pivoting. Madhivanan's blog has the code and examples: http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

theskywinner
Starting Member

2 Posts

Posted - 2011-07-02 : 08:05:14

Hello Sunita,

The number of categories will be four. But it can have any values. The values are not known in advance. I have looked at the Pivot article, but I am not able to grasp the concept being a beginner in SQL.

I would be really grateful if you can try to create query using Pivot article if you have some time.

Thanks & Regards,
Snehal

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-02 : 09:04:02

I am going to do this in 3 steps. I will show how to use the PIVOT operator or dynamic pivot. The first two steps are required for either approach.

First, create the sample data that you indicated in your original posting:


create table #tmpProducts
(product_id int, product_name varchar(255));
insert into #tmpProducts values (1,'Coin');
insert into #tmpProducts values (2,'Deck');
insert into #tmpProducts values (3,'Card');
insert into #tmpProducts values (4,'Balioon');

create table #tmpProductVariant
(variant_id int, product_id int, product_price int);
insert into #tmpProductVariant values(1,1,10);
insert into #tmpProductVariant values(2,2,15);
insert into #tmpProductVariant values(3,3,9);
insert into #tmpProductVariant values(4,4,11);

create table #tmpProductCategory
(product_id int, category_id int);
insert into #tmpProductCategory values(1,1);
insert into #tmpProductCategory values(1,2);
insert into #tmpProductCategory values(1,3);
insert into #tmpProductCategory values(1,4);
insert into #tmpProductCategory values(2,3);
insert into #tmpProductCategory values(2,2);
insert into #tmpProductCategory values(2,1);

Next, write a query against this data, joining the tables as required. I am not sure if this is exactly correct, because it is not clear to me what the VariantID does - whether a given product can have multiple variants. If so, you may need to modify this query to take that into account. The purpose here is just to look at the data to see if it is correct.


select 
   p.product_id,
   p.product_name,
   pv.product_price,
   pc.category_id
from
   #tmpProducts p
   inner join #tmpProductVariant pv
      on pv.product_id = p.product_id
   inner join #tmpProductCategory pc
      on pc.product_id = p.product_id

If you need to modify thsi query, modify it as required and then check the results to make sure that it is giving you the correct data here.

Once you have the correct data, you can use dynamic pivot or the PIVOT operator. The code below shows how to use the PIVOT operator


with A as
(
select 
   p.product_id,
   p.product_name,
   pv.product_price,
   pc.category_id
from
   #tmpProducts p
   inner join #tmpProductVariant pv
      on pv.product_id = p.product_id
   inner join #tmpProductCategory pc
      on pc.product_id = p.product_id
)
select 
   product_id,
   product_name,
   product_price,
   [1] as [Category1],
   [2] as [Category2],
   [3] as [Category3],
   [4] as [Category4]
from
   A 
PIVOT
( max(category_id) for category_id in ([1],[2],[3],[4])) V

If your categories are not known in advance, you cannot do this. So you will need to use the dynamic pivot. And, thanks to Madhivanan, it is even simpler. Before you run this query, go to Madhivanan's blog and copy the dynamic_pivot function (using the copy to clip board button at the right top of the code window), paste it in an SSMS window and run it. That will install the function. Then, run this query to use the function.

EXEC dynamic_pivot
'select 
   p.product_id,
   p.product_name,
   pv.product_price,
   pc.category_id
from
   #tmpProducts p
   inner join #tmpProductVariant pv
      on pv.product_id = p.product_id
   inner join #tmpProductCategory pc
      on pc.product_id = p.product_id
',
'category_id',
'max(category_id)'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources