Hello,
I have a table which has the following structure:
ID MessageText
001 Hello
001 There
001 Working
003 See
003 you
003 Next
003 Time
How to build a query or store procedure to return result like this:
ID MessageText
001 Hello There Working
003 See you Next Time
Your help/advice is greatly appreciated.
Thanks, FicisaYou don't have anything that would identify the order of the words in the sentence....|||The order is not important, as long as I can put them together into one field.|||USE Northwind
GO
CREATE TABLE myTable99(WordOrder int IDENTITY(1,1), [ID] char(3), MessageText varchar(255))
GO
INSERT INTO myTable99([ID], MessageText)
SELECT '001', 'Hello' UNION ALL
SELECT '001', 'There' UNION ALL
SELECT '001', 'Working' UNION ALL
SELECT '003', 'See' UNION ALL
SELECT '003', 'you' UNION ALL
SELECT '003', 'Next' UNION ALL
SELECT '003', 'Time'
GO
DECLARE @.messagetext varchar(2000), @.MAX_ID char(3), @.MIN_ID char(3)
DECLARE @.myTable99 TABLE ([ID] char(3), messagetext varchar(2000))
SELECT @.MAX_ID = MAX([ID])
, @.MIN_ID = MIN([ID])
FROM myTable99
SELECT @.messagetext = ''
WHILE @.MIN_ID <> @.MAX_ID
BEGIN
SELECT @.messagetext = COALESCE(@.messagetext+ ' ','') + MessageText
FROM myTable99
WHERE [ID] = @.MAX_ID
INSERT INTO @.myTable99([ID], MessageText)
SELECT @.MAX_ID, @.MessageText
SELECT @.MAX_ID = MAX([ID])
FROM myTable99
WHERE [ID] < @.MAX_ID
SELECT @.messagetext = ''
END
SELECT @.messagetext = COALESCE(@.messagetext+ ' ','') + MessageText
FROM myTable99
WHERE [ID] = @.MIN_ID
INSERT INTO @.myTable99([ID], MessageText)
SELECT @.MIN_ID, @.MessageText
SELECT * FROM @.myTable99
GO
DROP TABLE myTable99
GO|||aaaaaaaaaaaarggggh! code!!!
my eyes!!!!! they're BLEEEEEEEEEDING!!
hey, how's this for a solution:
select ID
, group_concat(MessageText) as MessageText
from atable
group
by ID
see GROUP BY functions (http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html)
not sql server?
pity, eh
:cool:|||That's mySQL not SQL Server|||Ok, how about the hybred approach:CREATE TABLE dbo.tMessages (
Id CHAR(3) NOT NULL
, MessageText VARCHAR(20) NOT NULL
)
INSERT dbo.tMessages (Id, MessageText)
SELECT '001', 'Hello'
UNION ALL SELECT '001', 'There'
UNION ALL SELECT '001', 'Working'
UNION ALL SELECT '003', 'See'
UNION ALL SELECT '003', 'you'
UNION ALL SELECT '003', 'Next'
UNION ALL SELECT '003', 'Time'
DROP FUNCTION dbo.fGrunge
GO
CREATE FUNCTION dbo.fGrunge (
@.id CHAR(3)
) RETURNS VARCHAR(4000) AS
BEGIN
DECLARE @.c VARCHAR(20), @.r VARCHAR(8000)
SET @.r = ''
DECLARE zfGrunge CURSOR FOR SELECT MessageText
FROM dbo.tMessages
WHERE id = @.id
ORDER BY MessageText
OPEN zfGrunge
FETCH zfGrunge INTO @.c
WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + ' ' + @.c
FETCH zfGrunge INTO @.c
END
CLOSE zfGrunge
DEALLOCATE zfGrunge
RETURN SubString(@.r, 2, 8000)
END
GO
SELECT a.id, dbo.fGrunge(a.id)
FROM dbo.tMessages AS a
GROUP BY a.id
ORDER BY a.id
DROP TABLE dbo.tMessages-PatP|||Pat,
A cursor?
Damn, it's only Monday....|||Oh wow,
Perfect!!!
Brett's answer works great. There sure are some programming, it would be nice to have group_concat() function in MS SQL. Forgot I can create one of my own. Thanks very much Pat!!!
Now here's another question, I was trying to use cursor to solve it, like in Pat's function, does it have any disadvantage/advantage over Brett's solution?
Thank you a bunch!!!
ficisa|||I can do it with oodles of left joins, but that gets ugly too. While cursors are a good way to kill an application, they are intent on killing this one anyway.
Trawling the result set is better done on the middle tier or the client anyway. This is fundamentally poor design. I was just offering this as a solution to the stated problem, not necessarily advocating it!
-PatP|||How much data you talking about?
Why not perf bechmark both and let us know...(ya gotta do something...:D )
But I'm thinking (ok, ok, don't all be sooo amazed) that the cursor will be slower...|||Originally posted by Brett Kaiser
That's mySQL not SQL Server
dude, i knew that
(something about grandmothers and eggs)
that's why i posted it!!
so that you sql server guys could SEE HOW IT SHOULD BE DONE
:cool:|||Originally posted by r937
dude, i knew that
(something about grandmothers and eggs)
that's why i posted it!!
so that you sql server guys could SEE HOW IT SHOULD BE DONE
:cool:
Rudy, where is that in the ANSI guide?
I can't find it...
:D|||ansi?
shirley you jest
as if microsoftborg gave two figs for ansi|||Don't call me shirley...
I prefer laverne|||Ok, I tested with my function below and Pat's function using a cursor, with 3436 groups, both took same amount of time of 4 seconds to complete. Hmmm....
So how about this as the function?
CREATE FUNCTION dbo.Group_Concat_Message
(@.InstitutionNumber char(6) )
RETURNS varchar(5000)
AS
BEGIN
DECLARE @.messagetext varchar(5000)
SELECT @.messagetext = COALESCE(@.messagetext+ ' ','') + MessageText
FROM vInstWithMessage
WHERE InstitutionNumber = @.InstitutionNumber
Return @.messagetext
END|||Why not...
Still think you'll end up worrying about the order of the data at some point..
GOOD LUCK...
and OH MY GOD...it's after 5:00!
Later..
