how to use concat, like and subconsultas with where?-CodePudding

SELECT asset_tag.asset_id, LEFT(asset_tag,SUBSTRING(asset_tag)-1 AS 'ETIQ' 
  
from    (SELECT DISTINCT S2.asset_id   ',' AS etiquetas          
(SELECT S1.tag_id   

            FROM asset_tag AS S1
            WHERE S1.tag_id
            ORDER BY S1.tag_id
            FOR XML PATH (''),TYPE

).VALUE('TEXT(1)`[1]','ninteger(MAX')[aset_tag]      FROM asset_tag AS S2   ) asset_tag;

enter image description here

I have to group by asset and the asset 1 have in one column 1,2,3,4,5 or the tag that it have

how to use heidisql functions, on dbforge? I know but here not I use heidisql version 12. and is my first time working with this

The objective is that the source table that has two columns, group by column 1 and that a new column indicate separated by commas what column 1 has in column 2 (of origin).

columna 1 - 1   1   2  2  3  3 4  4

columna 2 - a   b   c  a   d  a  f  g

and in a new column or table 1 - a b / 2 - b c

I see this answer on this page:

Here's a basic example of how SUBSTRING() works:

SELECT SUBSTRING(name, 2, 3) FROM mytable;

CodePudding user response：

How to concatenate rows of the same column in PostgreSQL?

Given that you want to concatenate rows of the same column, and not different columns (which is what you do when using CONCAT_WS()), what you would really be looking for is to use the ARRAY_AGG aggregation function within the ARRAY_TO_STRING function.

Documentation: https://www.postgresql.org/docs/13/functions-array.html

solution:

SELECT a.asset_id, ARRAY_TO_STRING(ARRAY_AGG(a.tag_id), ',') AS etiqueta

FROM public.asset_tag AS a

GROUP BY a.asset_id;

Result:

on asset_id 1 | 1,3,5 tag_id on asset_id 6 | 1,2 tag_id

If you insert this:

CREATE TABLE asset_tag ( asset_id INT,tag_id INT);

INSERT INTO asset_tag VALUES (1,1);

INSERT INTO asset_tag VALUES (1,3);

INSERT INTO asset_tag VALUES (1,5);

INSERT INTO asset_tag VALUES (6,1);

INSERT INTO asset_tag VALUES (6,2);

thanks to the person who gave me this answer .