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, 2012 at 06:54 PM in Default

avatar image

sqlLearner 1
972 39 51 57

(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, 2012 at 06:56 PM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

(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, 2012 at 09:46 PM

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

  • ahh, now I get the PIVOT reference......That'll teach me to speed read questions

Feb 21, 2012 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.

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:

x2091
x1069
x104
x9

asked: Feb 21, 2012 at 06:54 PM

Seen: 4249 times

Last Updated: Feb 21, 2012 at 10:17 PM

Copyright 2016 Redgate Software. Privacy Policy