Post reply

Get values in Single record

  • September 17, 2010 at 12:01 am

    #229312

    Hello

    I am new to TSQL, Here is my scenario.

    I have Tutor, Subject & TutorSubject table as follows

    Tutor

    tutorid

    tutorname

    Subject

    SubjectId

    SubjectName

    TutorSubject

    Tutorid

    Subjectid

    Now what I want is I want to have tutorname along with subject name saperated by comma in one single record liek below

    TutorName SubjectName

    Tutor1 Sub1,Sub2,Sub3

    Tutor2 Sub2,Sub3

    Tutor3 Sub1,Sub3

    How can I achieve this, please help me on this issue.

    Thanks,

  • September 17, 2010 at 12:48 am

    #1222761

    DECLARE @tbl_Tutor TABLE

    (

    TutorID INT,

    TutorName VARCHAR(100)

    )

    DECLARE @tbl_Subject TABLE

    (

    SubjectID INT,

    SubjectName VARCHAR(100)

    )

    DECLARE @tbl_TutorSubject TABLE

    (

    TutorID INT,

    SubjectID INT

    )

    INSERT @tbl_Tutor

    SELECT 1, 'James' UNION ALL

    SELECT 2, 'John'

    INSERT @tbl_Subject

    SELECT 1, 'Science' UNION ALL

    SELECT 2, 'Mathematics' UNION ALL

    SELECT 3, 'History' UNION ALL

    SELECT 4, 'English'

    INSERT @tbl_TutorSubject

    SELECT 1, 1 UNION ALL

    SELECT 1, 2 UNION ALL

    SELECT 1, 4 UNION ALL

    SELECT 2, 2 UNION ALL

    SELECT 2, 3

    ; WITH cte_Tutor_Subject_Details AS

    (

    SELECT T.TutorName, S.SubjectName

    FROM @tbl_TutorSubject TS

    INNER JOIN @tbl_Tutor T ON TS.TutorID = T.TutorID

    INNER JOIN @tbl_Subject S ON TS.SubjectID = S.SubjectID

    )

    SELECT TSD.TutorName,

    STUFF ( (

    SELECT ', ' + SubjectName

    FROM cte_Tutor_Subject_Details TSD_Inn

    WHERE TSD_Inn.TutorName = TSD.TutorName

    ORDER BY SubjectName

    FOR XML PATH(''),TYPE

    ).value('.','VARCHAR(MAX)'),1,1,SPACE(0)) AS Subjects

    FROM cte_Tutor_Subject_Details TSD

    GROUP BY TSD.TutorName

    This should do it for you. You would have got faster answers had you provided the DDL and sample data as I have done.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • September 17, 2010 at 1:22 am

    #1222768

    Hello,

    Thanks for answer, but this is not what I am looking for

    Here it shows records in multiple rows in following way:-

    TutorName SubjectName

    Test1 Social Studies

    test1 Math

    test1 Social Studies

    test2 Math

    test2 Social Studies

    But I want 1 single record for 1 tutor in following way:-

    TutorName SubjectName

    Test1 Social Studies, Math, Social Studies

    test2 Math, Social Studies

    Please suggest me.

    Thanks,

  • September 17, 2010 at 1:27 am

    #1222770

    Please provide the query you are running. What was the result you got from the query i gave you?

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • September 18, 2010 at 8:51 am

    #1223465

    xyzxyzxyzxyz123 (9/17/2010)

    Now what I want is I want to have tutorname along with subject name saperated by comma in one single record liek below

    TutorName SubjectName

    Tutor1 Sub1,Sub2,Sub3

    Tutor2 Sub2,Sub3

    Tutor3 Sub1,Sub3

    Uisng Kingston's code, I get these results:

    TutorNameSubjects

    James English, Mathematics, Science

    John History, Mathematics

    Is this not what you asked for?

    regards gah

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • September 18, 2010 at 4:12 pm

    #1223515

    I agree with gah. Kingston's code does exactly what you asked for.

    At this point, I suggest you read and heed the article at the first link in my signature line below. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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