Post reply

Cross tab Report F

  • February 14, 2011 at 10:44 pm

    #235125

    Dear All

    The Table name Examscheduledetails,

    The column three columns like Branchname,edate,courseid

    The table output like this

    Branchname edate courseid

    Aero 01 070180011

    Aero 01 080120011

    Aero 02 080180011

    Civil 01 080120011

    Civil 02 070190009

    Mech 01 null

    Mech 01 080120002

    Mech 01 080120003

    Mech 01 080120004

    We need the following type of output , Pls any ony help me to get the type of result set

    BranchName 01 02

    Aero 070180011,080120011 080180011

    Civil 080120011 070190009

    MEch Null 080120002,080120003,080120004

    Thanks

  • February 15, 2011 at 8:46 pm

    #1287438

    Are you sure you want the NULL in the output?

    --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)

  • February 15, 2011 at 9:03 pm

    #1287440

    Never mind. Here's the code to do as you asked. If you have any questions about how it works, please post back.

    SELECT t1.BranchName,

    [01] = STUFF(CAST(

    (

    SELECT ',' + x1.CourseID

    FROM dbo.ExamScheduleDetails x1

    WHERE x1.eDate = '01'

    AND x1.BranchName = t1.BranchName

    ORDER BY x1.CourseID

    FOR XML PATH(''),TYPE

    )

    AS VARCHAR(MAX)),1,1,''),

    [02] = STUFF(CAST(

    (

    SELECT ',' + x2.CourseID

    FROM dbo.ExamScheduleDetails x2

    WHERE x2.eDate = '02'

    AND x2.BranchName = t1.BranchName

    ORDER BY x2.CourseID

    FOR XML PATH(''),TYPE

    )

    AS VARCHAR(MAX)),1,1,'')

    FROM dbo.ExamScheduleDetails t1

    GROUP BY t1.BranchName

    ORDER BY t1.BranchName

    --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)

  • February 15, 2011 at 9:08 pm

    #1287441

    p.s. I suspect that a part of the reason why it took so long for someone to post a solution is because you didn't post the data in a "readily consumable" format. People like to test their code before they post it but frequently don't have the time to convert your "flat" test data. Please read the article at the first link in my signature line below before you post another question. It'll be worth it because people will actually take an interest in solving your problem if they don't have to work so hard to build test data.

    Here's one way you could have posted your data in a "Readily Consumable" format:

    --===== Do this demonstration in a nice safe place that everyone has

    USE TempDB

    ;

    --===== Create the test table on the fly and populate it with data.

    SELECT BranchName,eDate,CourseID

    INTO dbo.ExamScheduleDetails

    FROM (

    SELECT 'Aero' ,'01','070180011' UNION ALL

    SELECT 'Aero' ,'01','080120011' UNION ALL

    SELECT 'Aero' ,'02','080180011' UNION ALL

    SELECT 'Civil','01','080120011' UNION ALL

    SELECT 'Civil','02','070190009' UNION ALL

    SELECT 'Mech' ,'01', null UNION ALL

    SELECT 'Mech' ,'01','080120002' UNION ALL

    SELECT 'Mech' ,'01','080120003' UNION ALL

    SELECT 'Mech' ,'01','080120004'

    ) datagen (BranchName, eDate, CourseID)

    ;

    --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)

  • February 17, 2011 at 4:24 am

    #1288132

    Thank you ver much for your response

  • February 17, 2011 at 6:21 am

    #1288172

    You bet. Thank you for the reply. 🙂

    --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