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
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 |
Why FLOAD does not support multiset tables??
What is the purpose of indexes?
Highlight the need for Performance Tuning.
Hi frnds...can any one help me regarding this que.. We have column in a table name it as C1 WHICH CONTAIN ALPHANUMERIC AND NUMERIC VALUES. C1 2A 2B 2V 2H 1 2 3 4 5 i want to retrive numeric values like 1,2,3,4,5 How we can get Plz let me know.Thanks in advance
Please tell me a query to find the Primary key,Foreign key,primary Index,PPI for the Database?
What is spool space?
What are the different table types that are supported by teradata?
Does SDLC changes when you use Teradata instead of Oracle?
if a error occured in FASTLOAD is the fastload job stops?
Name the five phases that come under MultiLoad Utility.
What's the syntax of sub string?
There is a load to the Table every one hour and 24/7.Morning trafic is high, afternoon trafiic is less, Night trafiic is high.According to this situation which Utility you use and how do you load,which utility used?