How to get this result using Pivot or Unpivot

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

General SQL Server Forums

New to SQL Server Programming

How to get this result using Pivot or Unpivot

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-08-29 : 08:07:39

i have a query in which i am getting result like this and i am geting the result set in below. But i need to get as shown in last one

DatePeriod	Good	Fair	Poor	NotCategorised	NotTested	GoodPercentage	FairPercentage	PoorPercentage	NotCategorisedPercentage	NotTestedPercentage
Feb 2013	4	0	0	0	60	6.25	0	0	0	93.75

And for this one i have written query using UNPIVOT


  Select DatePeriod,Legend FROM 
(
select DatePeriod,Good,Fair,Poor,NotCategorised,NotTested,GoodPercentage,
FairPercentage,poorPercentage,NotCategorisedPercentage,NotTestedPercentage  from #cte 

)P
UNPIVOT(Legend FOR  #CTE IN (Good,Fair,Poor,NotCategorised,NotTested))AS UNPVT;

and i am not getting required output

my result set need to be like this


year    Legend  percent count
Dec-12  Good    13.89   35
Dec-12  Fair    0   0
Dec-12  Poor    0   0
Dec-12  NC  0   0
Dec-12  NoData  86.11   217

Suggest me

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-29 : 12:05:02

Can you post some sample data in a consumable format along with expected output?

Hhere are some link that can help you prepare that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-30 : 00:25:02

where did you get 13.85 etc values from? i cant see that in data posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources