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: 246695 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
?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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