Post reply

Help with multivalue crosstable conversion

  • September 19, 2007 at 10:24 am

    #198027

    Hi,

    I am working on a query for a while, but I just can't make it work. I can do if there is only one member per role, also with multiple values for each group separately, but I don't seem to be able to report it for all the groups together.

    I have a view with the following "data":

    GroupID Member Role

    ---------------------------

    1member1role1

    1member2role2

    1member3role2

    1 member4 role3

    1member5 role3

    2member4role1

    2member3role1

    I need to provide this data in the following layout:

    GroupIDRole1 Role2Role3

    ------------- -------------

    1member1 member2, member3member4, member5

    2member4, member5 nullnull

    Is there any way to do this in a stored procedures or a view without cursors in SQL 2000?

    Thank you for your help.

  • September 19, 2007 at 6:52 pm

    #734773

    What's the maximum number of roles?

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

  • September 19, 2007 at 7:05 pm

    #734775

    Create a function dbo.MembersList (GroupID) to build the Members list for any particular GroupId

    and run this:

    SELECT GroupID, dbo.MembersList (GroupID)

    FROM (

    select GroupID from YourViewName

    GROUP BY GroupID

    ) DT

    This does not use an explicit cursor, it makes it "hidden".

    _____________
    Code for TallyGenerator

  • September 19, 2007 at 9:49 pm

    #734809

    For now I have only three roles. - Erzsebet

  • September 20, 2007 at 9:58 am

    #734992

    Thank you so much. This is exactly what I needed.

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

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