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
- 11050 Views
- Cap Gemini, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / yuvaevergreen
Hi,
Since the no of output columns is a variable, I think we
may have to write a SP or macro to get the desired output.
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / vaishnavi a
Thanks for ur response, i just gave a sample table with few
records, but in a real time scenario we have a table with
duplicates & there can be many products under a particular
prod_id.It is not that there should be only 3 products
under a prod_id , it could be even 50. wat could be an
optimised way of writing a query for this scenario??
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / vishvajeet mistry
We can also use Full outer join in this case.
sel
t1.pid,
t1.pn as pn1,
t2.pn as pn2,
t3.pn as pn3
from
(sel pid,pn
from prod
qualify rank() over (partition by pid order by pn)=1)t1
Full outer join
(sel pid,pn
from prod
qualify rank() over (partition by pid order by pn)=2)t2
On
t1.pid=t2.pid
Full outer join
(
sel pid,pn
from prod
qualify rank() over (partition by pid order by pn)=3)t3
on
t1.pid=t3.pid
order by 1 ;
Hope it helps :)
| Is This Answer Correct ? | 0 Yes | 0 No |
Explain amp in teradata?
what is single value and Multivalue ?
If the table does not have duplicates then which utility you can suggest to load the data ?
How to identify ppi columns?
Does anyone got Latest TERADATA V12 Certification Dumps for the below? TE0-121 , TE0-122 , TE0-123 , TE0-124 , TE0-125 , TE0-126 TE0-127 , TE0-12Q
i have a table like sales....the field are Prodid Jan(jam month sales)Feb March apr may jun july 1 20 76 50 74 94 93 83 2 30 94 40 94 93 93 02 3 40 90 30 49 94 92 56 4 70 20 30 93 46 35 78 5 23 40 40 39 84 46 57 6 85 30 55 93 46 46 46 7 84 20 65 83 56 57 57 8 10 93 40 93 68 57 36 9 57 30 30 83 67 57 79 10 38 83 40 83 55 68 47 11 35 39 90 94 57 78 47 12 83 89 50 93 79 69 69 i wat ti find the max sales of every product which i want to load into target table
What are teradata utilities?
What is the maximum number of dml can be coded in a multiload script?
My table got locked during mload due to a failed job. What do I do to perform other operations on it?
I have a table with emp id, emp name, dept id and sal where dept id is NUSI. SEL * FROM EMP WHERE DEPTID = 100. Can any one explain how it will fetch the record.
What are the available primary index types?
Difference between Teradata V2R5 and Teradata V12 versions?
Teradata 
