Combining rows in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Combining rows in SQL

Author

Topic

codemonkey5
Starting Member

6 Posts

Posted - 2011-03-08 : 19:00:21

I've run into a problem on a sql query I've been working on. I have a client that is running an internal built messaging system and now they want the system to send messages to multiple users. The system is all SQL based but when a user sends a message to mutiple people, I want that message to show once with all the users names, message subject, etc. Each person the message is sent to will post to a sql table as an individual row with the message being sent to mutiple users being assigned a group id that is generated at run time.

So far I can pull the messages from the db with a stored procedure but the procedure pulls each row individually so that messages sent to multiple users are seen as being sent to each individual user.

Here is my setup:

table
-----------------
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[SenderID] [int] NOT NULL,
[RecipientID] [int] NOT NULL,
[MsgSubject] [nvarchar](255) NULL,
[MsgMessage] [ntext] NOT NULL,
[DateCreated] [datetime] NOT NULL,
[OriginalMessageID] [int] NULL,
[IsReadReceipt] [bit] NOT NULL,
[GroupID] [nvarchar](100) NULL,

Stored Procedure
--------------
@UserID As int
AS
SELECT
MessageID,
SenderID,
RecipientID,
MsgSubject,
MsgMessage,
DateCreated,
GroupID,
OriginalMessageID,
Recipient.UserName as RecipientName, (Recipient.FirstName + ' ' + Recipient.LastName) as RecipientFullName
FROM MessageCenterMessages LEFT OUTER JOIN dbo.Users AS Sender ON dbo.MessageCenterMessages.SenderID = Sender.UserID LEFT OUTER JOIN dbo.Users AS Recipient ON dbo.SNP_MessageCenterMessages.RecipientID = Recipient.UserID
WHERE SenderID = @UserID

and for grouping i've got this so far
---------------------------------
DECLARE @strValues varchar(8000)
Declare @userID int = 4
SELECT @strValues = COALESCE(@strValues+';', '') + RecipientFullName
FROM
(
SELECT
MessageID,
SenderID,
RecipientID,
MsgSubject,
MsgMessage,
DateCreated,
GroupID,
OriginalMessageID,
Recipient.UserName as RecipientName,
(Recipient.FirstName + ' ' + Recipient.LastName) as RecipientFullName
FROM MessageCenterMessages LEFT OUTER JOIN dbo.Users AS Sender ON dbo.MessageCenterMessages.SenderID = Sender.UserID LEFT OUTER JOIN dbo.Users AS Recipient ON dbo.MessageCenterMessages.RecipientID = Recipient.UserID
) X
WHERE GroupID <> ''
ORDER BY DateCreated

SELECT [Result] = @strValues

Can anyone tell me a way to have the stored procedure run as normal but if there is a groupid assigned, combine the rows and return them all from one stored procedure? Any help is greatly appreciated, I'm pulling my hair out on this one

CorbinTech
Starting Member

2 Posts

Posted - 2011-03-08 : 22:18:35

Can you provide some insert statements for sample data?

CorbinTech
Starting Member

2 Posts

Posted - 2011-03-08 : 22:28:45

Also, are the two DML statements separate Stored Procedures?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-09 : 11:22:47

see scenario 3

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

codemonkey5
Starting Member

6 Posts

Posted - 2011-03-09 : 11:35:32

Right now the stored procedures are two but ideally I would want them as one procedure that is returning the messages sent to single users but if they have a group id, combining the the rows based on that group id and returning one row.

Here is the query for creating the tables with data, tanks for the help:

create table MessageTable (MessageID int identity(1,1), SenderID int,
RecipientID int, MsgSubject nvarchar(255), MsgMessage ntext, DateCreated datetime,
OriginalMessageID int, IsReadReceipt bit, GroupID nvarchar(100))
/*Individual Messages*/
insert into MessageTable values (1,3,'Test Message','This is a test demo message','2010-12-28 12:23:22.000', 0, 1, NULL)
insert into MessageTable values (2,1,'Test Message2','This is test demo message 2','2010-12-28 12:23:22.000', 0, 1, NULL)
insert into MessageTable values (3,1,'Test Message3','This is test demo message 3','2010-12-28 12:23:22.000', 0, 1, NULL)
/*First Group Messages*/
insert into MessageTable values (3,2,'Test Message4','This is test demo message 4','2010-12-28 12:23:22.000', 0, 1, 'a0ed1a52-c12c-4584-b641-7fc0773233b8')
insert into MessageTable values (3,1,'Test Message4','This is test demo message 4','2010-12-28 12:23:22.000', 0, 1, 'a0ed1a52-c12c-4584-b641-7fc0773233b8')
/*Second Group Messages*/
insert into MessageTable values (3,1,'Test Message5','This is test demo message 5','2010-12-28 12:23:22.000', 0, 1, 'b1cs1a52-c12c-4584-b641-7fc0773233b8')
insert into MessageTable values (3,2,'Test Message5','This is test demo message 5','2010-12-28 12:23:22.000', 0, 1, 'b1cs1a52-c12c-4584-b641-7fc0773233b8')

create table Users (UserID int identity(1,1), Username nvarchar(100),
FirstName nvarchar(50), LastName nvarchar(50))
/*Insert Users*/
insert into Users values ('User1', 'Demo', 'User 1')
insert into Users values ('User2', 'Demo', 'User 2')
insert into Users values ('User3', 'Demo', 'User 3')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-09 : 12:03:50

[code]SELECT mt.*,su.Username + ' ' + su.FirstName + ' ' + su.LastName AS Sender,COALESCE(LEFT(t.u,LEN(t.u)-1),ru.Username + ' ' + ru.FirstName + ' ' + ru.LastName) AS Recipient
FROM (SELECT DISTINCT SenderID,MsgSubject,MsgMessage,DateCreated,OriginalMessageID, IsReadReceipt, GroupID FROM MessageTable) mt
INNER JOIN Users su
ON su.UserID = mt.SenderID
INNER JOIN Users ru
ON ru.UserID = mt.RecipientID
OUTER APPLY (SELECT Username + ' ' + FirstName + ' ' + LastName + ','
FROM Users u1
INNER JOIN MessageTable mt1
ON mt1.RecipientID = u1.UserID
WHERE mt1.GroupID = mt.GroupID
FOR XML PATH(''))t(u)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

codemonkey5
Starting Member

6 Posts

Posted - 2011-03-09 : 12:21:38

It's getting there but how do I group the rows together? When I run the query I'm still seeing 7 rows in the results when I only want to see 5.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-09 : 12:25:37

[code]
SELECT mt.*,su.Username + ' ' + su.FirstName + ' ' + su.LastName AS Sender,LEFT(t.u,LEN(t.u)-1) AS Recipient
FROM (SELECT DISTINCT SenderID,MsgSubject,MsgMessage,DateCreated,OriginalMessageID, IsReadReceipt, GroupID FROM MessageTable) mt
INNER JOIN Users su
ON su.UserID = mt.SenderID
OUTER APPLY (SELECT Username + ' ' + FirstName + ' ' + LastName + ','
FROM Users u1
INNER JOIN MessageTable mt1
ON mt1.RecipientID = u1.UserID
WHERE (mt1.GroupID = mt.GroupID
OR (mt1.GroupID IS NULL AND mt1.MessageID=mt.MessageID))
FOR XML PATH(''))t(u)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

codemonkey5
Starting Member

6 Posts

Posted - 2011-03-09 : 12:29:45

Perfect! You are the man, thanks a lot.

codemonkey5
Starting Member

6 Posts

Posted - 2011-03-09 : 13:02:12

I just noticed something, when a single user message is sent your query is not populating the "Recipient" field but it is for the ones that have group id's?

codemonkey5
Starting Member

6 Posts

Posted - 2011-03-09 : 15:47:07

quote:

Originally posted by visakh16


SELECT mt.*,su.Username + ' ' + su.FirstName + ' ' + su.LastName AS Sender,LEFT(t.u,LEN(t.u)-1) AS Recipient
FROM (SELECT DISTINCT SenderID,MsgSubject,MsgMessage,DateCreated,OriginalMessageID, IsReadReceipt, GroupID FROM MessageTable) mt
INNER JOIN Users su
ON su.UserID = mt.SenderID
OUTER APPLY (SELECT Username + ' ' + FirstName + ' ' + LastName + ','
             FROM Users u1
             INNER JOIN MessageTable mt1
             ON mt1.RecipientID = u1.UserID
             WHERE (mt1.GroupID = mt.GroupID 
             OR (mt1.GroupID IS NULL AND mt1.MessageID=mt.MessageID))
             FOR XML PATH(''))t(u)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Invalid column name 'MessageID', there isn't a message ID column for mt defined. When I add a 'MessageID' it messes up the query b/c of the distinct?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources