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