Column values as Column Header

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

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.

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.

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources