NEED HELP IN PIVOT

• SWATH

  Old Hand

  Points: 303

  More actions

  September 22, 2012 at 3:31 am

  #282030

  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

• SWATH

  Old Hand

  Points: 303

  More actions

  September 22, 2012 at 3:36 am

  #1541056

  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

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  September 22, 2012 at 5:48 am

  #1541065

  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.

• ssurve

  SSC Enthusiast

  Points: 176

  More actions

  September 22, 2012 at 10:35 am

  #1541092

  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]

  kirankranthi,PHANI,ARUN,SANGEET,

  SURYANULLNULLNULLMALATHI,SRIKANTH,

  SWATHIMALATHI,PANKAJ,VARSHA,NULLNULL

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  September 23, 2012 at 3:04 am

  #1541150

  Try a 'manual pivot' like i show in this article http://www.sqlservercentral.com/articles/T-SQL/73887/[/url]

  ---

  
  Clear Sky SQL
  My Blog[/url]

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  September 23, 2012 at 1:24 pm

  #1541225

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• SWATH

  Old Hand

  Points: 303

  More actions

  September 24, 2012 at 11:14 pm

  #1541647

  THANK YOU ALL:)