I have this table Movie with columns defined as (CustomerId, MovieId, Name) and I want to fetch these columns by concatenating them into a string in the position they are defined in Snowflake, by ordering through ORDINAL_POSITION. So I found that using LISTAGG() get’s me pretty close to what I want. By …
Tag: listagg
Returning multiple identical rows in LISTAGG query
I’m using Oracle, and want to connect two tables into one select statement (for a report), and we’re required to concatenate some of the data from one table into a single row – for which I’m expecting to use LISTAGG. Simplified version of the problem: ACCOUNTS table: InitialDate Accoun…
LISTAGG in SQL is returning a row with null values
I have 2 tables A and B, B is having a foreign key relationship with A i.e.,(b.detail_id = a.id) I want to apply LISTAGG query on one of the column in B. The above query is returning me a row with all values as null, But I want no rows. How can I achieve this? If it’s not possible any
Is there a way to continue on the next row after listagg() runs out of space (>4k)?
I would like to change a table that has a number of values per week, to having all values in one row per week. But when I use listagg() I run out of space. I can’t change the maximum string size (ALTER SYSTEM SET max_string_size=extended SCOPE= SPFILE), and I don’t want to truncate the resulting s…
find only the duplicate values in listagg
I am having some duplicate rows in my table. I wanted to find those duplicates. After i have done listagg on the column rp_num my table will have duplicate value like this : SELECT distinct vt….
How to use listagg properly with case statements
I am trying to use listagg but I’m getting the wrong output. Normally, I would use each case statement separately, but then how would I use listagg? Table A: Table B: Desired Output: My attempted (wrong) code: Answer Here’s one option, which uses multiple self-joins. lines #1 – 14 represent …
merging start and end date cycle dates
I have the below table. CUST_ID START_CYCLE END_CYCLE WORKER CUST_SUB_ID CUST_SUB_TYPE 101 1/1/2019 1/31/2019 ABC123 134 HIGH_SUB 101 2/1/2019 4/30/2019 ABC123 136 …
How to use Replace/Substitution function in PL SQL
I’ve a doubt regarding replacing / substituting values in PLSQL. I’ve used listagg to segregate n number of values with each and every value being splitted by comma delimiter. For instance when executing the above query it returns 7digit alpha numeric values. For example ABCD123,EFGH456,IJKL789 Af…
Change WM_CONCAT function to LISTAGG
I used WM_CONCAT in my sql query. The database has been updated to version 12c and WM_CONCAT doesn`t work. Query had value: Select r.product_id, count (1) how_many, wm_concat (distinct r.number) …
Why I’m getting ORA-00979 while trying to use LISTAGG and LEFT JOIN?
I’m trying to group my results so, instead of looking like this: id | nome | bairro ——————— 1 . |Test 1 | bairro 1 1 . |Test 1 | bairro 2 2 . |Test 2 | bairro 3 It looks like this: …