T-SQL, concatenate strings while doing multiple join and group by

Question

I wan't to concatenate all strings in a group, while doing a multiple join. Below is my scenario

Table - log

id | appname | level
---------------------
1  | app1    | debug
2  | app3    | warn
3  | app1    | debug
4  | app2    | info

Table - log_tag

id | text
----------
1  | tag_wink
2  | tag_steve
3  | ignore
4  | jimmy

Table - log_tag_map

tag_id | log_id
---------------
1      | 1
1      | 2
2      | 4
2      | 1
3      | 1

My goal is; For each log, join all it's tags separated by comma.

This is my desired output

id | appname | level | text
----------------------------
1  | app1    | debug | tag_wink, tag_steve, ignore
2  | app3    | warn  | tag_wink
3  | app1    | debug | 
4  | app2    | info  | tag_steve

I've bee trying a lot of things but can't seem to get the desired output.

This gives each log with a single tag_text

SELECT 
    log_table.id,
    log_table.appname,
    log_table.level,
    log_tag_table.text,
FROM log_table
INNER JOIN [log_tag_map] log_tag_map_table  
    ON log_tag_map_table.log_id = log_table.id
INNER JOIN [log_tag] log_tag_table 
    ON log_tag_map_table.tag_id = log_tag_table.id
ORDER BY log_table.id

After researching about concatenating strings in T-SQL, I used the famous FOR XML PATH solution, but I can't seem to get my desired result. I keep getting errors saying that "log_tag_map_table.tag_id" is invalid because it isn't in an aggregate function.

SELECT 
    log_table.id,
    MAX(log_table.appname),
    MAX(log_table.level),
    STUFF((
        SELECT ', ' + [log_tag].text 
        FROM [log_tag] 
        WHERE
            log_tag_map_table.tag_id = log_tag.id AND 
            log_tag_map_table.log_id = log_table.id
        FOR XML PATH (''))
        , 1
        , 2
        , '')
FROM log_table
INNER JOIN [log_tag_map] log_tag_map_table  
    ON log_tag_map_table.log_id = log_table.id
INNER JOIN [log_tag] log_tag_table 
    ON log_tag_map_table.tag_id = log_tag_table.id
GROUP BY log_table.id
ORDER BY log_table.id

Pரதீப் · Accepted Answer · 2016-10-07 17:52:16Z

You can fix that by adding the renaming column's in Group By. The Group by in your query is just to remove the duplicates.

I prefer APPLY operator over correlated-sub-query. It looks simple and removal of leading or trailing comma is very easy

SELECT DISTINCT log_table.id,
                log_table.appname,
                log_table.level,
                LEFT(concat_text, Len(concat_text) - 1) as Concat_text
FROM   log_table
       OUTER apply (SELECT [log_tag].text + ','
                    FROM   [log_tag_map] log_tag_map_table
                           JOIN log_tag
                             ON log_tag_map_table.tag_id = log_tag.id
                    WHERE  log_tag_map_table.log_id = log_table.id
                    FOR XML PATH ('')) oa (concat_text) 
ORDER BY log_table.id

Demo :

Schema Setup

CREATE TABLE log_table
  (
     [id]      INT,
     [appname] VARCHAR(4),
     [level]   VARCHAR(5)
  );

CREATE TABLE log_tag
  (
     [id]   INT,
     [text] VARCHAR(9)
  );

CREATE TABLE log_tag_map
  (
     [tag_id] INT,
     [log_id] INT
  );

Sample data

INSERT INTO log_table
            ([id],[appname],[level])
VALUES      (1,'app1','debug'),
            (2,'app3','warn'),
            (3,'app1','debug'),
            (4,'app2','info');


INSERT INTO log_tag
            ([id],[text])
VALUES      (1,'tag_wink'),
            (2,'tag_steve'),
            (3,'ignore'),
            (4,'jimmy');



INSERT INTO log_tag_map
            ([tag_id],[log_id])
VALUES      (1,1),
            (1,2),
            (2,4),
            (2,1),
            (3,1);

Query :

SELECT DISTINCT log_table.id,
                log_table.appname,
                log_table.level,
                LEFT(concat_text, Len(concat_text) - 1) as Concat_Text
FROM   log_table
       OUTER apply (SELECT [log_tag].text + ','
                    FROM   [log_tag_map] log_tag_map_table
                           JOIN log_tag
                             ON log_tag_map_table.tag_id = log_tag.id
                    WHERE  log_tag_map_table.log_id = log_table.id
                    FOR XML PATH ('')) oa (concat_text) 
Order by log_table.id

Reuslt :

╔════╦═════════╦═══════╦═══════════════════════════╗
║ id ║ appname ║ level ║        Concat_Text        ║
╠════╬═════════╬═══════╬═══════════════════════════╣
║  1 ║ app1    ║ debug ║ tag_wink,tag_steve,ignore ║
║  2 ║ app3    ║ warn  ║ tag_wink                  ║
║  3 ║ app1    ║ debug ║ NULL                      ║
║  4 ║ app2    ║ info  ║ tag_steve                 ║
╚════╩═════════╩═══════╩═══════════════════════════╝

Tried this,but still not providing the desired output (all text concatenated with ", ").
Thanks, the updated query seems to get the job done. Gotta get my head over thinking that Join will solve all my problem.

Unnikrishnan R · Accepted Answer · 2016-10-07 22:01:40Z

here is the way using STUFF.

SELECT  log_table.id,
        log_table.appname,
        log_table.level,
       STUFF((SELECT ', ' + [log_tag].text [text()]
         FROM [log_tag_map] log_tag_map_table
                           JOIN log_tag
                             ON log_tag_map_table.tag_id = log_tag.id
                    WHERE  log_tag_map_table.log_id = log_table.id
         FOR XML PATH('')),1,2,' ') text
FROM log_table

OUTPUT :

You will also get the OP using CROSS APPLY.

SELECT  Id ,appname,level,[text]
FROM    (
        SELECT   log_table.id,
        log_table.appname,
        log_table.level
        FROM    log_table
        ) a
CROSS APPLY
        (
        SELECT  CASE ROW_NUMBER() OVER(ORDER BY [log_tag].text) WHEN 1 THEN '' ELSE ', ' END +[log_tag].text [text()]
        FROM [log_tag_map] log_tag_map_table
                           JOIN log_tag
                             ON log_tag_map_table.tag_id = log_tag.id
                    WHERE  log_tag_map_table.log_id = a.id
        ORDER BY
                [log_tag].text
        FOR XML PATH ('')
        ) b([text])

OUTPUT :

Collectives™ on Stack Overflow

T-SQL, concatenate strings while doing multiple join and group by

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related