How to combine several value in SQL

rowfei
rowfei used Ask the Experts™
on
Hi,

How can I combine serval lines' comments value if the line number more than 1? Need it in SQL statement.

Below is the current data output:

ID        Line     Type   Comment
23         1           A         This is
23         2           B          Test
24         1           C          Testing
25         1           B          Testing


Below is what I want to display:

ID        Line     Type   Comment
23         1           A         This is Test
23         2           B         This isTest
24         1           C          Testing
25         1           B          Testing


Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Which database product?  The solution will likely be different.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
In Oracle as long as the comments aren't over 4000 characters, try this:
drop table tab1 purge;
create table tab1(id number, line number, type char(1), comments varchar2(10));

insert into tab1 values(23,1,'A','This is');
insert into tab1 values(23,2,'B','Test');
insert into tab1 values(24,1,'C','Testing');
insert into tab1 values(25,1,'B','Testing');
commit;


select id, line, type, listagg(comments, ' ') within group(order by line) over(partition by id) comments
from tab1
/

Open in new window

Author

Commented:
This is SQL server database. I also have several other tables to join together to come up different data fields.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>> I also have several other tables to join together to come up different data fields

I would add the additional information for the SQL Server Experts when they arrive.

The more accurate the information you provide, the more accurate the solution you will receive.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Agreed, we'll need an English sounding description of what you're trying to pull off here for us to provide a solution.

Guessing you'll want something like T-SQL:  Normalized data to a single comma delineated string and back but without the commas.
Business Intelligence Developer and Analyst
Top Expert 2015
Commented:
Hi rowfei,

You can try below code.

CREATE TABLE #Temp
( ID INT,
LINE INT,
TYPE CHAR(1),
COMMNET VARCHAR(20))

INSERT INTO #Temp
VALUES
 (23,1,'A','This is') 
,(23,2,'B','Test')
,(24,1,'C','Testing') 
,(25,1,'B','Testing') 


SELECT #Temp.ID,#Temp.LINE,#Temp.TYPE,
(SELECT 

COMMNET+' '


FROM #Temp T 
WHERE T.ID=#Temp.ID ORDER BY T.LINE
FOR XML PATH ('')
) AS Comment
FROM #Temp

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial