combining rows into one column in sql

Question

I have a list of rows with name leader name and manager name but these records are coming after joining few tables

SELECT DISTINCT 
    (leader.name), manager.Name 
FROM 
    ref, leader, orgList, manager, modules 
WHERE 
    leader.id = orgList.leaderCode
    AND orgList.dept = ref.id
    AND orgList.manager = manager.id
    AND orgList.id = modules.orgListId
    AND ref.id = 'xyzzz123'
    AND ref.month = 'august'
    AND modules.year = '2018'

I get result like this

leader_name       manager_name
---------------------------------
 John              Jim
 John              Hiko
 John              Sevu
 John              Celi
 Kerst             Newon
 Kerst             Dollace

But I actually want result like this

leader_name       manager_name
------------------------------------
John              Jim;Hiko;Sevu;Celi
Kerst             Newon;Dollace

I tried pivot and other things but that works only with one table.

Hint: JOIN. ON. That doesn't solve your problem, it just brings you up-to-date on SQL syntax. — Gordon Linoff
– Gordon Linoff, Commented May 28, 2019 at 22:31
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged — marc_s
– marc_s, Commented May 29, 2019 at 4:10

mkRabbani · Accepted Answer · 2019-05-29 04:24:40Z

Check this-

Note: Use Standard JOIN instead of comma separated JOIN

WITH CTE(leader_name,manager_name)
AS
(
    SELECT DISTINCT 
        (leader.name) leader_name, manager.Name  manager_name
    FROM 
        ref, leader, orgList, manager, modules 
    WHERE 
        leader.id = orgList.leaderCode
        AND orgList.dept = ref.id
        AND orgList.manager = manager.id
        AND orgList.id = modules.orgListId
        AND ref.id = 'xyzzz123'
        AND ref.month = 'august'
        AND modules.year = '2018'
)


SELECT 
leader_name ,
manager_name = 
    STUFF
    (
            (
                SELECT ';' + manager_name 
                FROM CTE B
                WHERE A.leader_name = B.leader_name
                FOR XML PATH ('')), 1, 1, ''
    )
FROM CTE A
GROUP BY leader_name

Collectives™ on Stack Overflow

combining rows into one column in sql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related