CODEWITHSUNDEEP
sqlconcatenationsnowflake-cloud-data-platform
John Thomas
John Thomas

Reputation: 1105

How to concatenate two strings in different rows in SQL (Snowflake)?

So I have data as follows in Snowflake:

   Question       Answer    ID
First name?         John   001
 Last name?        Smith   001
First name?         Avon   002
 Last name?    Barksdale   002
First name?     Stringer   003
 Last name?         Bell   003

Is there anyway to CONCATENATE these to the following?

ID       Full_Name
001     John Smith
002 Avon Barksdale
003  Stringer Bell

Upvotes: 1

Views: 1060

Answers (2)

Mike Walton
Mike Walton

Reputation: 7339

You could use a listagg for this:

SELECT listagg(answer,' ') WITHIN GROUP (ORDER BY question)
FROM x
GROUP BY id;

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use conditional aggregation:

select id,
       concat_ws(' ',
                 (case when question = 'First name?' then answer end),
                 (case when question = 'Last name?' then answer end)
                ) as fullname
from t
group by id

Upvotes: 0

Related Questions