CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Breaking data into multiple columns

I am working on a query where I require data to be divided in multiple columns based on the type of the value in it. I have a Voucher Type table where I am defining types now I want payment and receipt to be divided in two columns.

Here is the structure of the tables and sample data:

DROP TABLE [TransactionMaster];
DROP TABLE [VoucherType];

CREATE TABLE [VoucherType](
    [VoucherTypeCode] [tinyint] NOT NULL PRIMARY KEY,
    [FullName] [nvarchar](255) NOT NULL
);


INSERT INTO [VoucherType] VALUES (1, 'Cash Payment Voucher');
INSERT INTO [VoucherType] VALUES (2, 'Cash Receipt Voucher');
INSERT INTO [VoucherType] VALUES (3, 'Bank Payment Voucher');
INSERT INTO [VoucherType] VALUES (4, 'Bank Receipt Voucher');


CREATE TABLE [TransactionMaster](
    [ID] [bigint] NOT NULL PRIMARY KEY,
    [VoucherTypeCode] [tinyint] NOT NULL,
    [PayeeName] [varchar](255) NOT NULL,
    [RefNo] [nvarchar](50) NULL
    CONSTRAINT [FK_tbl_TransactionMaster_tbl_VoucherType] FOREIGN KEY([VoucherTypeCode])
    REFERENCES [VoucherType] ([VoucherTypeCode])
)



INSERT INTO [TransactionMaster] VALUES (1, 2, 'Asim', 'CRV-0001-LHR');
INSERT INTO [TransactionMaster] VALUES (2, 4, 'Ali', 'BRV-2421-KHI');
INSERT INTO [TransactionMaster] VALUES (3, 1, 'Erick', 'CPV-5435-ISL');
INSERT INTO [TransactionMaster] VALUES (4, 3, 'Asim', 'BPV-2345-CAN');
INSERT INTO [TransactionMaster] VALUES (5, 2, 'Mehboob', 'CRV-2976-PSH');
INSERT INTO [TransactionMaster] VALUES (6, 1, 'Erick', 'CPV-2323-KOH');

This is the query.

SELECT [Master].[RefNo], [Type].[FullName] [V.Type], [Master].[PayeeName] 
FROM [TransactionMaster] [Master], [VoucherType] [Type]
WHERE [Type].[VoucherTypeCode] = [Master].[VoucherTypeCode]


       Inward                                                    Outward
RefNo         V.Type                PayeeName       RefNo         V.Type                PayeeName

CPV-5435-ISL  Cash Payment Voucher  Erick           CRV-0001-LHR  Cash Receipt Voucher  Asim
BPV-2345-CAN  Bank Payment Voucher  Asim            BRV-2421-KHI  Bank Receipt Voucher  Ali
CPV-2323-KOH  Cash Payment Voucher  Erick           CRV-2976-PSH  Cash Receipt Voucher  Mehboob

So basically if the VoucherCode is 1 or 3 it'll be in Inward part columns and if the VoucherCode is 2 or 4 then it'll be in outward part columns.

So I require total six columns instead of three in one row. Hope this can be done. Outward and Inward is just to clear out columns part they are not required to be shown or something.

Upvotes: 0

Views: 58

Answers (1)

Tarun Gosain
Tarun Gosain

Reputation: 26

Check out this:

    select tb1.Refno, tb1.[FullName] as VType, tb1.PayeeName, tb2.Refno, tb2.[FullName] as VType, tb2.PayeeName from(
(select ROW_NUMBER()over (order by id) as rowid, TransactionMaster.RefNo, TransactionMaster.PayeeName, [VoucherType].FullName
from TransactionMaster 
inner join [VoucherType] on  TransactionMaster.VoucherTypeCode = [VoucherType].VoucherTypeCode
where TransactionMaster.[VoucherTypeCode] in (1,3))tb1
 full outer join 
(select ROW_NUMBER()over (order by id) as rowid, TransactionMaster.RefNo, TransactionMaster.PayeeName, [VoucherType].FullName
from TransactionMaster 
inner join [VoucherType] on  TransactionMaster.VoucherTypeCode = [VoucherType].VoucherTypeCode
where TransactionMaster.[VoucherTypeCode] in (2,4)
) tb2 on tb1.rowid = tb2.rowid)

Upvotes: 1

Related Questions