Pivot vs Join - 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 vs Join

Author

Topic

WaterWolf
Starting Member

24 Posts

Posted - 2008-03-18 : 13:34:28

Hello,

I have a table which has columns that are something like this:

| Name | Date | NumItems | Cost | Color |

I want to present this data, split into months. Eg. the selected dataset should look something like this:

| Name | NumItems_June | Cost_June | Color_June | NumItems_July | Cost_July | Color_July |

Now I can accomplish this by joining select statements together, eg. something like:

SELECT A.Name as Name, A.NumItems as NumItems_June, A.Cost as Cost_June, A.Color as Color_June, B.NumItems as NumItems_July, B.Cost as Cost_July, B.Color as Color_July
FROM
(
SELECT Name, NumItems, Cost, Color
FROM [table] As A
WHERE Date >= '2007-06-01' AND Date <= '2007-06-30'
) As A
JOIN
(
SELECT Name, NumItems, Cost, Color
FROM [table] As B
WHERE Date >= '2007-07-01' AND Date <= '2007-07-30'
) As B
ON A.Name = B.Name

Now I'm aware that this may also be possible to do using a pivot. Is there any advantage of using a pivot ? I find it a somewhat confusing command as it seems to involve an aggregate function on a column. Can it aggregate on many columns too ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources