Query issue

[rafeman]

I have the following SQL query that gives me the data I need. However, I need to transpose the rows into columns.

Ie.

Instead of using union to put them all together I need each query as a separate column. One row per order number.

select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery1
where RowNum = 1

--

(select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery2
where RowNum = 2)

--

(select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery3
where RowNum = 3)

--

(select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery4
where RowNum = 4)

--

(select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery5
where RowNum = 5)

--

(select * from
(
select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904'
) Delivery6
where RowNum = 6)

 

[gmmastros]

Since you have 6 queries, and each query returns 2 columns, I assume you want 12 columns in your output. Is this correct?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IanWaterman]

You can just link them together on order number

Select d1.dhh_order_no, d1.SYS_DEL_HISTORY_HEADER DEL1,d2.SYS_DEL_HISTORY_HEADER DEL2,......
from
(select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum',CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADERwhere DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904') D1
Inner Join
(select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum',CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADERwhere DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904') D2
on D1.dhh_order_no= D2.dhh_order_no
and D1.Rownum = 1and d2.rownum = 2
inner Join .....

Not sure why you are restricting row numbers this may not be required.

Ian

 

[rafeman]

Thanks guys, the follwoing gave me what I needed;

with MyData
as (select ROW_NUMBER() OVER(PARTITION BY DHH_ORDER_NO ORDER BY DHH_DELIVERYNOTE_NO DESC) AS 'RowNum'
,CAST(DHH_ORDER_NO AS float) AS DHH_ORDER_NO
, DHH_DELIVERYNOTE_NO from SYS_DEL_HISTORY_HEADER
where DHH_ORIGIN in ('SOO') and DHH_ORDER_NO = '7904')

select
DHH_ORDER_NO,
Row1_RowNum = max(case when RowNum = 1 then RowNum else 0 end),
Row1_DHH_DELIVERYNOTE_NO = max(case when RowNum = 1 then DHH_DELIVERYNOTE_NO else 0 end),
Row2_RowNum = max(case when RowNum = 2 then RowNum else 0 end),
Row2_DHH_DELIVERYNOTE_NO = max(case when RowNum = 2 then DHH_DELIVERYNOTE_NO else 0 end),
Row3_RowNum = max(case when RowNum = 3 then RowNum else 0 end),
Row3_DHH_DELIVERYNOTE_NO = max(case when RowNum = 3 then DHH_DELIVERYNOTE_NO else 0 end),
Row4_RowNum = max(case when RowNum = 4 then RowNum else 0 end),
Row4_DHH_DELIVERYNOTE_NO = max(case when RowNum = 4 then DHH_DELIVERYNOTE_NO else 0 end),
Row5_RowNum = max(case when RowNum = 5 then RowNum else 0 end),
Row5_DHH_DELIVERYNOTE_NO = max(case when RowNum = 5 then DHH_DELIVERYNOTE_NO else 0 end),
Row6_RowNum = max(case when RowNum = 6 then RowNum else 0 end),
Row6_DHH_DELIVERYNOTE_NO = max(case when RowNum = 6 then DHH_DELIVERYNOTE_NO else 0 end)
from MyData
group by DHH_ORDER_NO