How can I display each value in a new column in SQL-CodePudding

I have the following query:

(SELECT 
  art_nr
, MENG_STK as S01
FROM NW_ART@MMS_HK WHERE BESTAND_L = 1 AND HIER_FD in (8701)
AND art_nr in (43848))

Union
(Select art_nr, MENG_STK as S02 
FROM NW_ART@MMS_HK WHERE BESTAND_L = 1 AND HIER_FD in (8702)
AND art_nr in (43848))

Union
(Select art_nr, MENG_STK as S03  
FROM NW_ART@MMS_HK WHERE BESTAND_L = 1 AND HIER_FD in (8703)
AND art_nr in (43848));


The outcome is the following :

  ART_NR    S01
 --------   ---- 
1|43848     742
2|43848     1357
3|43848     2172


I want to turn it into this: 

   ART_NR    S01   S02   S03   
   --------  ----  ----  ----
1| 43848     742   1357  2172

I have 0 clue how to join this into 1 row with 3 columns, I have tried:

Select S01, S02, S03..... but it shows me an error.

Is there anyone who could please help me out?

CodePudding user response：

You don't need union for this.

SELECT 
  art_nr
, max(case when BESTAND_L = 1 AND HIER_FD in (8701) then MENG_STK end) as S01
, max(case when BESTAND_L = 1 AND HIER_FD in (8702) then MENG_STK end) as S02
, max(case when BESTAND_L = 1 AND HIER_FD in (8703) then MENG_STK end) as S03

FROM NW_ART@MMS_HK WHERE art_nr in (43848))
Group By art_nr;