CODEWITHSUNDEEP
sql-servert-sql
CyberNinja
CyberNinja

Reputation: 864

SQL - Comma Delimited subset results of multiple column in its own column

I have a view with a union all table, a person may belong to one or many table. how do i create a query that will add a column with a ';' delimited where the person belong to, the ID is unique per person. here's the example

--table1
PID  fName  tableMem
1    test   group1
2    test2  group1

--table2
PID  fName  tableMem
1    test   group2
3    test3  group2

--table3
PID  fName  tableMem
1    test   group3
3    test3  group3

Here's the output I wanted

--compiled table after union of all the 3 tables
PID  fname  tableMem 
1    test   group1;group2;group3
2    test2  group1
3    test3  group2;group3

Here's the query I built from reading here for the past 2 days.I'm using STUFF and partition because I need the row to be distinct and this query will run as view.

SELECT * 
FROM
    (SELECT 
         *,
         ROW_NUMBER() OVER(PARTITION BY PIP ORDER BY Fname) AS rownum
     FROM 
         (SELECT 
              *,
              STUFF((SELECT ';'+ di.tablemem
                     FROM DPI di  <<-- alias table from union
                     WHERE DPI.PID = di.PID
                     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') tablemem
          FROM
              (SELECT * 
               FROM
                   (--table1
                    SELECT 'group1' AS tableMem, * FROM table1
                    UNION ALL
                    --table2
                    SELECT 'group2' AS tableMem, * FROM table2
                    UNION ALL
                    --table3
                    SELECT 'group3' AS tableMem, * FROM table3) AS DPI <<--alias table name
              ) AS innertable
         ) AS distinctTable 
    ) AS outerTable
WHERE
    rownum = 1

what am I missing or what is wrong with the query. I'm guessing its because Im using a derived table name of the union sub select. is there any workaround?

Thank you in advance

Upvotes: 1

Views: 91

Answers (4)

gotqn
gotqn

Reputation: 43636

Try this:

DECLARE @table1 TABLE
(
    [PID] TINYINT
   ,[fname] VARCHAR(12)
   ,[tableMem] VARCHAR(12)
);

DECLARE @table2 TABLE
(
    [PID] TINYINT
   ,[fname] VARCHAR(12)
   ,[tableMem] VARCHAR(12)
);

DECLARE @table3 TABLE
(
    [PID] TINYINT
   ,[fname] VARCHAR(12)
   ,[tableMem] VARCHAR(12)
);

INSERT INTO @table1 ([PID], [fname], [tableMem])
VALUES (1, 'test', 'group1')
      ,(2, 'test2', 'group1');

INSERT INTO @table2 ([PID], [fname], [tableMem])
VALUES (1, 'test', 'group2')
      ,(3, 'test3  ', 'group2');

INSERT INTO @table3 ([PID], [fname], [tableMem])
VALUES (1, 'test', 'group3')
      ,(3, 'test3  ', 'group3');


WITH DataSource AS
(
    SELECT *
    FROM @table1
    UNION ALL
    SELECT *
    FROM @table2
    UNION ALL
    SELECT *
    FROM @table3
)
SELECT DISTINCT DS.[PID]
               ,DS.fname
               ,CSVvalue.[tableMem]
FROM DataSource DS
CROSS APPLY
(   
    SELECT STUFF
    (
        (
            SELECT ',' + DS1.[tableMem]
            FROM DataSource DS1
            WHERE DS.[PID] = DS1.[PID]
                AND DS.[fname] = DS1.[fname]
            ORDER BY DS1.[tableMem]
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)')
        ,1
        ,1
        ,''
    )
) CSVvalue ([tableMem]);

Upvotes: 0

Valerica
Valerica

Reputation: 1630

Here is a possible solution using CTE:

;with cte as (
    select * from @tbl1
    union all
    select * from @tbl2
    union all
    select * from @tbl3
)
select distinct
    pid
    ,fname
    ,stuff(
        (select '; ' + tableMem
        from cte
        where pid = a.pid
            and fname = a.fname
        FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'') as tableMem 
from cte a

Upvotes: 1

Serg
Serg

Reputation: 22811

You need GROUP BY data by PID, fName, using the FOR XML aggregation it would be

WITH DPI AS (
    --table1
    Select 'group1' as tableMem,* from table1

    UNION ALL

    --table2
    Select 'group2' as tableMem,* from table2

    UNION ALL

    --table3
    Select 'group3' as tableMem,* from table3
)
SELECT PID, fName
    , STUFF((
            SELECT ';'+ di.tablemem
            FROM DPI di  
            WHERE di.PID = di1.PID
            FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') tablemem
FROM DPI di1
GROUP BY PID, fName;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Given that you have a finite number of tables, I think I find this simpler:

select p.pid, p.fname,
       trim(';' from
             (case when t1.pid is not null then 'group1;' else '' end) +
             (case when t2.pid is not null then 'group2;' else '' end) +
             (case when t3.pid is not null then 'group3;' else '' end)
           ) as groups
from (select pid, fname from table1 union  -- on purpose to remove duplicates
      select pid, fname from table2 union
      select pid, fname from table3
     ) p left join
     table1 t1
     on t1.pid = p.pid left join
     table2 t2
     on t2.pid = p.pid left join
     table3 t3
     on t3.pid = p.pid;

The most recent version of SQL Server supports string_agg(), which would make this simpler still.

Upvotes: 0

Related Questions