使用嵌套Case语句的SQL行到列

I am trying to get a nested case statement to work which in stages is working but now I have nested another case statement it complains about syntax. I have looked at many examples and can't see where I have gone wrong.

我试图让一个嵌套的case语句工作，分阶段工作，但现在我已经嵌套了另一个case语句，它抱怨语法。我看了很多例子，看不出我哪里出错了。

What I wish to achieve is a row per PolicyTransactionId (set in the where clause) with each account code as a column. This worked for me, where it turns to error is adding the nested entry to look at the debit/credit entry and turning the number to negative where appropriate.

我希望实现的是每个PolicyTransactionId（在where子句中设置）的行，每个帐户代码作为一列。这对我有用，它转向错误是添加嵌套条目以查看借方/贷方条目并在适当的情况下将数字转为负数。

Attempt so far attached.

到目前为止尝试了。

http://sqlfiddle.com/#!6/8db47/3

CREATE TABLE [dbo].[PolicyTransactionSplits](
    [PolicyTransactionSplitId] [int] NOT NULL,
    [PolicyTransactionId] [int] NOT NULL,
    [AccountCode] [int] NOT NULL,
    [AccountDesc] [nvarchar](max) NULL,
    [TransactionType] [nvarchar](max) NULL,
    [Amount] [decimal](18, 2) NOT NULL,
    [Adjusted] [bit] NOT NULL DEFAULT ((0))
  )
;



INSERT INTO PolicyTransactionSplits
    ([PolicyTransactionSplitId], [PolicyTransactionId], [AccountCode], [AccountDesc], [TransactionType], [Amount], [Adjusted])
VALUES
(1551,1096,1000,'Total Transaction Premium','Debit',50,0),
(1552,1096,1010,'Total Net Premium','Debit',50,0)
;

...

select
  max(case when AccountCode = 1000 then case when TransactionType = 'Debit' then Amount end else case when AccountCode = 1000 then case when TransactionType = 'Credit' then Amount*-1 end) [Total Transaction Premium]
  max(case when AccountCode = 1000 then case when TransactionType = 'Debit' then Amount end else case when AccountCode = 1000 then case when TransactionType = 'Credit' then Amount*-1 end) [Total Transaction Premium]
from PolicyTransactionSplits
where PolicyTransactionId = 10

2 个解决方案

#1

You don't need to nest case statements:

您不需要嵌套case语句：

select max(case when AccountCode = 1000 and TransactionType = 'Debit'
                then amount
                when AccountCode = 1000 and TransactionType = 'Credit' 
                then Amount * -1
           end) as [Total Transaction Premium],
       max(case when AccountCode = 1000 and TransactionType = 'Debit'
                then Amount 
                when AccountCode = 1000 and TransactionType = 'Credit' 
                then Amount * -1
           end) as [Total Transaction Premium]
from PolicyTransactionSplits
where PolicyTransactionId = 10;

It is unclear to me why are you are repeating the same logic. Perhaps you just want this:

我不清楚你为什么要重复相同的逻辑。也许你只想要这个：

select max(case when TransactionType = 'Debit'
                then amount
                when TransactionType = 'Credit' 
                then Amount * -1
           end) as [Total Transaction Premium]
from PolicyTransactionSplits
where PolicyTransactionId = 10 and AccountCode = 1000;

#2

You get an syntax error because you're missing 2 end statements and a comma at the end of the first line, but your logic doesn't make much sense either, because it's this:

你得到一个语法错误，因为你在第一行末尾缺少2个结束语句和一个逗号，但你的逻辑也没有多大意义，因为它是这样的：

case when AccountCode = 1000 then 
  case when TransactionType = 'Debit' then Amount end 
else 
  case when AccountCode = 1000 then 
    case when TransactionType = 'Credit' then Amount*-1 end 
  end
end

You'll never get to the second part because AccountCode = 1000 is the criteria in both cases. Maybe you're looking for this?

你永远不会到达第二部分，因为在这两种情况下，AccountCode = 1000都是标准。也许你正在寻找这个？

case when AccountCode = 1000 then 
  case when TransactionType = 'Debit' then Amount  
       when TransactionType = 'Credit' then Amount*-1 
  end
end

#1