How to get 2 value from same id and same column?

Database
Fan

How to get 2 value from same id and same column?

I have this data :

ID - Customer - Book - Price

021 - ABC - Book 1 - 120,00

021 - ABC - Book 2 - 150,00

022 - XYZ - Book 3 - 100,00

021 - ABC - Book 2 - 150,00

I want to show it like :

ID - Customer - Book - Total Price

021 - ABC - Book 1, Book 2 - 420,00

022 - XYZ - Book 3 - 100,00

Please, i need your help.

Thanks, :)

5 REPLIES
Enthusiast

Re: How to get 2 value from same id and same column?

this will do all except suming up price which will need to be joined to resultset

CREATE TABLE har2 AS
(
SEL id,customer, book, price, ROW_NUMBER () OVER (PARTITION BY id,customer ORDER BY book ) AS rnk FROM DLWORK_DB01_DA.har )
WITH DATA ;

WITH RECURSIVE base (c_rnk, C_id,  c_customer,c_list) AS(
SELECT rnk c_rnk,  id AS c_id, customer AS c_customer , CAST(TRIM(book) AS VARCHAR(6000)) c_list
FROM  har2
WHERE rnk = 1  
UNION
 ALL 
SELECT rnk c_rnk, id, customer ,b.c_list || ',' || CAST(TRIM(c.book) AS VARCHAR(6000)) c_list
FROM har2 c INNER JOIN base b
 ON b.c_rnk + 1 = c.rnk 
 AND c.id=b.c_id )  
SELECT  c_id, c_customer, c_list
FROM base
QUALIFY RANK() OVER (PARTITION BY c_id
ORDER BY c_rnk DESC) = 1
Fan

Re: How to get 2 value from same id and same column?

Thanks for your kind respond. :)

My data is complicated, so actually i have query like this before, refer to some solution i found in this forum.

It's run but run so long and doesn't give me any result.

WITH RECURSIVE SEGMENT_LIST( CUST_NO, LOB_ID, DESCRIPTION, LVL)
AS
(
SELECT a.HOST_CUST_NO, c.SUB_LOB_ID, MIN(c.SUB_LOB_DESC) (VARCHAR(1000)), 1
FROM SIT03_EDW_ACL_SEM_VR.DIM_HOST_CUST a
JOIN SIT03_EDW_ACL_SEM_VR.FACT_LN_MTRC_MTHLY_MSTR b ON a.BUSN_DT = b.BUSN_DT AND a.HOST_CUST_ID = b.HOST_CUST_ID
JOIN SIT03_EDW_ACL_SEM_VR.DIM_LOB c ON a.BUSN_DT = c.BUSN_DT
GROUP BY 1,2
UNION ALL
SELECT a.HOST_CUST_NO, c.SUB_LOB_ID, c.SUB_LOB_DESC || ',' || DESCRIPTION, LVL+1
FROM SIT03_EDW_ACL_SEM_VR.DIM_HOST_CUST a
JOIN SIT03_EDW_ACL_SEM_VR.FACT_LN_MTRC_MTHLY_MSTR b ON a.BUSN_DT = b.BUSN_DT AND a.HOST_CUST_ID = b.HOST_CUST_ID
JOIN SIT03_EDW_ACL_SEM_VR.DIM_LOB c ON a.BUSN_DT = c.BUSN_DT
INNER JOIN SEGMENT_LIST d ON a.HOST_CUST_NO = CUST_NO
AND c.SUB_LOB_ID = LOB_ID
AND c.SUB_LOB_DESC > DESCRIPTION
)

SELECT a.CUST_NO, a.DESCRIPTION
FROM SEGMENT_LIST a
JOIN SIT03_EDW_ACL_SEM_VR.DIM_HOST_CUST c ON a.CUST_NO = c.HOST_CUST_NO
JOIN SIT03_EDW_ACL_SEM_VR.DIM_LOB d ON a.LOB_ID = d.SUB_LOB_ID
WHERE c.BUSN_DT ='2012-10-31' AND HOST_CUST_NO in('0006158010')


I want to ask you, could i use join in WITH RECURSIVE, like i do in my query?

And is there another way to get the result without using WITH RECURSIVE? Because actually i want to display the data in Cognos not in Teradata, and Cognos doesn't know WITH RECURSIVE.

Thank you, i'm really new with this stuff.

Junior Contributor

Re: How to get 2 value from same id and same column?

For your example (up to two rows per ID) there's a simple solution:

SELECT ID, Customer, 
MIN(Book) || COALESCE(',' || MAX(Book), ''), SUM(Price)
FROM tab
GROUP BY 1, 2

But it's probably more complicated and i don't have a clue what you actually want.

Dieter

Fan

Re: How to get 2 value from same id and same column?

thank you for your kindness, but i try another way to solve it because Cognos can't execute with recursive.

:)

Junior Contributor

Re: How to get 2 value from same id and same column?

How many rows into one column?

Is there a know maximum?

A typical solution might involve aggregation using MIN(CASE) or OLAP.

Btw, if Cognos can't use recursion you can create a recursive view and tell Cognos to use it.

Dieter