Pivot table ? - 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 2005 Forums

Transact-SQL (2005)

Pivot table ?

Author

Topic

kho
Starting Member

2 Posts

Posted - 2007-11-28 : 10:53:45

Hi!

I`ve been struggling to find a solution to make a report from MS SQL, and I think pivot table is the key but I`m not sure on how to do this. Hope someone can help, here is the case :

I have one table "Products" and one table "Packages". Every product in the Products table has 1 - * Packages (unit which the product is sold by)

The Products table looks something like this :

Id | productText | productGroup | etc

And the Packages table like this :

Id | productId | packageType | weight | etc

What I want to do is to flat this out so I can get the Product and all it`s packages in one record. Something like this :

productText | productGroup | packageType | weight | packageType | weight | etc.

I would really appriciate if someone could help me out with this.

Regards,
Knut

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 11:13:53

if there know real limit to the number of packages, there is not a good tsql solution for you. are you using a reporting tool? is there a realistic limit to the number of packages? if not do you really want a report that might grow to 100+ columns in width?

kho
Starting Member

2 Posts

Posted - 2007-11-28 : 11:26:17

It would be OK to set the limit of number of packages to four...

Thanks.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 12:01:00

you can try something like the following...

declare @sample table (id int identity(1,1), productText varchar(20), productGroup varchar(20))

insert @sample (productText, productGroup)
select 'product1', 'groupA'
union all
select 'product2', 'groupB'
union all
select 'product3', 'groupA'
union all
select 'product4', 'groupB'

declare @sample2 table (id int identity(1,1), productId int, packageType varchar(20), weight float)
insert @sample2 (productId, packageType, weight)
select id, 'bagged', 49.99
from @sample
where productText = 'product1'
union all
select id, 'boxed', 59.99
from @sample
where productText = 'product1'
union all
select id, 'crated', 99.99
from @sample
where productText = 'product1'
union all
select id, 'crated', 199.99
from @sample
where productText = 'product4'
union all
select id, 'bagged', 9.99
from @sample
where productText = 'product3'
union all
select id, 'boxed', 19.99
from @sample
where productText = 'product3'

select product.*, package1.*, package2.*, package3.*, package4.*
from @sample as product
left join
(SELECT ProductID, packageType, weight, RANK() OVER (PARTITION BY ProductID ORDER BY weight) AS RANK FROM @sample2)
as package1 on package1.productid = product.id and package1.rank = 1
left join
(SELECT ProductID, packageType, weight, RANK() OVER (PARTITION BY ProductID ORDER BY weight) AS RANK FROM @sample2)
as package2 on package2.productid = product.id and package2.rank = 2
left join
(SELECT ProductID, packageType, weight, RANK() OVER (PARTITION BY ProductID ORDER BY weight) AS RANK FROM @sample2)
as package3 on package3.productid = product.id and package3.rank = 3
left join
(SELECT ProductID, packageType, weight, RANK() OVER (PARTITION BY ProductID ORDER BY weight) AS RANK FROM @sample2)
as package4 on package4.productid = product.id and package4.rank = 4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources