CODEWITHSUNDEEP
sqldatabasesnowflake-cloud-data-platform
Razan Aldossary
Razan Aldossary

Reputation: 259

Same column with different values into single output

Greeting :)

It might be a simple snowflake problem but I'm struggling with this..

I want to gather different values in the same column into a single output. To give you more context, I'm working in a customer table and that customer registered with 2 mobile number so that give me the same customer twice in the output but each record with a different phone number (and those numbers in the same column in our database) so, I want to get only one record and gather those two numbers in the same column.

I'm doing analytics, so just reading from the database I'm not changing anything here.

Thank you in advance :)

---- For more clarification: I cannot use listagg() because the numbers are stored in the same column and my goal is to gather them in single output :)

Upvotes: 0

Views: 1468

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25928

so stripping this down, to I have a table with customer_id and phone

like so:

SELECT
    customer_id,
    phone
FROM VALUES 
   (123, '555-1234-999'),
   (123, '555-5555-999'),
   (678, '555-6162-999')
   v(customer_id, phone);

give you your many rows:

CUSTOMER_ID PHONE
123 555-1234-999
123 555-5555-999
678 555-6162-999

and we can be lazy (for me) and turn it into an array:

SELECT
    customer_id,
    ARRAY_AGG(phone) as phones
FROM VALUES 
   (123, '555-1234-999'),
   (123, '555-5555-999'),
   (678, '555-6162-999')
   v(customer_id, phone)
GROUP BY 1 
ORDER BY 1;

which gives:

CUSTOMER_ID PHONES
123 [ "555-1234-999", "555-5555-999" ]
678 [ "555-6162-999" ]

a slight step more might be to add a WITHIN GROUP clause to order those phones:

SELECT
    customer_id,
    ARRAY_AGG(phone) WITHIN GROUP (ORDER BY phone) as phones
FROM VALUES 
   (123, '555-1234-999'),
   (123, '555-5555-999'),
   (678, '555-6162-999')
   v(customer_id, phone)
GROUP BY 1 
ORDER BY 1;

you can order by a different column, here I added a fake account number, what luck:

SELECT
    customer_id,
    ARRAY_AGG(phone) WITHIN GROUP (ORDER BY account) as phones
FROM VALUES 
   (123, '555-1234-999',2),
   (123, '555-5555-999',1),
   (678, '555-6162-999',1)
   v(customer_id, phone, account)
GROUP BY 1 
ORDER BY 1;

or we can do a self LEFT JOIN knowing "there are only 2 phone per customer"

WITH data AS (
  SELECT *
  FROM VALUES 
   (123, '555-1234-999',2),
   (123, '555-5555-999',1),
   (678, '555-6162-999',1)
   v(customer_id, phone, account)
)
SELECT
    a.customer_id,
    a.phone,
    b.phone
FROM data AS a
LEFT JOIN data AS b 
    ON a.customer_id = b.customer_id and a.account = 1 and b.account = 2
ORDER BY 1;

which gives:

CUSTOMER_ID PHONE PHONE
123 555-1234-999
123 555-5555-999 555-1234-999
678 555-6162-999

Righto, so the multi-join was not wanted, good, because it's ugly.

Not sure why you cannot use LIST_AGG as you can wrap the tokens in a pattern and then delimiter it. But really ARRAY_AGG is nicer.

SELECT
    customer_id,
    LISTAGG('"'|| phone ||'"',',' ) as phones
FROM VALUES 
   (123, '555-1234-999'),
   (123, '555-5555-999'),
   (678, '555-6162-999')
   v(customer_id, phone)
GROUP BY 1 
ORDER BY 1;

gives:

CUSTOMER_ID PHONES
123 "555-1234-999","555-5555-999"
678 "555-6162-999"

Upvotes: 2

Related Questions