question

LilyJune-5583 avatar image
0 Votes"
LilyJune-5583 asked EchoLiu-msft commented

Show 2 rows result data as one rows multiple column

CREATE TABLE [dbo].[Trxntable](
[Currency] [varchar](3) NULL,
[Amount] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Trxntable] ([Currency], [Amount]) VALUES (N'840', CAST(100.00 AS Decimal(18, 2)))
INSERT [dbo].[Trxntable] ([Currency], [Amount]) VALUES (N'702', CAST(-100.00 AS Decimal(18, 2)))
INSERT [dbo].[Trxntable] ([Currency], [Amount]) VALUES (N'840', CAST(-200.00 AS Decimal(18, 2)))
INSERT [dbo].[Trxntable] ([Currency], [Amount]) VALUES (N'702', CAST(150.00 AS Decimal(18, 2)))

select SUM(Amount)[TotalAmount],Currency from Trxntable group by Currency
131541-image.png
I would like to get as one rows based on amount and currency,if amount less than zero want to take as debit currecy and debit amount else credit,please help suggest any way
131517-image.png





sql-server-transact-sql
image.png (1.3 KiB)
image.png (1.6 KiB)
image.png (1.6 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We do not recommend modifying the original post. If you have any updates, you can post them in the comments or answers, otherwise it will disrupt our discussion thread.
In addition, can you provide more data (more than three lines) and the output you expect.

1 Vote 1 ·

so sorry, because the first time for me.Noted with thanks

0 Votes 0 ·

It doesn't matter.Many people who come to the forum for the first time are not familiar with the default rules of the forum.

Thank you for providing the CRETAE sentence and the screenshot of the expected output. This has brought us great convenience in solving problems.

Echo

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered
 ;WITH cte
 as(SELECT [Currency],SUM([Amount]) [TotalAmount],
 ROW_NUMBER() OVER(ORDER BY [Currency]) rr
 FROM [dbo].[Trxntable]
 GROUP BY [Currency])
    
 SELECT 
 MAX(CASE WHEN [TotalAmount]<0 THEN [Currency] END) AS debitCurrency,
 MAX(CASE WHEN [TotalAmount]<0 THEN [TotalAmount] END) AS debitamount,
 MAX(CASE WHEN [TotalAmount]>0 THEN [Currency] END) AS crediCurrency,
 MAX(CASE WHEN [TotalAmount]>0 THEN [TotalAmount] END) AS creditamount
 FROM cte
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered LilyJune-5583 commented

Hi @LilyJune-5583,

Please try:

 ;WITH cte
 as(SELECT [Currency],SUM([Amount]) [TotalAmount],
 ROW_NUMBER() OVER(ORDER BY [Currency]) rr
 FROM [dbo].[Trxntable]
 GROUP BY [Currency])
    
 SELECT c.[Currency] AS SGDCurrency,
 c.[TotalAmount] AS SGDamount,
 c2.[Currency] AS USDCurrency,
 c2.[TotalAmount] AS USDamount 
 FROM cte c
 JOIN cte c2 ON  c.rr=c2.rr-1

Output:
131571-image.png


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (2.8 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I would like to get as one rows based on amount and currency,if amount less than zero want to take as debit currecy and debit amount else credit,pls any suggest

131390-image.png


0 Votes 0 ·
image.png (1.6 KiB)

Hi @LilyJune-5583,

I have provided the latest answer, please check if it works.

Regards
Echo

1 Vote 1 ·

It's work .Thanks.I appreciate that @EchoDu-MSFT

0 Votes 0 ·