Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

how to use concat, like and subconsultas with where?

Mandiizal's profile image Mandiizal posted 2 months ago in General Permalink

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

Mandiizal's profile image Mandiizal posted 2 months ago Permalink

is something like that

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]

but is my first time use heidisql and i dont`t know , only i worked with postgresql

Mandiizal's profile image Mandiizal posted 2 months ago Permalink

and i dont know how to use substring and others

ansgar's profile image ansgar posted 2 months ago Permalink

Basic example for using SUBSTRING in MySQL and MariaDB:

SELECT SUBSTRING(`name`, 3) FROM acl_role;

What's the server type to which you connect in HeidiSQL? Microsoft SQL Server probably?

Mandiizal's profile image Mandiizal posted 2 months ago Permalink

THANKS YOU !!

I'm using heidisql

1 attachment(s):
  • heidi
Mandiizal's profile image Mandiizal posted 2 months ago Permalink

microsoft sql server i think

ansgar's profile image ansgar posted 2 months ago Permalink

Yes, that looks like MS SQL.

You can use the completion proposal to help finding the right syntax for various functions. Just type some chars and press Ctrl + Space:

Description

Mandiizal's profile image Mandiizal posted 2 months ago Permalink

Thanks !!

and functions that do what "left or len" do

SELECT Main.SubjectID, LEFT(Main.Students,Len(Main.Students)-1) As "Students" FROM maiin

Mandiizal's profile image Mandiizal posted 2 months ago Permalink

AND OTHER THING, HOW ARE SUBQUERIES NAMED? WITH AS? AND HOW ARE THEY USED?

BECAUSE THIS NOT ?

SELECT asset_tag.asset_id , LEFT(asset_tag, LENGTH(asset_tag.tag_id)-1 AS etiqueta FROM ( SELECT ST2.asset_tag ( SELECT ST1.asset_tag + ',' AS [etiqueta(1)] FROM asset_tag ST1 WHERE asset_tag.tag_id = ST2.asset_tag.tag_id ORDER BY ST1.asset_tag.tag_id FOR XML PATH (''), TYPE

)value('etiqueta()[1]','nvarchar(max)') [asset_tag] ) from asset_tag ST2 )asset_tag ;

Mandiizal's profile image Mandiizal posted 2 months ago Permalink

i want taht the group "6" have "5,1" , el "1" have "1,3,5" , what i`m doing wrong?

1 attachment(s):
  • concat-29-sept
ansgar's profile image ansgar posted 2 months ago Permalink

Ok, then you should not group by tag_id. Remove the tag_id from the GROUP BY clause, and replace that concat_ws(...) by just asset_tag.tag_id:

SELECT asset_tag.asset_id, asset_tag.tag_id
FROM public.asset_tag
JOIN ...
WHERE ...
GROUP BY asset_tag.asset_id;
Mandiizal's profile image Mandiizal posted 2 months ago Permalink

Yes , that error i see but this

1 attachment(s):
  • concat-29-sept.2

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.