How to merge the results from two pivot table

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)

How to merge the results from two pivot table

Author

Topic

zhangn
Starting Member

29 Posts

Posted - 2009-12-10 : 07:32:34

I have pivot two separate table with the same rows number.
Is that any way I can put them together.

Query 1 - Pivot by Providers at each region

select * from
(SELECT
ProviderName
,LDZName
,convert(decimal(18,2), CoefficientValue) as Weight
FROM [WeatherDB].[dbo].[DerivedWeatherWeights] as w
inner join dbo.LDZMapping as ldzm
on ldzm.LDZID = w.LDZID
inner join dbo.WeatherProviderId as WPI
on WPI.ProviderId = w.ProviderId
where
ReferenceDateTime = '2009-12-10 08:00'
) as b
pivot
(
avg(Weight)
FOR ProviderName IN ([Met Office],[MeteoGroup UK],[Metra])
) AS pvt
order by LDZName

it output

LDZName Met Office MeteoGroup UK Metra
EA 0.140000 0.420000 0.440000
EM 0.300000 0.290000 0.410000
NE 0.240000 0.320000 0.440000
NO 0.130000 0.540000 0.340000
NT 0.130000 0.420000 0.450000
NW 0.490000 0.350000 0.160000
SC 0.340000 0.210000 0.450000
SE 0.130000 0.410000 0.470000
SO 0.470000 0.100000 0.430000
SW 0.190000 0.260000 0.550000
WM 0.630000 0.230000 0.150000
WN 0.490000 0.350000 0.160000
WS 0.280000 0.720000 0.000000

Query 2 is pivot by the coefficient

select LDZName, Alpha, Beta from
(SELECT LDZName,
CoefficientName, CoefficientValue as Weight
FROM [WeatherDB].[dbo].[DerivedWeatherWeights] as w
inner join dbo.LDZMapping as ldzm
on ldzm.LDZID = w.LDZID
inner join dbo.CoefficientId as CI
on CI.CoefficientId = w.CoefficientId
where
ReferenceDateTime = '2009-12-10 08:00'
and ProviderId = 5
) as b
pivot
(
avg(Weight)
FOR CoefficientName IN ([Alpha],[Beta])
) AS pvt
order by LDZName

the output from the second query is listed below:

LDZName Alpha Beta
EA 0 1
EM 0 1
NE 0 1
NO 0 1
NT 0 1
NW 0 1
SC 0 1
SE 0 1
SO 0 1
SW 0 1
WM 0 1
WN 0 1
WS 0 1

I want to result to be outputed like following:

LDZName Alpha Beta Met Office MeteoGroup UK Metra
EA 0 1 0.14 0.42 0.44
EM 0 1 0.3 0.29 0.41
NE 0 1 0.24 0.32 0.44
NO 0 1 0.13 0.54 0.34
NT 0 1 0.13 0.42 0.45
NW 0 1 0.49 0.35 0.16
SC 0 1 0.34 0.21 0.45
SE 0 1 0.13 0.41 0.47
SO 0 1 0.47 0.1 0.43
SW 0 1 0.19 0.26 0.55
WM 0 1 0.63 0.23 0.15
WN 0 1 0.49 0.35 0.16
WS 0 1 0.28 0.72 0

any help will be more than welcome!

Ning

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-10 : 07:41:56

Something like:


select a.LDZName, b.Alpha, b.Beta, a.[Met Office] a.MeteoGroup a.[UK Metra]
From (select * from
			(SELECT 
					ProviderName
					,LDZName
					,convert(decimal(18,2), CoefficientValue) as Weight
					FROM [WeatherDB].[dbo].[DerivedWeatherWeights] as w
					inner join dbo.LDZMapping as ldzm
					on ldzm.LDZID = w.LDZID 
					inner join dbo.WeatherProviderId as WPI
					on WPI.ProviderId = w.ProviderId
			where ReferenceDateTime = '2009-12-10 08:00'
			) as b
			pivot 
			(
			avg(Weight)
			FOR ProviderName IN ([Met Office],[MeteoGroup UK],[Metra])
			) AS pvt) a
LEFT JOIN (select LDZName, Alpha, Beta from
				(SELECT LDZName,
						CoefficientName, CoefficientValue as Weight
						FROM [WeatherDB].[dbo].[DerivedWeatherWeights] as w
						inner join dbo.LDZMapping as ldzm
						on ldzm.LDZID = w.LDZID 
						inner join dbo.CoefficientId as CI
						on CI.CoefficientId = w.CoefficientId
				where ReferenceDateTime = '2009-12-10 08:00'
				and ProviderId = 5
				) as b
				pivot 
				(
				avg(Weight)
				FOR CoefficientName IN ([Alpha],[Beta])
				) AS pvt) b
ON b.LDZName = a.LDZName
order by a.LDZName

zhangn
Starting Member

29 Posts

Posted - 2009-12-10 : 11:02:19

it works thanks so much RickD!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources