Need help in SQL query

1

I am having 2 diff. table as below

CREATE TABLE dbo.sop1(
SOP_Number char(21) NOT NULL,
SOP_Type smallint NOT NULL
)

CREATE TABLE dbo.SopDist(
SOPNUMBE char(21) NOT NULL,
SEQNUMBR int NOT NULL,
ACTINDX int NOT NULL,
DEBITAMT numeric(19, 5) NOT NULL,
CRDTAMNT numeric(19, 5) NOT NULL
)

INSERT INTO sop1(SOP_Number,SOP_Type)VALUES('STDINV2256',1)

INSERT INTO SopDist(SOPNUMBE,SEQNUMBR,ACTINDX ,DEBITAMT,CRDTAMNT)VALUES('STDINV2256',10,14,100,0)
INSERT INTO SopDist(SOPNUMBE,SEQNUMBR,ACTINDX ,DEBITAMT,CRDTAMNT)VALUES('STDINV2256',20,17,0,200)
INSERT INTO SopDist(SOPNUMBE,SEQNUMBR,ACTINDX ,DEBITAMT,CRDTAMNT)VALUES('STDINV2256',30,12,350,0)
INSERT INTO SopDist(SOPNUMBE,SEQNUMBR,ACTINDX ,DEBITAMT,CRDTAMNT)VALUES('STDINV2256',40,224,0,100)
INSERT INTO SopDist(SOPNUMBE,SEQNUMBR,ACTINDX ,DEBITAMT,CRDTAMNT)VALUES('STDINV2256',50,89,0,150)

SELECT SOP1.SOP_NUMBER, SOP1.SOP_TYPE, SOPDIST.SOPNUMBE, SOPDIST.SEQNUMBR, SOPDIST.ACTINDX,
SOPDIST.DEBITAMT, SOPDIST.CRDTAMNT FROM SOP1
INNER JOIN SOPDIST ON SOP1.SOP_NUMBER = SOPDIST.SOPNUMBE

OutPut :
SOP_NUMBER SOP_TYPE SOPNUMBE SEQNUMBR ACTINDX DEBITAMT CRDTAMNT
STDINV2256 1 STDINV2256 10 14 100.00000 0.00000
STDINV2256 1 STDINV2256 20 17 0.00000 200.00000
STDINV2256 1 STDINV2256 30 12 350.00000 0.00000
STDINV2256 1 STDINV2256 40 224 0.00000 100.00000
STDINV2256 1 STDINV2256 50 89 0.00000 150.00000

My Expected ouput :

SOP_NUMBER SOP_TYPE SOPNUMBE SEQNUMBR ACTINDX DEBITAMT CRDTAMNT
STDINV2256 1 STDINV2256 10 14 100.00000 0.00000
NULL NULL STDINV2256 20 17 0.00000 200.00000
NULL NULL STDINV2256 30 12 350.00000 0.00000
NULL NULL STDINV2256 40 224 0.00000 100.00000
NULL NULL STDINV2256 50 89 0.00000 150.00000

Please help soon

Thanks in Advance

2

Try this:

with cte
  as (select s.sop_number
            ,s.sop_type
            ,sd.sopnumbe
            ,sd.seqnumbr
            ,sd.actindx
            ,sd.debitamt
            ,sd.crdtamnt
            ,row_number() over(partition by s.sop_number
                                           ,s.sop_type
                               order by sd.seqnumbr
                              )
             as rn
        from sop1 as s
             inner join sopdist as sd
                     on sd.sopnumbe=s.sop_number
     )
select case when c.rn=1 then c.sop_number else null end as sop_number
      ,case when c.rn=1 then c.sop_type else null end as sop_type
      ,c.sopnumbe
      ,c.seqnumbr
      ,c.actindx
      ,c.debitamt
      ,c.crdtamnt
  from cte as c
 order by c.sop_number
         ,c.sop_type
         ,c.seqnumbr
;
3

I have copied this query but not working.

appreciate if you can send me full query

4

I am able to execute but problem is
if there are more rows in SOP1 table with same SOP number.

INSERT INTO sop1(SOP_Number,SOP_Type)VALUES('STDINV2256',2)

5

Replace the INSERT INTO ... VALUES with INSERT INTO ... SELECT