How to get this result?

Question

How to get this result?

Charmer

SSChampion

Points: 14903
More actions
January 12, 2018 at 7:18 am

#341651

SELECT                                             '725518-021' AS MNbr                                          ,12 AS CO_ID                                          ,13 AS CU_ID                                          ,'2017-10-01' AS PRICEDATE                                          ,23.91 AS WHOLESALE                                          ,NULL AS RETAIL                                         UNION ALL                                         SELECT                                             '725518-021'                                          ,12                                          ,13                                          ,'2017-10-01'                                          ,NULL                                          ,55
                                        -- Expecting Result
                                        SELECT                                             '725518-021' AS MNbr                                          ,12 AS CO_ID                                          ,13 AS CU_ID                                          ,'2017-10-01' AS PRICEDATE                                          ,23.91 AS WHOLESALE                                          ,55 AS RETAIL
Hi Friends,
I am thinking to use use self join and ISNULL in the SELECT list to achieve this output? Is there any other efficient way?
Provide me your suggestions pls.
Thanks,
Charmer

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

Charmer - Friday, January 12, 2018 7:18 AM
SELECT '725518-021' AS MNbr ,12 AS CO_ID ,13 AS CU_ID ,'2017-10-01' AS PRICEDATE ,23.91 AS WHOLESALE ,NULL AS RETAIL UNION ALL SELECT '725518-021' ,12 ,13 ,'2017-10-01' ,NULL ,55 -- Expecting Result
SELECT '725518-021' AS MNbr ,12 AS CO_ID ,13 AS CU_ID ,'2017-10-01' AS PRICEDATE ,23.91 AS WHOLESALE ,55 AS RETAIL
Hi Friends,
I am thinking to use use self join and ISNULL in the SELECT list to achieve this output? Is there any other efficient way?
Provide me your suggestions pls.

Have you tried using MAX and GROUP BY?
SELECT MNbr, Wholesale = Max(Wholesale) etc
GROUP BY MNbr
?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Charmer SSChampion Points: 14903 More actions · Answer 2

Phil Parkin - Friday, January 12, 2018 7:52 AM
Charmer - Friday, January 12, 2018 7:18 AM
SELECT '725518-021' AS MNbr ,12 AS CO_ID ,13 AS CU_ID ,'2017-10-01' AS PRICEDATE ,23.91 AS WHOLESALE ,NULL AS RETAIL UNION ALL SELECT '725518-021' ,12 ,13 ,'2017-10-01' ,NULL ,55 -- Expecting Result
SELECT '725518-021' AS MNbr ,12 AS CO_ID ,13 AS CU_ID ,'2017-10-01' AS PRICEDATE ,23.91 AS WHOLESALE ,55 AS RETAIL
Hi Friends,
I am thinking to use use self join and ISNULL in the SELECT list to achieve this output? Is there any other efficient way?
Provide me your suggestions pls.
Have you tried using MAX and GROUP BY?
SELECT MNbr, Wholesale = Max(Wholesale) etc
GROUP BY MNbr
?

Yes, It worked. Much efficient.

Thank you very much Phil.

Thanks,
Charmer