how to use concat, like and subconsultas with where?

Question

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: https://stackoverflow.com/a/545672/20100117 But i don´t know what mean "st1" or [text()] the alias?

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students" FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

score 0 · Answer 1 · answered Sep 28 '22 at 08:27

0

If you use HeidiSQL, you can use the completion proposal to help finding the right syntax for the various functions. Just type some first characters and press Ctrl+Space:

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

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

answered Sep 28 '22 at 08:27

Anse

1,573
12
27

Thank you substring I know how it works what I don't know is to apply it in my case – A izag alba Sep 28 '22 at 09:30

score 0 · Accepted Answer · answered Sep 30 '22 at 07:32

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 .

how to use concat, like and subconsultas with where?

2 Answers2