May 10, 2011 at 7:27 am
Hi All,
i have 9 columns say qso,cp,cd,qsoYESpercentage,cpYESpercentage,cdYESpercentage,qsoNOpercentage,qsoNOpercentage,qsoNOpercentage. I get one row as i execute the query with above columns. the values are
qso, -- 130
cp, -- 120
cd, -- 125
qsoYESpercentage, -- 99.19%
cpYESpercentage, -- 100.00%
cdYESpercentage, -- 100.00%
qsoNOpercentage, -- 0.81%
qsoNOpercentage, -- 0.00%
qsoNOpercentage -- 0.00%
now i want to unpivot these values. it shoudl appear like :
YES NO
qso 99.19% 0.81%
cp 100.00% 0.00%
cd 100.00% 0.00%
when i do unpivoting, the values appear as
Stage Yes No
cd 99.19 0.00
cd 99.19 0.81
cd 100.00 0.00
cd 100.00 0.81
cp 99.19 0.00
cp 99.19 0.81
cp 100.00 0.00
cp 100.00 0.81
qso 99.19 0.00
qso 99.19 0.81
qso 100.00 0.00
qso 100.00 0.81
it is something like doing cross join and not getting the correct values..someone please help me on this?? thanks a lot for your help..
May 10, 2011 at 7:32 am
You can look into unpivot in books online.
1 solution I recently tried is to insert that row into a table variable. Then do 1 union all per column. I use a table variable in this particular case because I KNOW there's only 1 row.
Easy to code, understand, maintain and performance is pretty much optimal.
May 10, 2011 at 10:25 am
I'm pretty new, but I had a similiar problem with using unpivot. This might not be the best way to do it, but here is how solved it.
DECLARE @TempTable TABLE
(
qso varchar(3)
,cp varchar(3)
,cd varchar(3)
,qsoYesPercentage varchar(7)
,cpYesPercentage varchar(7)
,cdYesPercentage varchar(7)
,qsoNoPercentage varchar(7)
,cpNoPercentage varchar(7)
,cdNoPercentage varchar(7)
)
INSERT INTO @TempTable
SELECT 'qso','cp','cd','99.19%','100.00%','100.00%','.81%','0.00%','0.00%'
SELECT st.ID, yp.ColumnValue as YesPercent, np.ColumnValue as NoPercent
FROM (
Select ColumnName as ID, ColumnName, ColumnValue
FROM (Select qso
,cp
,cd
FROM @TempTable) as tmp
UNPIVOT
(ColumnValue For ColumnName IN (qso,cp,cd))
as Stage
) as st
JOIN (
Select SUBString(ColumnName,1,CHARINDEX('Y',ColumnName) - 1) as ID, ColumnName, ColumnValue
FROM (Select qsoYesPercentage
,cpYesPercentage
,cdYesPercentage
FROM @TempTable) as tmp
UNPIVOT
(ColumnValue For ColumnName IN (qsoYesPercentage,cpYesPercentage,cdYesPercentage))
as YesPercent
) as yp on yp.ID = st.ID
JOIN (
Select SUBString(ColumnName,1,CHARINDEX('N',ColumnName) - 1) as ID, ColumnName, ColumnValue
FROM (Select qsoNoPercentage
,cpNoPercentage
,cdNoPercentage
FROM @TempTable) as tmp
UNPIVOT
(ColumnValue For ColumnName IN (qsoNoPercentage,cpNoPercentage,cdNoPercentage))
as NOpercent
) as np on np.ID = st.ID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply