Arsee
Arsee

Reputation: 671

How to convert Row to Column

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.

enter image description here

enter image description here The expected result I need for a pie chart:

Upvotes: 1

Views: 46

Answers (1)

John Cappelletti
John Cappelletti

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

enter image description here

Upvotes: 1

Related Questions