Result SQL statement

Asked

Viewed 42 times

0

Hello! I have a table of a questionnaire, I would like to have a result grouped by "property", making a GROUP_CONCAT in the "answers", according to the model below:

property | question | answers
10 | 1 | rice, beans
10 | 2 | rice, beans, corn
11 | 1 | maize

Follows the sql of the table
property | question | answers
10 | 1 | rice
10 | 1 | beans
10 | 2 | rice
10 | 2 | beans
10 | 2 | maize
11 | 1 | maize

sql group-concat

  • How is your query? You have tried and failed?

  • I tried, I used examples but it didn’t work... I used GROUP_CONCAT(DISTINCT field)

  • 1

    But what’s the problem? Is there an error? What is the full SQL you tried?

  • This syntax changes according to the BD.

  • SELECT property, question, GROUP_CONCAT(DISTINCT answer) FROM questionario GROUP BY property

1 answer

1

You can use the STUFF function of SQL Server: The STUFF function inserts a string into another character string. It excludes a specified length of characters from the first string at the initial position and then inserts the second string at the first position at the initial position. Parameters: character_expression: It is a character data expression. character_expression can be a constant, variable or column of characters or binary data.

start: It is an integer value that specifies the location to start the deletion and insertion. If start or length is negative, a null string will be returned. If start is longer than the first character_expression, a null string will be returned. start can be bigint type.

length: An integer specifies the number of characters to be deleted. If length is longer than the first character_expression, the exclusion will occur until the last character in the last character_expression. length can be bigint type.

Syntax Function STUFF: STUFF ( character_expression , start , length ,character_expression )

In your case it would look something like this:

with tab as
(
select 10 as id, 1 as cod, 'arroz' as descricao
union
select 10 as id, 1 as cod, 'feijão' as descricao
union
select 10 as id, 2 as cod, 'arroz' as descricao
union
select 10 as id, 2 as cod, 'feijão' as descricao
union
select 10 as id, 2 as cod, 'milho' as descricao
union
select 11 as id, 1 as cod, 'milho' as descricao
)
SELECT T1.id, T1.cod, STUFF((SELECT ', ' + CONVERT(VARCHAR, T2.descricao)
                             FROM tab T2
                             WHERE T1.id = T2.id and t1.cod = T2.cod
                             ORDER BY T1.id
                             FOR XML PATH('')), 1, 2, '') AS id_descri
FROM tab T1
GROUP BY T1.id, T1.cod
order by T1.id, T1.cod

Browser other questions tagged sql group-concat

You are not signed in. Login or sign up in order to post.