How to Merge The Recods In SQL SEREVR

  • Hi All,

    This is my sample table please have a look on that

    Create Table MySampleTable

    (

    Name NVarchar(25),

    SubjectWiseMarks NVarchar(25)

    )

    Insert Into MySampleTable Values ('AAA','Maths-100')

    Insert Into MySampleTable Values ('AAA','Physics-90')

    Insert Into MySampleTable Values ('AAA','Chemistry-80')

    Insert Into MySampleTable Values ('BBB','Maths-90')

    Insert Into MySampleTable Values ('BBB','Physics-80')

    Insert Into MySampleTable Values ('CCC','Maths-80')

    CURRENT OUTPUT:

    NameSubjectWiseMarks

    AAAMaths-100

    AAAPhysics-90

    AAAChemistry-80

    BBBMaths-90

    BBBPhysics-80

    CCCMaths-80

    But My Required OUTPUT is Like This,

    REQUIRED OUTPUT:

    NameSubjectWiseMarks

    AAA Maths-100

    Physics-90

    Chemistry-80

    BBB Maths-90

    Physics-80

    CCC Maths-80

    Please give me the solution.

    Thanks in Advance,

    VenkiDesai.

  • NameSubjectWiseMarks

    AAAMaths-100

    Physics-90

    Chemistry-80

    BBBMaths-90

    Physics-80

    CCCMaths-80

    What Exactly u r looking for..???

    Do u want to merge these two columns in One COLUMN in Select Statement, With grouping The Unique Name..??

  • Mr. Kapsicum (10/23/2013)


    NameSubjectWiseMarks

    AAAMaths-100

    Physics-90

    Chemistry-80

    BBBMaths-90

    Physics-80

    CCCMaths-80

    What Exactly u r looking for..???

    Do u want to merge these two columns in One COLUMN in Select Statement, With grouping The Unique Name..??

    See, if this Helps,

    ;with cte as

    (

    select ROW_NUMBER() over (partition by name order by name) as RN,* from #MySampleTable

    )

    select case when Rn=1 then Name+SubjectWiseMarks else SubjectWiseMarks End NewColumn from cte

  • Thanks for replying sir

    But i want to have 2 different columns i.e., Name & SubjectWiseMarks.

    But for column Name i want distinct .

  • Hi venkidesaik,

    I thought below query met you requirement.Have a look into this ad try it.

    ;with cte as

    (

    select ROW_NUMBER() over ( order by name) as RN,* from MySampleTable

    )

    select

    (case when RN in (select min(RN) from cte group by Name) then Name else null end) as Name

    ,SubjectWiseMarks from cte

    [font="Courier New"]

    NameSubjectWiseMarks

    AAA Maths-100

    NULL Physics-90

    NULL Chemistry-80

    BBB Maths-90

    NULL Physics-80

    CCC Maths-80[/font]

    ***********************************

    ;with cte as

    (

    select ROW_NUMBER() over ( order by name) as RN,* from MySampleTable

    )

    select

    (case when RN in (select min(RN) from cte group by Name) then Name else ' ' end) as Name

    ,SubjectWiseMarks from cte

    [font="Courier New"]NameSubjectWiseMarks

    AAA Maths-100

    Physics-90

    Chemistry-80

    BBB Maths-90

    Physics-80

    CCC Maths-80 [/font]

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

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