Show column with information in new column-CodePudding

I have a table here in this example with 2 order numbers under sub-orders. Now I need the info in the user_c_56 column in the sub-orders as well.

The aunr column is assigned to the order number. Here is the original table and the way I would like it to be.

Here my query:

SELECT b.order_nr,
       b.user_c_56,
       b.aunr,
       s.a_atufe,
       z.mehrfach_kz,
       b.soll_dauer
FROM [hydra1].[hydadm].[v_auftrags_zusatz] z
     JOIN [hydra1].[hydadm].[auftrags_bestand] b ON z.auftrag_nr = b.aunr
     JOIN [hydra1].[hydadm].[auftrag_status] s ON b.auftrag_nr = s.auftrag_nr
WHERE s.eingeplant = ('M');

CodePudding user response：

If I understand the issue correctly, an approach based on windowed function (MAX in the example) is a possible option:

SELECT 
   b.order_nr,
   b.user_c_56,
   b.aunr,
   s.a_atufe,
   z.mehrfach_kz,
   b.soll_dauer,
   MAX(CASE WHEN b.order_nr = b.aunr THEN b.user_c_56 END) 
      OVER (PARTITION BY b.aunr) AS user_c_56
FROM [hydra1].[hydadm].[v_auftrags_zusatz] z
JOIN [hydra1].[hydadm].[auftrags_bestand] b ON z.auftrag_nr = b.aunr
JOIN [hydra1].[hydadm].[auftrag_status] s ON b.auftrag_nr = s.auftrag_nr
WHERE s.eingeplant = ('M')