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
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 ahh, now I get the PIVOT reference......That'll teach me to speed read questions
4 People are following this question.
