Result SQL statement

0

Hello! I have a table of a questionnaire, I would like to have a result grouped by "property", doing 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 | corn Home Follows sql from table
property | question | answers
    10 | 1 | rice     10 | 1 | beans
    10 | 2 | rice     10 | 2 | beans
    10 | 2 | corn
    11 | 1 | corn

    
asked by anonymous 02.03.2018 / 21:22

1 answer

0

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

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

length: An integer that specifies the number of characters to exclude. If length is longer than the first character_expression, the deletion will occur until the last character in the last character_expression. length can be of the 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
    
03.03.2018 / 19:03