1. Categories >> Software >> Data Warehouse >> Teradata
  2. Suggest New Category

Hello Frndz,
I have a table named product as shown below:
product_id product_name
1 AAA
1 BBB
1 CCC
2 PPP
2 QQQ
2 RRR

Now my output should be:
product_id product_name_1 product_name_2 product_name_3
1 AAA BBB CCC
2 PPP QQQ RRR

Question Posted / tdguy

Answer Posted / tdguy

Hi,
Below query can be used assuming that there would not be
duplicates of product_names in the parent table and only
three product_names would be the output required.

SEL PRODUCT_ID,MIN(PRODUCT_NAME_1) AS PROD1,MIN
(PRODUCT_NAME_2) AS PROD2,MIN(PRODUCT_NAME_3) AS PROD3
FROM
(SEL PRODUCT_ID,PRODUCT_NAME,
ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID ORDER BY
PRODUCT_NAME ASC) AS PRODRANK,
CASE WHEN PRODRANK=1
THEN PRODUCT_NAME END AS PRODUCT_NAME_1,
CASE WHEN PRODRANK=2
THEN PRODUCT_NAME END AS PRODUCT_NAME_2,
CASE WHEN PRODRANK=3
THEN PRODUCT_NAME END AS PRODUCT_NAME_3
FROM PRODUCT) A
GROUP BY 1;


If the above case is numeric, SUM should be used instead of
MIN.

Is This Answer Correct ?    7 Yes 0 No



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

Can you fastexport a field, which is primary key by putting equality on that key?

711

What are the various etl tools in the market?

587

What is stored procedure in teradata?

593

Name the five phases that come under MultiLoad Utility.

589

What is meant by a Parsing Engine?

710




Differentiate database data and data warehouse data?

589

how many modules are there in telecome domain?how to explain the architecture?

1561

How would you load a very large file in teradata in general?

569

Explain vproc in teradata?

566

Describe the set operators in teradata.

583

hi frnds i want to learn teradata utilities and teradata dba real time. i have 1+ years of experience in teradata. so i want to go deeply in Tearada. plz let me know at my email id who r best to learn from. im lookng for a realtime guy in HYD or Banglr. Thanks in advance. Rajesh my email-id: rajeshmss87@gmail.com

2357

What are the different table types supported by teradata?

537

What is bteq script in teradata?

636

How do you do backup and recovery in teradata?

553

What are differences between teradata and ansi session modes in teradata?

632