Reputation: 671
I have a tablix that Contains Type, Default, Break, CRG, Meeting, Training, Specialist, Personal, SpecialProjects and SystemOutage are arranged horizontally. I need to put it in a Pie Chart and each chart is filtered through dataset by Category. The issue I am having is that Default, Break, CRG, Meeting, Training, Specialist, Personal, SpecialProjects and SystemOutage is in horizontal layout, and I need it to be in Vertical. I tried the Unpivot but I can't make it work with 9 fields. see below.
The expected result I need for a pie chart:
Upvotes: 1
Views: 46
Reputation: 82020
Perhaps this is what you are looking for
Declare @YourTable table (Type varchar(50),[Default] int,[Break] int,CRG int,Meeting int,Training int,Specialist int,Personal int,SpecialProjects int,SystemOutage int)
Insert into @YourTable values
('Category1',32721,40634,0,1348,9162,0,10212,44549,17090),
('Category2',261997,83567,0,0,552312,0,17859,59345,30138)
Select B.*
From (Select Type,XMLData=cast((Select A.* for XML Raw) as xml) From @YourTable A) A
Cross Apply (
Select Type = r.value('@Type','varchar(50)')
,Item = attr.value('local-name(.)','varchar(100)')
,Value = attr.value('.','int')
From XMLData.nodes('/row') as A(r)
Cross Apply A.r.nodes('./@*') AS B(attr)
Where attr.value('local-name(.)','varchar(100)') not in ('Type')
) B
Where A.Type='Category1'
Returns
Upvotes: 1