Okay here's my task - in our business every (98%) sales invoice and sales return have 7 line items.
I want to write a query that pivots the line items into columns.
Sounds simple right? Well in our case some transactions are created by manual entry and some (most) by API.
The Transactions created manually have line sequence in the GP increments of 16384, 32768, etc. The Transactions created by the API actually have Line Item Sequences as 1,2,3,4 etc. The API doesn't conform with 16384, etc.
Here's what I got so far -
Select * From
(select A.CUSTNMBR, A.SOPNUMBE, CAST(A.DOCDATE as date) DocDate, DOCAMNT as DocAmnt,
B.ITEMDESC,
B.LNITMSEQ
from SOP30200 A
left outer join SOP30300 B
ON
A.SOPNUMBE=B.SOPNUMBE and A.SOPTYPE=B.SOPTYPE) as SRC
PIVOT
( MAX (ITEMDESC) for LNITMSEQ in ([16368] , [32768])
) as PVT
My problem - I'm specifying specific Line Item Sequence Numbers - is there an alternative way in SQL to simply specify the first 7 rows in the SOP30300 table as columns?
Row Number(?) instead of PVT?
Thanks in advance