CONCAT and CASE combined

Transact-SQL
1

Hi everyone,

I'm trying to create a query that concatenates the directions field that were involved in each transaction_id.

I've tried to do it by creating a flag table with multiple case whens, and then querying on it, but as you can see in the image below, I'm having trouble to get a multiple 1's flag in one single row ID.

Any ideas on how to do it?

Here are the code and the tables I'm using:

    SELECT 
    	t2.trans_id
    	CASE WHEN t2.dept_nbr IN (SELECT dept_nbr FROM t3 with WHERE dir = 'dir1')	THEN 1 ELSE 0 END AS flag_dir1,
    	CASE WHEN t2.dept_nbr IN (SELECT dept_nbr FROM t3 with WHERE dir = 'dir2')	THEN 1 ELSE 0 END AS flag_dir2
    FROM 
    	t2

image
image1548×293 8.96 KB

Thanks in advance.

2 
SELECT 
    t2.trans_id,
    CASE MAX(CASE WHEN t3.dir = 'dir1' THEN 1 ELSE '' END) + MAX(CASE WHEN t3.dir = 'dir2' THEN 2 ELSE '' END) 
        WHEN 1 THEN 'dir1' WHEN 2 THEN 'dir2' WHEN 3 THEN 'dir1 - dir2' END AS dir
FROM 
    #t2 t2
INNER JOIN
    #t3 t3 ON t3.dept_nbr = t2.dept_nbr
GROUP BY
    t2.trans_id
2 Likes
3

thank you! this worked.