question

sqlLearner 1 avatar image
sqlLearner 1 asked

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
sql-server-2008t-sqlpivotunion-all
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 ahh, now I get the PIVOT reference......That'll teach me to speed read questions
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.