SQL - Combining results (rows) of one value

Question

I think this is a newbie question but I am not coming up with anything is my searches.

Two tables:

Table1 Name: CLIENT

Inactive    ClientID    Name
0           1001        Fred
0           1002        Cindy
0           1003        John
0           1004        Sherry

Table2 Name: JOURNAL

ClientID    RecordType  Date       Comments
1001        Note       01-01-2012   TXT1
1001        Note       01-01-2012   TXT2
1003        Note       01-01-2012   TXT3
1001        Note       01-02-2012   TXT4
1002        Note       01-06-2012   TXT5
1003        Note       01-22-2012   TXT6
1003        Note       01-23-2012   TXT7

If have no way of knowing how many rows there will be in JOURNAL for a particular ClientID. Could be none or many.

If I use the following which gives me everything EXCEPT 1004 so that is my first issue.

SELECT FROM CLIENT.InActive, CLIENT.ClientID, CLIENT.Name, JOURNAL.Comments
LEFT OUTER JOIN JOURNAL ON CLIENT.ClientID = JOURNAL.ClientID
WHERE CLIENT.Inactive = 0 and JOURNAL.Date > '2011-01-01'

What I am trying to do is combine the results of the JOURNAL.Comments into one record as such.

ClientID    Name    Comments
1001        Fred    TXT1, TXT2, TXT4
1002        Cindy   TXT5
1003        John    TXT3, TXT6, TXT7
1004        Sherry

UPDATED: I am pulling data from a Faircom ODBC source so I am limited and unable to use CREATE for a TMP table. Am using Excel or MSQUERY as I cannot get SMS to connect to FairCom driver.

Any suggestions would be appreciated. YES ... I am aware of alias'. Wanted to keep it simple as I am having a hard time grasping this one.

Sorry for the formatting. Somehow it got butchered upon submission. PLease let me know if you cannot follow. Thx. — user1650365, Sep 06 '12 at 00:43
Duplicate of [SQL Server: Can I Comma Delimit Multiple Rows Into One Column?](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) — Kermit, Sep 06 '12 at 00:53
The reason you aren't getting 1004 in the result is that you use JOURNAL in your where clause. — jswolf19, Sep 06 '12 at 01:02

Taryn · Answer 1 · 2013-06-07T10:14:27.783

First things first, you are not getting the 1004 record because of where you have placed the date filter, move it to the left join:

select c.inactive,
  c.clientid,
  c.name,
  j.comments
from client c
left join journal j
  on c.clientid = j.clientid
  and j.dt > '2011-01-01'
where c.inactive = 0

Second, you did not specify the RDBMS, so here are some options:

In SQL Server you can use STUFF():

select c.inactive,
  c.clientid,
  c.name,
  STUFF((SELECT distinct ', ' + comments 
              from journal j
              where j.clientid = c.clientid
                 and j.dt > '2011-01-01'
            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
from client c
where c.inactive = 0

See SQL Fiddle with Demo

In MySQL use GROUP_CONCAT():

select c.inactive,
  c.clientid,
  c.name,
  GROUP_CONCAT(COALESCE(j.comments, 'NULL'))
from client c
left join journal j
  on c.clientid = j.clientid
  and j.dt > '2011-01-01'
where c.inactive = 0
GROUP BY c.inactive,
  c.clientid,
  c.name

See SQL Fiddle with Demo

In Oracle you can use the listagg() function:

select c.inactive,
  c.clientid,
  c.name,
  LISTAGG(j.comments, ',') WITHIN GROUP (ORDER BY c.clientid) AS comments
from client c
left join journal j
  on c.clientid = j.clientid
  and j.dt > to_date('2011-01-01', 'yyyy-mm-dd')
where c.inactive = 0
GROUP BY c.inactive,
  c.clientid,
  c.name

See SQL Fiddle with Demo

Why would I be getting "Literal not allowed in ON condition"? I suspect it is the ' around the value. Using either MSQury or excel connecting to a FairCOM ODBC driver. — user1650365, Sep 06 '12 at 02:40

score 3 · Answer 2 · answered Sep 06 '12 at 01:02

Here's another version using CROSS APPLY

SELECT a.inactive , 
       a.clientid,
       a.name,
       SUBSTRING(d.CommentsList,1, LEN(d.CommentsList) - 1) CommentsList
FROM   client a
       CROSS APPLY
       (
          SELECT comments + ', ' 
          FROM journal AS B 
          WHERE A.clientid = B.clientid 
          FOR XML PATH('')
        ) D (CommentsList)

SQLFiddle Demo

special thanks to bluefeet for the SQLFiddle DDL

score 2 · Answer 3 · answered Sep 06 '12 at 00:51

For SQL Server 2008 this is the solution, add your where clause

SELECT  ClientID   ,Name ,Comments= STUFF((SELECT ','+ yt.Comments
                                                    FROM JOURNAL yt
                                                    WHERE yt.ClientID = sc.ClientID            
                                                    FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
         FROM CLIENT AS sc

SQL - Combining results (rows) of one value

3 Answers3

SQLFiddle Demo

Linked