SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question about select query

    Hello,

    I have a table with records like this:

    Code:
    -----------------
    | Id  | Message |
    -----------------
    | 01  | Test1   |
    | 02  | Test2   |
    | 01  | Test3   |
    | 03  | Test4   |
    | 02  | Test5   |
    | 01  | Test6   |
    -----------------
    I want to create a query that returns the results as this:

    Code:
    -----------------------------
    | Id  | Message             |
    -----------------------------
    | 01  | Test1, Test3, Test6 |
    | 02  | Test2, Test5        |
    | 03  | Test3               |
    -----------------------------
    I've been trying to do it but I'm still yet to succeed. Any ideas?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    we have two forums, databases and mysql, and i notice you did not post in the mysql forum

    you can do what you want easily in mysql, but it's not really worth attempting in any other database system because it's quite complex, and it's better to do it in the application

    which database system are you using?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In MySQL:

    Code:
    SELECT Id, GROUP_CONCAT(Message SEPARATOR ', ') AS Message FROM Table GROUP BY Id;

  4. #4
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In any other database, assuming you have a limited number of messages per id, you can do a denormalization query to accomplish a similar output, although a UDF would be a preferable solution (UDFs are easy to implement in Most DBs, and there's probably a group-concat equiv out there already - check google):

    Denorm Query:
    Code:
    SELECT i.Id,
        CONCAT(
            MAX(CASE WHEN d1.Message IS NOT NULL THEN d1.Message ELSE '' END),
            MAX(CASE WHEN d2.Message IS NOT NULL THEN CONCAT(', ', d2.Message) ELSE '' END),
            MAX(CASE WHEN d3.Message IS NOT NULL THEN CONCAT(', ', d3.Message) ELSE '' END),
            MAX(CASE WHEN d4.Message IS NOT NULL THEN CONCAT(', ', d4.Message) ELSE '' END),
            MAX(CASE WHEN d5.Message IS NOT NULL THEN CONCAT(', ', d5.Message) ELSE '' END),
            MAX(CASE WHEN d6.Message IS NOT NULL THEN CONCAT(', ', d6.Message) ELSE '' END)
        ) AS Message
    FROM
        (SELECT DISTINCT Id FROM Table) AS i
        LEFT JOIN Table AS d1 ON i.Id = d1.Id
        LEFT JOIN Table AS d2 ON i.Id = d2.Id 
            AND d2.Message != d1.Message
        LEFT JOIN Table AS d3 ON i.Id = d3.Id
            AND d3.Message != d1.Message
            AND d3.Message != d2.Message
        LEFT JOIN Table AS d4 ON i.Id = d4.Id 
            AND d4.Message != d1.Message
            AND d4.Message != d2.Message
            AND d4.Message != d3.Message
        LEFT JOIN Table AS d5 ON i.Id = d5.Id 
            AND d5.Message != d1.Message
            AND d5.Message != d2.Message
            AND d5.Message != d3.Message
            AND d5.Message != d4.Message
        LEFT JOIN Table AS d6 ON i.Id = d6.Id 
            AND d6.Message != d1.Message
            AND d6.Message != d2.Message
            AND d6.Message != d3.Message
            AND d6.Message != d4.Message
            AND d6.Message != d5.Message
    GROUP BY i.Id;
    Not sure if that's exactly right, but you get the idea... it's REALLY inefficient, in case you can't tell LOL.

    You would really need an index on both Id and Message, although your Primary Key is already technically Id + Message.

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your answers I'm doing this in SQL Server 2008.

    In the meantime I found a query that actually does what I need. What do you think of this:

    Code:
    SELECT	i.Id,
    		STUFF(g.y, 1, 1, '') AS Msgs
    FROM	(
    			SELECT		Id
    			FROM		[TABLE]
    			GROUP BY	Id
    		) AS i
    CROSS APPLY	(
    			SELECT DISTINCT	', ' + CAST(Message AS VARCHAR(100))
    			FROM		[TABLE] AS s
    			WHERE		s.Id = i.Id
    			ORDER BY	', ' + CAST(Message AS VARCHAR(100))
    			FOR XML		PATH('')
    		) AS g(y)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by prl View Post
    What do you think of this:
    you don't want to know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In MS-SQL, try this:
    Code:
    CREATE FUNCTION ConcatMessages (@MessageId int) RETURNS varchar(max) AS
    BEGIN
        DECLARE @Output varchar(max);
        SELECT @Output = CONCAT(@Output, (CASE WHEN @Output IS NULL THEN '' ELSE ', ' END), Message)
        FROM Table
        WHERE Id = @MessageId;
        RETURN @Output;
    END
    GO
    Then your query can look like this:
    Code:
    SELECT Id, ConcatMessages(Id) AS Message FROM Table GROUP BY Id;

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    In MS-SQL, try this:
    microsoft sql server has a CONCAT function?

    whoa
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LMAO.... been a while... I'm MySQL-Brained!!! Use + instead of CONCAT().

    FYI, I was confusing COALESCE(), which is a standard function that's in virtually every DB... was thinking it was CONCAT() that's on every DB for some reason... not sure why.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •