SQL add all values of a group as a new value-CodePudding

I would like to create from this table:

id	att_1
2	G
2	R
1	A
1	B
1	C
3	F

Something like that: (there's also a timestamp column, att_2 column should be organized based on the timestamp, earlier on the left side)

id	att_1	att_2
2	G	G,R
2	R	G,R
1	A	A,B,C
1	B	A,B,C
1	C	A,B,C
3	F	F

CodePudding user response：

You can use windows function and make simple query like below:

Demo

select
  id,
  att_1,
  string_agg(att_1, ',') over (
    partition by id
  ) as att_2
from  test1

For update statement:

update test2 t2
set att_2 = t.att_2
from (
  select
    id,
    att_1,
    string_agg(att_1, ',') over (
      partition by id
    ) as att_2
  from test1
) t
where t.id = t2.id

CodePudding user response：

string_Agg I believe is the fucntion you're looking for. Some systems use List_Agg Just depends on RDMBS... This appears to be Postgresql so I think it's string_agg.

string_agg(Value, deliniator, order by [optional])

SELECT t1.ID, t1.Att_1, t2.Att_2 
FROM tblName t1
INNER JOIN (SELECT ID, STRING_AGG (att_1,',' Order by Att_2) att_2 
            FROM tblName
            GROUP BY ID) t2
 on t2.ID = t1.ID

--BUILDING ON THE ABOVE and assuming this is a 1 and done thing...

WITH CTE AS (
    SELECT t1.ID, t1.Att_1, t2.Att_2 
    FROM tblName t1
    INNER JOIN (SELECT ID, STRING_AGG (att_1,',' Order by Att_2) att_2 
                FROM tblName
                GROUP BY ID) t2
     on t2.ID = t1.ID)

UPDATE tblName ut1
SET ut1.att_2 = CTE.Att_2
FROM CTE
WHERE ut1.ID = CTE.ID;

-- if not one and done....

Then consider using a trigger, function based index, or computed/generated column. While I'm confident a trigger would work; I'm unsure if a function based index or computed/generated column would as an analytic function is needed and it would need to process multiple rows.