How to join the columns value in one column?

Question

I have a table with two columns relationship type and lives with like the following, I want all the relationship types values which has 'T' in lives with column in one column

+------------+---------+
|relationship|liveswith|
+------------+---------+
|A           |T        |
+------------+---------+
|B           |T        |
+------------+---------+
|C           |F        |
+------------+---------+

like this

+------------+---------+
|T           |F        |
+------------+---------+
|A B         |C        |
+------------+---------+

I tried using Pivot but it only gives me one values in T column. I am using Microsoft SQL Server 2012

https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — S3S, Feb 07 '19 at 16:58
How many possible values of `liveswith` are there? Are T/F = True/False, or are they something else? — Tab Alleman, Feb 07 '19 at 17:01

George Joseph · Accepted Answer · 2019-02-07T17:21:52.710

1

You can make use of for xml path('') and use STUFF as follows

 create table data(relationship varchar(10),liveswith varchar(10));

 insert into data values('A','T');
 insert into data values('B','T');
 insert into data values('C','F');

 with temp_output
     as (
       SELECT  a.liveswith
              ,STUFF((SELECT  '-' + relationship
                        FROM data a1
                       WHERE a1.liveswith=a.liveswith
                     ORDER BY relationship
                        FOR XML PATH('')), 1, 1, '') AS listStr
         FROM data a
       GROUP BY a.liveswith
         )
select max(case when liveswith='T' then liststr end) as 'T'
      ,max(case when liveswith='F' then liststr end) as 'F'
 from temp_output  

+-----+---+
|  T  | F |
+-----+---+
| A-B | C |
+-----+---+

edited Feb 07 '19 at 17:21

answered Feb 07 '19 at 17:01

George Joseph

5,842
10
24

That's not the same format as the desired result in the question. – Tab Alleman Feb 07 '19 at 17:02
@GeorgeJoseph can you explain the part where you union all I am not able to understand it – jazz b Feb 07 '19 at 17:18
Yep, sure i used the with data() to simulate a table. I have updated my answer to make use of an actual table using create and insert scripts – George Joseph Feb 07 '19 at 17:22
@GeorgeJoseph ohh ok Thank you I got that it means my table – jazz b Feb 07 '19 at 17:24

score 1 · Answer 2 · answered Feb 07 '19 at 17:03

1

You can pivot on true/false with case statements:

SELECT
string_agg(CASE WHEN liveswith THEN relationship ELSE '' END, ' ') AS T,
string_agg(CASE WHEN NOT liveswith THEN relationship ELSE '' END, ' ') AS F
FROM foobar;

Output:

  t   |  f
------+-----
 A B  |   C

answered Feb 07 '19 at 17:03

K Pekosh

633
1
6
15

I tried your suggestion but it says string_agg is not recognized as a built in function – jazz b Feb 07 '19 at 17:22
Sorry, looks like it was implemented in sql server 2017: http://www.sqlservercentral.com/blogs/sql-geek/2018/02/28/new-built-in-function-string_agg-sql-server-2017/ – K Pekosh Feb 07 '19 at 17:26

How to join the columns value in one column?

2 Answers2