6
I created a table for example:
I have the following content:
select * from table_t;
And I need to get the following result:
In Mysql I use the following query:
select group_concat(concat_ws(' - ',id,descri)) id_descri, tipo
from table_t
group by tipo;
What about Sqlserver? Thank you!
5
Only to complement the response for future research (how I needed to), from the 2017 version of Sqlserver was implemented the function STRING_AGG(), which has the same characteristic as group_concat.
In your query would look like this:
select STRING_AGG(CONCAT(id, ' - ', descri), ',') id_descri, tipo
from table_t
group by tipo;
ref: https://docs.microsoft.com/pt-br/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
2
SELECT tipo, STUFF((SELECT ', ' + CONVERT(VARCHAR, tipo) + ' - ' + descri
FROM table_t iT
WHERE iT.tipo = T.tipo
ORDER BY tipo
FOR XML PATH, TYPE).value('.[1]', 'NVARCHAR(max)'), 1, 2, '') AS id_descri
FROM table_t T
GROUP BY tipo
or more concise
SELECT tipo, STUFF((SELECT ', ' + CONVERT(VARCHAR, tipo) + ' - ' + descri
FROM table_t iT
WHERE iT.tipo = T.tipo
ORDER BY tipo
FOR XML PATH('')), 1, 2, '') AS id_descri
FROM table_t T
GROUP BY tipo
I was curious about this answer because I wanted a style function group_concat()in Sql-Server. I could explain your reply @Bruno?
Browser other questions tagged sql-server query group-concat
You are not signed in. Login or sign up in order to post.
Wouldn’t it be possible to do that on the app? For example, select the types, then loop the result and for each type select the descriptions and ids! I did this way with group_concat, but I’ve had problems of slowness with a very large table and maintenance was also more complicated.
– Marcelo Diniz
Yes, it would be possible, as I know the feature in Mysql and for the sake of performance I prefer it to be in db itself.
– marcelo_drummer
I understood, but as for the question of performance, when I did this and had a lot of lines, the performance was much higher in the application itself, but then you should know how much to your side ai.
– Marcelo Diniz