用于根据Unique字段将值从行填充到列的SQL查询 [英] SQL query to populate Values from rows to Columns based on Unique filed
问题描述
大家好，
我有一个视图，给出如下结果，
I have a view that giving the results as below,
AgreementKey
AgreementKey
已确认InvoiceNumber
Confirmed InvoiceNumber
原始InvoiceNumber
Original InvoiceNumber
RevenueTransaction TypeName
RevenueTransaction TypeName
CreditAmount
CreditAmount
DebitAmount
DebitAmount
RebillAmount
RebillAmount
5645238
9867000546
9897515417.00
Credit
-130135.59
NULL
NULL
5645238
9897515417
NULL
借记
NULL
130215.19
NULL
5645238
9897516538
9897515417.00
Rebill
NULL
NULL
120213.7154
在上述情况下，对于1份协议，有3份发票（Confirmedinv＃）具有不同的交易类型。
In above case, for 1 Agreement there are 3 invoices (Confirmedinv#) with different transaction types.
第1条记录，Inv＃9867000546是信用证发票和原始inv＃列显示此信用发票的借记发票＃
for 1 st record, Inv# 9867000546 is a credit invoice and the Original inv # column shows the debit invoice # for this credit invoice
现在，对于每条记录，我想填充最后3个Amount列，其中2个当前为NULL fileds 。
Now, for each record, I want to populate the last 3 Amount columns where 2 of them are currently NULL fileds.
例如，对于第一条记录，我想根据各自的确认/原始发票编号填写借记和重新计算金额。
For Example, for 1st record, i want to populate the Debit and rebill amounts based on their respective Confirmed/Original Invoice numbers.
预期结果如下，
expected results are as below,
AgreementKey
AgreementKey
已确认的InvoiceNumber
Confirmed InvoiceNumber
原始InvoiceNumber
Original InvoiceNumber
RevenueTransaction TypeName
RevenueTransaction TypeName
CreditAmount
CreditAmount
DebitAmount
DebitAmount
RebillAmount
RebillAmount
5645238
9867000546
9897515417.00
Credit
-130135.59
130215.19
120213.7154
5645238
9897515417
NULL
借记
-130135.59
130215.19
120213.7154
5645238
9897516538
9897515417.00
Rebill
-130135.59
130215.19
120213.7154
请帮助我如何获得预期的结果。
Please help me how can i get the expected results.
以下是查看代码，
推荐答案
创建表#C（AggKey CHAR（1），CREDITAM INT，DEBITAM INT）
INSERT INTO #C值（'A'， - 52，NULL）
INSERT INTO #C values（'A'，NULL，66）
INSERT INTO #C值（'A'，NULL，NULL）
INSERT INTO #C values（'A'，NULL，NULL）
$
SELECT * FROM #C
ALTER TABLE＃ C ADD ID INT IDENTITY（1,1）
SELECT AggKey，CREDITAM不为空时的情况
         那么CREDITAM
          ELSE（SELECT COALESCE（最高（CREDITAM），（选择TOP 1 CREDITAM FROM #C WHERE CREDITAM不为空））
FROM #C
WHERE ID< = t.ID）
&NBSP; &NBSP; END AS CREDITAM，
当DEBITAM不为空时的案例
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP;那么DEBITAM
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; ELSE（SELECT COALESCE（最高（DEBITAM），（选择TOP 1 DEBITAM FROM #C WHERE DEBITAM不为空））
FROM #C
WHERE ID< = t.ID）
&NBSP; &NBSP; END AS CREDITAM
&NBSP; &NBSP;
来自#C
CREATE TABLE #C (AggKey CHAR(1) ,CREDITAM INT,DEBITAM INT)
INSERT INTO #C values('A', -52,NULL)
INSERT INTO #C values('A', NULL,66)
INSERT INTO #C values('A',NULL,NULL)
INSERT INTO #C values('A', NULL,NULL)
SELECT * FROM #C
ALTER TABLE #C ADD ID INT IDENTITY(1,1)
SELECT AggKey,CASE WHEN CREDITAM is not null
THEN CREDITAM
ELSE (SELECT COALESCE(max(CREDITAM),(SELECT TOP 1 CREDITAM FROM #C WHERE CREDITAM is not null))
FROM #C
WHERE ID <= t.ID)
END AS CREDITAM,
CASE WHEN DEBITAM is not null
THEN DEBITAM
ELSE (SELECT COALESCE(max(DEBITAM),(SELECT TOP 1 DEBITAM FROM #C WHERE DEBITAM is not null))
FROM #C
WHERE ID <= t.ID)
END AS CREDITAM
FROM #C
这篇关于用于根据Unique字段将值从行填充到列的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
