CODEWITHSUNDEEP
sql
user1650365
user1650365

Reputation: 31

SQL - Combining results (rows) of one value

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.

Upvotes: 3

Views: 679

Answers (3)

Taryn
Taryn

Reputation: 247700

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

Upvotes: 4

John Woo
John Woo

Reputation: 263723

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

Upvotes: 3

ClearLogic
ClearLogic

Reputation: 3682

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

Upvotes: 2

Related Questions