Need Help with Pivooting the Columns..

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  February 7, 2013 at 4:33 pm

  #288948

  I am trying to achieve to get the results in a certain format so want to know how to do it ..

  --Create temp table

  CREATE TABLE #PivotTest

  (

  id nvarchar(500),

  Contact nvarchar(500),

  TypeId int,

  Valid Bit,

  Datefrom Datetime

  )

  --Populate the data

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(1, 'test@email.com', 1,0,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(1, '0280606597', 2,0,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(2, 'test1@email.com', 1,0,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(2, '0280000000', 2,1,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(3, 'test2@email.com', 1,0,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(3, '0281000000', 2,0,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(4, 'test3@email', 1,1,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(4, '0270000000', 2,0,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(5, 'test4@email.com', 1,0,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(5, '0290000000', 2,0,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(6, 'test5@com', 1,1,getdate())

  INSERT INTO #PivotTest (id, Contact, TypeId,Valid,DateFrom) VALUES(6, '0200', 2,1,getdate())

  Select * from #pivottest

  Expected Output..

  Id, Contact as Email, TypeID as EmailType,Valid as EmailStatus,Contact as Mobile, Typeid as MobileType,Valid as MobileStatus

  1 test@email.com 1 0 0280606597 2 0

  2 test1@email.com 1 0 02800000002 1

  Thanks in Advance

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman

• Dwain Camps

  SSC Guru

  Points: 86948

  More actions

  February 7, 2013 at 7:58 pm

  #1585727

  Something like this perhaps?

  SELECT id

  ,Email=MAX(CASE Typeid WHEN 1 THEN Contact END)

  ,EmailType=MAX(CASE Typeid WHEN 1 THEN TypeID END)

  ,EmailStatus=MAX(CASE Typeid WHEN 1 THEN CAST(Valid AS INT) END)

  ,Mobile=MAX(CASE Typeid WHEN 2 THEN Contact END)

  ,MobileType=MAX(CASE Typeid WHEN 2 THEN TypeID END)

  ,MobileStatus=MAX(CASE Typeid WHEN 2 THEN CAST(Valid AS INT) END)

  FROM #pivottest

  GROUP BY id

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  February 7, 2013 at 8:49 pm

  #1585738

  Thanks Dwain..Tat was simple.

  I thought i was good at writing SQL queries but not anymore ..I guess i was much of thinking on how to acheive the result set using pivot but never thought of doing it in simple way..

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman

• Dwain Camps

  SSC Guru

  Points: 86948

  More actions

  February 7, 2013 at 9:00 pm

  #1585742

  Sri8143 (2/7/2013)

  ---

  Thanks Dwain..Tat was simple.

  I thought i was good at writing SQL queries but not anymore ..I guess i was much of thinking on how to acheive the result set using pivot but never thought of doing it in simple way..

  Simple is good. I like simple.

  Truth is, I can never remember (or haven't bothered to memorize) the syntax for PIVOT because this way (crosstab) is so easy.

  Same is true for UNPIVOT - I always use CROSS APPLY VALUES (there's a link in my signature for this). That way is easy to remember and usually faster.

  Others may have differing opinions of course.

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  February 7, 2013 at 10:17 pm

  #1585755

  Sure , I wil have a look ..

  once again thanks ..

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman