-2

I need your support to put together a query that allows me to ungroup a column with multiple values in a single line in this case the column CONDICIONES:

I have the CONDICIONES table with the columns Hora TRX; Fecha TRX; Condiciones; Código Cliente.

In my Teradata query, I am making it look for the conditions:

WHERE CONDICIONES LIKE ANY ('%0800%', '%0801%', '%0805%', '%0806%', '%0821%'|;

but the ungrouped information does not throw me away.

What I need is the query to return the following result:

Desired output:

RESULT

Table:

THIS IS THE TABLE

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Micky Stone
  • 103
  • 7
  • 1
    Normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes it is.). – sticky bit May 17 '21 at 23:56
  • 1
    And don't post images of tables. Post DDL and DML as **text**. – sticky bit May 17 '21 at 23:57
  • @Gordon linoff, the problem is that the database administrator agreed to have the information stored like this. And now we need to extract each condition value to know when customers have each one. We want to make a timeline for days to know what the impact of each one is. Please could you help me???? – Micky Stone May 18 '21 at 01:07
  • This is a simple task for `strtok_split_to_table`, but its syntax is a bit tricky. Can you provide more details? What's the delimiter between values, `'»'` and the datatype of `Fecha TRX`? – dnoeth May 18 '21 at 12:14
  • @ dnoeth, thanks for your answer. the delimiter is "?" and the datatype is Text – Micky Stone May 18 '21 at 15:51

1 Answers1

1

The easiest way is probably to split all values into rows before filtering/aggregationg:

WITH cte AS
 ( -- prepare the data
   SELECT "Fecha TRX"  AS pk, CONDICIONES AS delim_str
   FROM vt
 )
SELECT "Fecha TRX", CONDICIONES, Count(*)
FROM TABLE (StrTok_Split_To_Table(cte.pk, cte.delim_str, '?')
            RETURNS ("Fecha TRX" VARCHAR(20) -- should be the same datatype as input "Fecha TRX" 
                    ,tokennum INTEGER
                    ,CONDICIONES VARCHAR(10) )
           ) AS dt 
WHERE CONDICIONES IN ('0800', '0801', '0805', '0806', '0821'); 
GROUP BY 1,2;
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • thanks in advance, but 1 QUESTION. In this What does "token" refer to?.... Thanks........ I'm asking because TERADATA it returns the following message "Executed as Single statement. Failed [5628 : HY000] Column token not found in dt. Elapsed time = 00:00:00.015 " – Micky Stone May 18 '21 at 23:25