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)
 Column values as Column Header

Author  Topic 

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-10-21 : 08:08:51
I have a shippings table, with some general information about a shipment.
I also got a components table with some general information about components.
And I got a shippingscomposition table where it is defined what components and how much of them is used in which shipment.
I would like a query where I get a column for each component.
I assume I need a query with the PIVOT keyword, but I can't get it right. Any help appreciated! Thanks.
declare @Shippings Table (ShippingID int IDENTITY(1,1), ShippingName varchar(50));
declare @ShippingComposition Table (ShippingCompositionID int IDENTITY(1,1), ShippingID int, ComponentID int, LoadingVolume int, DischargeVolume int);
declare @Components Table(ComponentID int IDENTITY(1,1), ComponentName varchar(100));

insert into @Components
values
('Component 1'),
('Component 2'),
('Component 3'),
('Component 4'),
('Component 5');

insert into @Shippings
values
('Shipping 1'),
('Shipping 2'),
('Shipping 3');


insert into @ShippingComposition
(ShippingID, ComponentID, LoadingVolume, DischargeVolume)
values
(1,1,100,10),
(1,2,200,20),
(1,3,300,30),
(1,4,400,40),
(1,5,500,50),
(2,1,110,11),
(2,3,330,33),
(2,4,440,44),
(2,5,550,55),
(3,2,222,2),
(3,3,333,3),
(3,4,444,4);

select Shippings.ShippingName,
Components.ComponentName,
ShippingComposition.LoadingVolume,
ShippingComposition.DischargeVolume
from @Shippings as Shippings
inner join @ShippingComposition as ShippingComposition
on ShippingComposition.ShippingID = Shippings.ShippingID
inner join @Components as Components
on Components.ComponentID = ShippingComposition.ComponentID
order by Shippings.ShippingID, Components.ComponentID

expected result:

ShippingName    Component 1 Loading    Component 1 Discharge    Component 2 Loading    Component 2 Discharge    Component 3 Loading    Component 3 Discharge    Component 4 Loading    Component 4 Discharge    Component 5 Loading    Component 5 Discharge
------------ ------------------- --------------------- ------------------- --------------------- ------------------- --------------------- ------------------- --------------------- ------------------- ---------------------
Shipping 1 100 10 200 20 300 30 400 40 500 50
Shipping 2 110 11 0 0 330 33 440 44 550 55
Shipping 3 0 0 222 2 333 3 444 4 0 0

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-21 : 08:58:34
This ought to do it:


with cte as (
select Shippings.ShippingName,
Components.ComponentName,
ShippingComposition.LoadingVolume,
ShippingComposition.DischargeVolume
from @Shippings as Shippings
inner join @ShippingComposition as ShippingComposition
on ShippingComposition.ShippingID = Shippings.ShippingID
inner join @Components as Components
on Components.ComponentID = ShippingComposition.ComponentID
)
--order by Shippings.ShippingID, Components.ComponentID

select ShippingName
, max(case when ComponentName = 'Component 1' then LoadingVolume else 0 end) as 'Component 1 Loading'
, max(case when ComponentName = 'Component 1' then DischargeVolume else 0 end) as 'Component 1 Discharge'
, max(case when ComponentName = 'Component 2' then LoadingVolume else 0 end) as 'Component 2 Loading'
, max(case when ComponentName = 'Component 2' then DischargeVolume else 0 end) as 'Component 2 Discharge'
, max(case when ComponentName = 'Component 3' then LoadingVolume else 0 end) as 'Component 3 Loading'
, max(case when ComponentName = 'Component 3' then DischargeVolume else 0 end) as 'Component 3 Discharge'
, max(case when ComponentName = 'Component 4' then LoadingVolume else 0 end) as 'Component 4 Loading'
, max(case when ComponentName = 'Component 4' then DischargeVolume else 0 end) as 'Component 4 Discharge'
, max(case when ComponentName = 'Component 5' then LoadingVolume else 0 end) as 'Component 5 Loading'
, max(case when ComponentName = 'Component 5' then DischargeVolume else 0 end) as 'Component 5 Discharge'

from cte
group by ShippingName
Go to Top of Page

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-10-28 : 09:48:27
Thanks for the response!
Your solution works perfectly with the example I created.

But in my real environment, I need several other columns of the Shippings table and columns of other joined tables.
I did not include them in the example because it would make it to complex, but I was hoping to get a solution generic enough that I could adapt to my real environment.
The reason why I can't use your solution is the "group by ShippingName". I will have more than one record of the Shippings table, so I can not group on any of those columns.

Is there another way to achieve the same result? I was thinking I needed a PIVOT table, but I keep struggling with the syntax.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 10:12:27
There are a few approaches:

1. Using my earlier response, add the additional columns
2. Using PIVOT, you'll need more than one PIVOT clause, since you have more than one PIVOT column (loading and discharge). Note that PIVOT does grouping. It's basically a short-hand way of doing what my examples does.
3. With or without PIVOT, build a dynamic SQL query. This may save some typing if the input table is fixed. If the input columns can vary, this may be the only way.
Go to Top of Page

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-10-28 : 10:34:56
Thanks again for the quick response and suggestions.

I made a dynamic SQL, but ended up with 36 inner (nested) selects.
The performance of that query is terrible.

I ended up with a query base on your example and added all columns to the group by clause.
It looks like it does the job.
Go to Top of Page
   

- Advertisement -