PIVOT Query Question - SQL 2005

I am trying to create a crosstab query using SQL 2005 and the PIVOT
operator using the following data:
Id MetricId Metric BaseVal PeerVal
012141 1 MarketValueChange 0.08 0.46
012141 2 BarraPredictedBeta NULL 1.44
012141 3 Beta 1.261 1.81
012141 4 PERatio 21.80 75.39
012141 5 EquityDRate 0.12 0.14
and I can create a query that contains just the BaseVal data with the
query below. How can add to the query to include the PeerVal data so
the output would look like this:
Id MarketValueChange-Base MarketValueChange-Peer...
012141 0.08 0.46
Also, do I need to "hardcode" the metricnames in the select statement
when I have them in the data or can I dynamically have them set as my
column names?
Select Id
,"1" AS MarketValueChange1Year
,"2" AS BarraPredictedBeta
,"3" AS Beta
,"4" AS PriceToEarningsRatio
,"5" AS EquityDiscountRate
FROM (
SELECT Id, MetricID, BaseVal
FROM OutputAll WHERE Id= '012141')
p PIVOT (
MIN(baseval)
FOR metricid IN ("1","2","3","4","5")
) pvt
Order by Id
GO
Thanks
*** Sent via Developersdex http://www.examnotes.net ***Hello John .,

> Also, do I need to "hardcode" the metricnames in the select statement
> when I have them in the data or can I dynamically have them set as my
> column names?
I'll give part 1 some more thought, but as to this question, yes, you do
have to hardcode it today. If you want a dynamic pivot, you'll need a dynami
c
query.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||PIVOT in 2005 is like taking your wife to McDonald's
on your anniversary:)
It's quite shallow to say the least.
Check out RAC for all dynamic pivoting/xtabs problems.
www.rac4sql.net
"John ." <jrp210@.yahoo.com> wrote in message
news:%230aGTavFGHA.1396@.TK2MSFTNGP11.phx.gbl...
> I am trying to create a crosstab query using SQL 2005 and the PIVOT
> operator using the following data:
> Id MetricId Metric BaseVal PeerVal
> 012141 1 MarketValueChange 0.08 0.46
> 012141 2 BarraPredictedBeta NULL 1.44
> 012141 3 Beta 1.261 1.81
> 012141 4 PERatio 21.80 75.39
> 012141 5 EquityDRate 0.12 0.14
>
> and I can create a query that contains just the BaseVal data with the
> query below. How can add to the query to include the PeerVal data so
> the output would look like this:
> Id MarketValueChange-Base MarketValueChange-Peer...
> 012141 0.08 0.46
> Also, do I need to "hardcode" the metricnames in the select statement
> when I have them in the data or can I dynamically have them set as my
> column names?
> Select Id
> ,"1" AS MarketValueChange1Year
> ,"2" AS BarraPredictedBeta
> ,"3" AS Beta
> ,"4" AS PriceToEarningsRatio
> ,"5" AS EquityDiscountRate
> FROM (
> SELECT Id, MetricID, BaseVal
> FROM OutputAll WHERE Id= '012141')
> p PIVOT (
> MIN(baseval)
> FOR metricid IN ("1","2","3","4","5")
> ) pvt
> Order by Id
> GO
> Thanks
> *** Sent via Developersdex http://www.examnotes.net ***|||> Also, do I need to "hardcode" the metricnames in the select statement
> when I have them in the data or can I dynamically have them set as my
> column names?
>
That would be nice, but not in 2005. PIVOT and UNPIVOT requires hardcoded
values. Use dynamic SQL to get around that if necessary..
Perhaps something like this will work for you. Union the set together
renaming the metric to include the value you want, and then do the pivot on
it:
create table test
(
id char(6),
MetricId tinyint,
Metric varchar(20),
baseval numeric(4,2),
peerval numeric(4,2)
)
go
insert into test
select 012141, 1, 'MarketValueChange', 0.08, 0.46
UNION ALL
select 012141, 2, 'BarraPredictedBeta', NULL, 1.44
UNION ALL
select 012141, 3, 'Beta', 1.261, 1.81
UNION ALL
select 012141, 4, 'PERatio', 21.80, 75.39
UNION ALL
select 012141, 5, 'EquityDRate', 0.12, 0.14
go
--CTE to cover the UNION
With flatter as
(
select id, metric + '-baseVal' as metric, baseVal as value
from test
UNION ALL
select id, metric + '-peerVal', peerVal
from test )
--then hardcoding the names of the columns
SELECT id, [MarketValueChange-baseVal], [MarketValueChange-peerVal]
FROM ( SELECT id, value, metric
FROM flatter) AS expanded
PIVOT ( max(value) FOR metric IN
([MarketValueChange-baseVal], [MarketValueChange-peerVal])
) AS ourPivot
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"John ." <jrp210@.yahoo.com> wrote in message
news:%230aGTavFGHA.1396@.TK2MSFTNGP11.phx.gbl...
>I am trying to create a crosstab query using SQL 2005 and the PIVOT
> operator using the following data:
> Id MetricId Metric BaseVal PeerVal
> 012141 1 MarketValueChange 0.08 0.46
> 012141 2 BarraPredictedBeta NULL 1.44
> 012141 3 Beta 1.261 1.81
> 012141 4 PERatio 21.80 75.39
> 012141 5 EquityDRate 0.12 0.14
>
> and I can create a query that contains just the BaseVal data with the
> query below. How can add to the query to include the PeerVal data so
> the output would look like this:
> Id MarketValueChange-Base MarketValueChange-Peer...
> 012141 0.08 0.46
> Also, do I need to "hardcode" the metricnames in the select statement
> when I have them in the data or can I dynamically have them set as my
> column names?
> Select Id
> ,"1" AS MarketValueChange1Year
> ,"2" AS BarraPredictedBeta
> ,"3" AS Beta
> ,"4" AS PriceToEarningsRatio
> ,"5" AS EquityDiscountRate
> FROM (
> SELECT Id, MetricID, BaseVal
> FROM OutputAll WHERE Id= '012141')
> p PIVOT (
> MIN(baseval)
> FOR metricid IN ("1","2","3","4","5")
> ) pvt
> Order by Id
> GO
> Thanks
> *** Sent via Developersdex http://www.examnotes.net ***