Monday, January 4, 2021

mySQL: Combine strings into a single list

In mySQL, how do I combine strings into a single list? That is, how do I pivot them from rows into a list of values?

First, let's take a look at the data we're dealing with. We have two tables, message and recipientinfo, and we want to show all messages as rows, and instead of having one row per TO recipient, we want to have one row per message, that holds all the people listed on the TO line for each message. To do this, in mySQL, we have the GROUP_CONCAT() function, and in SQL Server, in order to combine values, we have to STUFF() them.

Now, let's take a look at the statements using the Enron data set.





    GROUP_CONCAT(r.rvalue) as 'TO'

FROM recipientinfo as r

right JOIN message as m ON r.mid = m.mid 

where rtype = 'TO'

GROUP BY m.sender, date

LIMIT 100;

SQL Server


m.sender as MsgSender,

m.[date] as MsgDate,

MsgToList = STUFF((

SELECT ',' + r.rvalue

FROM dbo.recipientinfo as r

WHERE r.mid = m.mid and rtype = 'TO'

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

FROM dbo.[message] as m

No comments:

Post a Comment