Thread: Query problem

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    4
    Rep Power
    0

    Query problem


    I'm running an SQL Query to return a list of all games that it's the current players go - but displaying all the opponents

    Code:
                "SELECT " & _
                    "GameTable.[Game#] AS [G #], " & _
                    "PlayerTable.[Player#] AS [P #], " & _
                    "playerTable.[PlayerID] as [Opponent], " & _
                "FROM UserTable INNER JOIN (GameTable INNER JOIN PlayerTable ON GameTable.[Game#] = PlayerTable.[Game#]) ON UserTable.UserID = PlayerTable.PlayerID " & _
                "WHERE " & _
                    "(GameTable.[Game#] in (SELECT playerTable.[Game#] FROM PlayerTable WHERE  ((PlayerTable.PlayerID)='" & Vars.CurrentUserID & "')and ((PlayerTable.[Player#])=(GameTable.[CurrentPlayer]))))" & _
                    "and ((PlayerTable.PlayerID)<>'" & Vars.CurrentUserID & "') "
    This query get's a line for every opponent but my problem is that i want to add more then one opponent and i don't want more then one line per game

    I would like to put them all the opponent's names [comma seperated] into one cell

    can this be done all in one SQL Statement?

    Your help would be greatly appreciated
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    4
    Rep Power
    0
    Can anyone tell me if there is a way in MS SQL to combine multiple row's into one

    eg

    A B C
    1 1 1
    1 1 2
    1 2 1
    1 2 2
    1 2 3

    i want this displayed as

    A B AllC
    1 1 1,2
    1 2 1,2,3

    can this be done?
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Using COALESCE to Build Comma-Delimited String
    http://sqlteam.com/item.asp?ItemID=2368


    rudy

IMN logo majestic logo threadwatch logo seochat tools logo