Hello Frndz, I have a table named product as shown below: product_id product

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 / vaishnavi.ayyadurai

4 Answers
12486 Views
Cap Gemini, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Hello Frndz, I have a table named product as shown below: product_id product_name 1 AAA 1 BBB ..

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

Hello Frndz, I have a table named product as shown below: product_id product_name 1 AAA 1 BBB ..

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

Hello Frndz, I have a table named product as shown below: product_id product_name 1 AAA 1 BBB ..

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

Hello Frndz, I have a table named product as shown below: product_id product_name 1 AAA 1 BBB ..

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

Post New Answer