не могу объединить в одну таблицу sql managment studio

Question

есть скрипт

        SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY bill_id ASC) AS rownumber,
   b.bill_id as bill_id1  FROM Bills b
    WHERE b.pay_id = 6179 and b.advtype = 1 
        and b.rperiod_beg =convert(datetime, '2020-12-01 00:00:00.000',102)
) AS foo
WHERE rownumber = 1

        SELECT * FROM (
  SELECT
 ROW_NUMBER() OVER (ORDER BY bill_id ASC) AS rownumber,
   b.bill_id as bill_id2  FROM Bills b
    WHERE b.pay_id = 6179 and b.advtype = 1 
        and b.rperiod_beg =convert(datetime, '2020-12-01 00:00:00.000',102)
) AS foo
WHERE rownumber = 2

        SELECT * FROM (
  SELECT
 ROW_NUMBER() OVER (ORDER BY bill_id ASC) AS rownumber,
   b.bill_id as bill_id3  FROM Bills b
    WHERE b.pay_id = 6179 and b.advtype = 1 
        and b.rperiod_beg =convert(datetime, '2020-12-01 00:00:00.000',102)
) AS foo
WHERE rownumber = 3

он выдает такой результат

Как сделать так, чтобы результат выводился так:

чтобы я мог потом во временную таблицу записать

Answer 1

если сделать именно то, что Вы просите, и используя Ваш запрос, то вот -

SELECT  b1.bill_id1, b2.bill_id2, b3.bill_id3
FROM (SELECT bill_id1 FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY bill_id ASC) AS rownumber,
   b.bill_id as bill_id1  FROM Bills b
    WHERE b.pay_id = 6179 and b.advtype = 1 
        and b.rperiod_beg =convert(datetime, '2020-12-01 00:00:00.000',102)
) AS foo
WHERE rownumber = 1) b1 left join
(        SELECT bill_id2 FROM (
  SELECT
 ROW_NUMBER() OVER (ORDER BY bill_id ASC) AS rownumber,
   b.bill_id as bill_id2  FROM Bills b
    WHERE b.pay_id = 6179 and b.advtype = 1 
        and b.rperiod_beg =convert(datetime, '2020-12-01 00:00:00.000',102)
) AS foo
WHERE rownumber = 2) b2 ON 1 = 1
LEFT JOIN
        (SELECT * FROM (
  SELECT
 ROW_NUMBER() OVER (ORDER BY bill_id ASC) AS rownumber,
   b.bill_id as bill_id3  FROM Bills b
    WHERE b.pay_id = 6179 and b.advtype = 1 
        and b.rperiod_beg =convert(datetime, '2020-12-01 00:00:00.000',102)
) AS foo
WHERE rownumber = 3) b3 ON 1=1

но это выглядит странно, даж немного страшно :)

БЛОГ НА HUSL

не могу объединить в одну таблицу sql managment studio

Ответы (1 шт):