Multiple rows in one cell - 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 2005 Forums

Transact-SQL (2005)

Multiple rows in one cell

Author

Topic

gerbenhk
Starting Member

2 Posts

Posted - 2010-10-02 : 09:18:12

Hi,

I would like to combine multiple rows into one cell. I have the following table:

Incidents
-accountid
-subject

The table can containt multiple rows with the same account id like this:

11111 This is a test
11111 This is another test

Now I would like to combine the subjects and put this in a cell in another table:

Combined
- Accountid
- Combined Subjects

then the row must be:

11111 This is a test;This is another test

Can anyone help me out?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-02 : 09:56:56

Try this -


;WITH C AS 
(
SELECT accountid, Subject FROM Incidents
)

SELECT Distinct accountid, 
STUFF((SELECT ';' + subject FROM Incidents WHERE accountid = c.accountid FOR XML PATH('')),1,1,'') 
FROM C

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

gerbenhk
Starting Member

2 Posts

Posted - 2010-10-02 : 13:36:04

Hi,

Based on your example, this is what I have made, and it works

;WITH C AS
(
SELECT [Ask_Roger_B_V__MSCRM].dbo.IncidentBase.accountid, [Ask_Roger_B_V__MSCRM].dbo.IncidentBase.title FROM [Ask_Roger_B_V__MSCRM].dbo.IncidentBase
)

SELECT Distinct accountid,
STUFF((SELECT + CHAR(13) + CHAR(10) + title FROM [Ask_Roger_B_V__MSCRM].dbo.IncidentBase WHERE accountid = c.accountid FOR XML PATH('')),1,6,'')
FROM C

Now I only want to run this query as a job, which update the two columns AccountID and Title in the table [Ask_Roger_B_V__MSCRM].dbo.AllCan_Incidents

Can you help we wich command I have to add to this query

thanks

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-04 : 03:03:32

Might be this -


UPDATE [Ask_Roger_B_V__MSCRM].dbo.AllCan_Incidents SET title = A.title
FROM [Ask_Roger_B_V__MSCRM].dbo.AllCan_Incidents U
INNER JOIN 
(
SELECT Distinct accountid, 
STUFF((SELECT + CHAR(13) + CHAR(10) + title FROM [Ask_Roger_B_V__MSCRM].dbo.IncidentBase WHERE accountid = c.accountid FOR XML PATH('')),1,6,'') AS title
FROM C
) A ON U.accountid = A.accountid

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources