Post reply

Need Help with Pivooting the Columns..

  • 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

  • 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

  • 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

  • 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

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply