代码之家 › 专栏 › 技术社区 › Doonie Darkoo

将数据分为多个列

sql-server

Doonie Darkoo · 技术社区 · 6 年前

我正在处理一个查询,其中我要求根据数据中值的类型将数据分为多个列。我有一个凭证类型表,在那里我正在定义类型,现在我希望付款和收据被分为两列。

下面是表和示例数据的结构:

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');

这就是问题所在。

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

所以基本上如果 VoucherCode 是1或3,它将在内部部分列中,如果 凭证代码 如果是2或4,则它将位于外部部分列中。

所以我需要总共六列而不是一行三列。希望能做到。向外和向内只是为了清除列中不需要显示的部分或其他内容。

1 回复 | 直到 6 年前

erTarun26 6 年前

看看这个:

    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)