Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

NEED HELP IN PIVOT Expand / Collapse
Author
Message
Posted Saturday, September 22, 2012 3:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 16, 2012 9:28 PM
Points: 15, Visits: 167
I HAVE below TABLE with colum names proposer,approver,designation


PROPOSER APPROVER DESIGNATION
----------------------------------
kiran ARUN CFO
kiran kranthi HOD
kiran PHANI CEO
kiran SANGEET ACCOUNT
SURYA MALATHI ACCOUNT
SURYA SRIKANTH ACCOUNT
SWATHI MALATHI HOD
SWATHI PANKAJ CEO
SWATHI VARSHA CEO


and i need O/P like

proposer HOD CEO CFO ACCOUNT
-----------------------------------------------------------------------------------
kiran KRANTHI PHANI ARUN SANGEETH
SURYA -------- ----- ----- MALATHI,SRIKANTH
SWATHI MALATHI PANKAJ,VARSHA ----- -----------


BY USING PIVOT I CAN GET ONLY ONE VALUE



SELECT PROPOSER,[HOD],[CEO],[CFO],[ACCOUNT]
FROM
(SELECT PROPOSER,DESIGNATION,APPROVER FROM KIRAN)A
PIVOT
(MAX(APPROVER) FOR DESIGNATION IN([HOD],[CEO],[CFO],[ACCOUNT]))PVT


HOW CAN I GET 2 VALUES ??????????


THANKS IN ADVNACE
Post #1363086
Posted Saturday, September 22, 2012 3:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 16, 2012 9:28 PM
Points: 15, Visits: 167
SWATH (9/22/2012)
I HAVE below TABLE with colum names proposer,approver,designation


PROPOSER APPROVER DESIGNATION
----------------------------------
kiran ARUN CFO
kiran kranthi HOD
kiran PHANI CEO
kiran SANGEET ACCOUNT
SURYA MALATHI ACCOUNT
SURYA SRIKANTH ACCOUNT
SWATHI MALATHI HOD
SWATHI PANKAJ CEO
SWATHI VARSHA CEO


and i need O/P like

proposer HOD CEO CFO ACCOUNT
-----------------------------------------------------------------------------------
kiran KRANTHI PHANI ARUN SANGEETH
SURYA -------- ----- ----- MALATHI,SRIKANTH
SWATHI MALATHI PANKAJ,VARSHA ----- -----------


BY USING PIVOT I CAN GET ONLY ONE VALUE



SELECT PROPOSER,[HOD],[CEO],[CFO],[ACCOUNT]
FROM
(SELECT PROPOSER,DESIGNATION,APPROVER FROM KIRAN)A
PIVOT
(MAX(APPROVER) FOR DESIGNATION IN([HOD],[CEO],[CFO],[ACCOUNT]))PVT


HOW CAN I GET 2 VALUES ??????????


THANKS IN ADVNACE
Post #1363087
Posted Saturday, September 22, 2012 5:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:36 AM
Points: 1,565, Visits: 2,378
Swath, I don't have access to a server right now, but if you search for PIVOT there are countless examples here, and also on google.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1363098
Posted Saturday, September 22, 2012 10:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 5:22 AM
Points: 71, Visits: 13
Try this


declare @Tmp table(PROPOSER varchar(100),APPROVER varchar(100),DESIGNATION varchar(100))

insert into @Tmp
select 'kiran','ARUN','CFO' union all
select 'kiran','kranthi','HOD' union all
select 'kiran','PHANI','CEO' union all
select 'kiran','SANGEET','ACCOUNT' union all
select 'SURYA','MALATHI','ACCOUNT' union all
select 'SURYA','SRIKANTH','ACCOUNT' union all
select 'SWATHI','MALATHI','HOD' union all
select 'SWATHI','PANKAJ','CEO' union all
select 'SWATHI','VARSHA','CEO'

SELECT PROPOSER,[HOD],[CEO],[CFO],[ACCOUNT]
FROM
(select PROPOSER,DESIGNATION,(select APPROVER+',' as[text()] from @Tmp [1] where [1].PROPOSER=[2].PROPOSER and [1].DESIGNATION=[2].DESIGNATION for XML path('')) as APPROVER
from @Tmp [2])A
PIVOT
(MAX(APPROVER) FOR DESIGNATION IN([HOD],[CEO],[CFO],[ACCOUNT]))PVT


Output
PROPOSER [HOD] [CEO] [CFO] [ACCOUNT]
kiran kranthi, PHANI, ARUN, SANGEET,
SURYA NULL NULL NULL MALATHI,SRIKANTH,
SWATHI MALATHI, PANKAJ,VARSHA, NULL NULL
Post #1363131
Posted Sunday, September 23, 2012 3:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:57 AM
Points: 1,949, Visits: 8,304
Try a 'manual pivot' like i show in this article http://www.sqlservercentral.com/articles/T-SQL/73887/



Clear Sky SQL
My Blog
Kent user group
Post #1363207
Posted Sunday, September 23, 2012 1:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 36,789, Visits: 31,247
To clean up the presence of trailing commas, use STUFF on ssurve's code like the following...

 SELECT PROPOSER,HOD,CEO,CFO,ACCOUNT
FROM ( --=== This concatenates multiple Approver values.
SELECT PROPOSER,
DESIGNATION,
APPROVER = STUFF((SELECT ',' + APPROVER
FROM @Tmp t1
WHERE t1.PROPOSER = t2.PROPOSER
AND t1.DESIGNATION = t2.DESIGNATION
ORDER BY t1.PROPOSER
FOR XML PATH('')),1,1,'')
FROM @Tmp t2
) d
PIVOT (MAX(APPROVER) FOR DESIGNATION IN(HOD,CEO,CFO,ACCOUNT))PVT
;


Output:
PROPOSER HOD     CEO           CFO  ACCOUNT
-------- ------- ------------- ---- ----------------
kiran kranthi PHANI ARUN SANGEET
SURYA NULL NULL NULL MALATHI,SRIKANTH
SWATHI MALATHI PANKAJ,VARSHA NULL NULL


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1363285
Posted Monday, September 24, 2012 11:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 16, 2012 9:28 PM
Points: 15, Visits: 167
THANK YOU ALL:)
Post #1363783
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse