Post reply

Required suggestion for query for mentioned output

  • January 21, 2019 at 9:56 pm

    #373560

    Dear All,
    Below is the table structure with values and required output. I have tried with Join but it gives me 2 rows. Can you please help me.

    Tablestudent
    idname
    1            Vinayak
    2            Ajit
    3            Pavan

    Tableadmitiondetails
    sr. Nostudentid Acadamicyr DateOFadmission
    1                  1       I1/7/2008
    2                  2       I1/7/2008
    3                  1       II1/7/2009
    4                    2       II  1/7/2009
    5                  3        I1/7/2009

    expected result
    studidnameI_yearII_year
    1         Vinayak1/7/20081/7/2009
    2         Ajit1/7/20081/7/2009
    3         Pavan1/7/2009 

  • January 22, 2019 at 2:05 am

    #2019597

    You did not provide consumable test data, so the following code is untested.

    This code should get you going in the right direction
    SELECT ad.studentid
      , st.name
      , I_year = MAX( CASE WHEN ad.Acadamicyr = 'I' THEN ad.DateOFadmission END )
      , II_year = MAX( CASE WHEN ad.Acadamicyr = 'II' THEN ad.DateOFadmission END )
    FROM admitiondetails AS ad
    INNER JOIN student AS st
     ON ad.studentid = st.id
    GROUP BY ad.studentid, st.name

    How to post data/code on a forum to get the best help.

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

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