Question about select query

Databases
1

Hello,

I have a table with records like this:

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

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

we have two forums, databases and [url=http://www.sitepoint.com/forums/databases-88/forumdisplay.php?f=182]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?

3

In MySQL:


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

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:


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

Thanks for your answers :slight_smile: 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:

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

you don’t want to know :x

7

In MS-SQL, try this:


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:


SELECT Id, ConcatMessages(Id) AS Message FROM Table GROUP BY Id;
8

microsoft sql server has a CONCAT function?

whoa :wink:

9

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. :smiley: