Post reply

display selected pivot data

  • May 5, 2009 at 3:32 am

    #202841

    hi, I have data like

    Select personid, company from persons

    123 bbc

    123 google

    123 microsoft

    124 microsoft

    124 servercentral

    124 google

    124 itv

    how can I show on the top 2 rows for each person in this format.

    personid company1 company2

    123 bbc google

    124 microsoft servercentral

  • May 5, 2009 at 4:22 am

    #988646

    Note: This is not a generic solution. Adjust the order by clause of rank to get the desired result

    SELECT personid, MAX(company) AS company1, MIN (company) as company2

    FROM (

    SELECT RANK() OVER(PARTITION BY personid ORDER BY company) rnk, personid, company

    FROM persons ) p

    WHERE rnk IN (1, 2)

    GROUP BY personid

  • May 5, 2009 at 4:42 am

    #988656

    great use of rank() !!

    thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

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