x

Use a pivot in a UNION

I am trying to combine ALL the records from BOTH OF these tables. Each TABLE has SOME id's that the other does not have so I don't want to lose any in the process. My problem is the TotalUsages and TotalSites data is all falling under the TotalUsages column. I need to pivot the TotalSites column so it becomes it's own column.

SELECT a1.id, a1.city, a1.Year, a1.totalusages
FROM table1 a1
UNION ALL 
SELECT a2.id, a2.city, a2.Year, a2.totalsites
FROM table2 a2
more ▼

asked Feb 21 '12 at 06:54 PM in Default

sqlLearner 1 gravatar image

sqlLearner 1
772 28 37 39

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If you want TotalSites as it's own column

SELECT a1.id, a1.city, a1.Year, a1.totalusages, 0 as TotalSites
FROM table1 a1
UNION ALL 
SELECT a2.id, a2.city, a2.Year, 0, a2.totalsites
FROM table2 a2
more ▼

answered Feb 21 '12 at 06:56 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 44 49 76

(comments are locked)
10|1200 characters needed characters left

I'm interpreting your question differently to the way @Kev Riley has done...

What I suggest is builds on what you've done.

Firstly, some temporary tables and data:

DECLARE @table1 TABLE (ID int, City varchar(20), [Year] int, TotalUsages int)
DECLARE @table2 TABLE (ID int, City varchar(20), [Year] int, TotalSites int)
INSERT INTO @table1 values (1,'London', 2009, 100), (2, 'Birmingham', 2009, 50), (3, 'London', 2008, 75)
INSERT INTO @table2 values (2, 'Birmingham', 2009, 3), (3, 'London', 2008, 2), (4, 'Leeds', 2008, 1)

OK, so what we're doing is adding another column to indicate whether the source row is coming from the Usages or Sites table, and using that to PIVOT around:

SELECT ID, City, Year, [Usages], [Sites]
FROM (SELECT a1.id, a1.City, a1.Year, a1.TotalUsages AS Total, 'Usages' AS UsagesOrSites FROM @table1 a1
UNION ALL SELECT a2.id, a2.City, a2.Year, a2.TotalSites AS Total, 'Sites' AS Usag esOrSites FROM @table2 a2
) AS UnionTable
PIVOT
(SUM (Total) FOR UsagesOrSites IN ([Usages], [Sites])) AS PivotTable

The results here:

ID  City         Year   Usages   Sites
1   London       2009   100      NULL
2   Birmingham   2009   50       3
3   London       2008   75       2
4   Leeds        2008   NULL     1

If you want "0" rather than "NULL", replace the first line of the SELECT with:

SELECT ID, City, Year, IsNull([Usages], 0) AS Usages, IsNull([Sites], 0) AS Sites
more ▼

answered Feb 21 '12 at 09:46 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.4k 14 20 44

+1 ahh, now I get the PIVOT reference......That'll teach me to speed read questions
Feb 21 '12 at 10:17 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x977
x76
x6

asked: Feb 21 '12 at 06:54 PM

Seen: 1855 times

Last Updated: Feb 21 '12 at 10:17 PM