How to concatenate column values with matching column values in another column inside a case stateme-CodePudding

I would like to create hhID in my column based on values in NodeID. IF nodeID is empty, hhId should be a copy of UID, If not, then hhID should be a combination of all UIDs with the same values of nodeID.I have put dummy text here; 'Nepal is there'. However, for UID, 'JBGNARZ1', hhID should be JBGNARZ1, JRL0UUOI.

 SELECT UID,nodeID,
 CASE 
     WHEN  nodeID='' THEN UID
     else  'nepal is there'
 END AS hhID
 FROM housetable;

I have tried using following code, but it concatenates all UIDs with empty nodeID values in a single string.

SELECT n.UID,
GROUP_CONCAT(n.UID)  AS hhID
FROM housetable n
GROUP BY n.nodeID;

However, I need all UIDs intact in my first column. Could you please kindly help me?

CodePudding user response：

You can use group_concat to achieve this:

with your_table as (
  select 'A' AS UID, null as nodeID
  union select 'B' AS UID, 1 as nodeID
  union select 'C' AS UID, 2 as nodeID
  union select 'D' AS UID, 1 as nodeID
)
select 
  UID, 
  nodeID,
  case when nodeID is null then UID
  else (select group_concat(UID) 
        from your_table b 
        where b.nodeID = a.nodeID 
        group by nodeID)
  end as hhID
from your_table a

UID	nodeID	hhID
A		A
B	1	B,D
C	2	C
D	1	B,D

dbfiddle here

CodePudding user response：

Assuming nodeID is null when blank, this should get you what you need:

select case when isnull(nodeID) then housetable.UID else c.b end
from housetable
left join (
  select nodeID as a, group_concat(distinct UID separator ', ') as b
  from housetable
  group by a
) as c on c.a = housetable.nodeID and instr(c.b, housetable.UID)