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
- 4 Answers
- 11072 Views
- Cap Gemini, I also Faced
- E-Mail Answers
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
What are the uses of bynets in multi-node systems?
Explain the term 'foreign key' related to relational database management system?
What is called partitioned primary index (ppi) and discuss the advantages of using it in a query?
What are the available primary index types in teradata.
Why are oltp database designs not generally a good idea for a data warehouse?
What are the functions involved in shared information architecture?
Differentiate primary key and partition key?
if collect stats but it show low confidence why?
How do you verify a complicated sql?
What is the difference between global temporary tables and volatile temporary tables?
What tools would you use for that?
Explain fallback in teradata?
Explain teradata vs. Redshift?
Hi send me the Teradata dumps to my id rajeshanantha@yahoo.co.in Thanks Rajesh. A
In Teradata, how do we Generate Sequence?
