List of unique values instead of count teradata sql

Question

I have 2 columns; access_method-id, app_name Each user uses different apps from time to time and I need to write a query to get a list of all the apps that the user have used in one column. like this

acess_method_id  |  App_Name
12345            | [bima,gaming,tube]
34579            | [candy,bubbles,gaming,tube]

Data in the table on which I am running the query looks like this

acess_method_id  |  App_Name
    12345        | bima
    12345        | gaming
    12345        | tube
    34579        | candy
    34579        | bubbles
    34579        | gaming
    34579        | tube

I am using this query on Teradata from Dbeaver

Select COUNT(DISTINCT App_Name),ACCESS_METHOD_ID 
from DP_VEW.mytable as a 
GROUP BY ACCESS_METHOD_ID

this query gives me a count of the apps, I need to get the list. Using Teradata SQL is there a way to write a query to get the desired results?

your dbms name please – Zaynul Abadin Tuhin Feb 22 '22 at 11:34 — Zaynul Abadin Tuhin, Feb 22 '22 at 11:34
Since you specify Teradata I've removed the SQLServer TAG – Stu Feb 22 '22 at 11:38 — Stu, Feb 22 '22 at 11:38
@ZaynulAbadinTuhin using dBeaver – irum zahra Feb 22 '22 at 11:38 — irum zahra, Feb 22 '22 at 11:38
I've seen people use recursive cte's here. – jarlh Feb 22 '22 at 11:45 — jarlh, Feb 22 '22 at 11:45
What's your Teradata release? – dnoeth Feb 22 '22 at 15:04 — dnoeth, Feb 22 '22 at 15:04
What's your exact desired result based on your sample? – Andrew Feb 22 '22 at 15:20 — Andrew, Feb 22 '22 at 15:20
@Andrew please see the edits – irum zahra Feb 23 '22 at 09:44 — irum zahra, Feb 23 '22 at 09:44

score 0 · Answer 1 · answered Feb 23 '22 at 13:20

0

use XMLAGG()

SELECT acess_method_id,
XMLAGG(App_Name || ',' order by App_Name)
FROM your_table
GROUP BY acess_method_id;

answered Feb 23 '22 at 13:20

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 2 · answered Feb 23 '22 at 15:27

What you want is similar to Standard SQL's ListAgg function, which is not implemented in Teradata.

But nPath can return this result, too. You have to get used to the syntax, but it's way more efficient than XMLAGG.

SELECT * 
FROM 
   NPath
    (
      ON (
           SELECT DISTINCT acess_method_id, App_Name
           FROM mytable
         ) AS dt                           -- input data
      PARTITION BY acess_method_id         -- group by column(s)
      ORDER BY App_Name                    -- order within list
      USING
        MODE (NonOverlapping)              -- required syntax 
        Symbols (True AS T)                -- every row
        Pattern ('T*')                     -- is returned
        RESULT(First (acess_method_id OF T) AS acess_method_id, -- group by column
               Count (App_Name OF T) AS Cnt,
               Accumulate(App_Name OF ANY (T) Delimiter ',') AS ListAgg
              )
    );

You can also move the distinct into the RESULT functions like this:

SELECT * 
FROM 
   NPath
    (
      ON (
           SELECT acess_method_id,  App_Name
           FROM mytable
         ) AS dt                           -- input data
      PARTITION BY acess_method_id         -- group by column(s)
      ORDER BY App_Name                    -- order within list
      USING
        MODE (NonOverlapping)              -- required syntax 
        Symbols (True AS T)                -- every row
        Pattern ('T*')                     -- is returned
        RESULT(First (acess_method_id OF T) AS acess_method_id, -- group by column
               Count (DISTINCT App_Name OF T) AS Cnt,
               Accumulate(DISTINCT App_Name OF ANY (T) Delimiter ',') AS ListAgg
              )
    );

Check which version is more efficient.

List of unique values instead of count teradata sql

2 Answers2

Linked