T-SQL single row corresponding rows

Question

0.00/5 (No votes)

See more:

I have a SQL table similar to this (MSSQL 2017 Standard)

Key Period  Year    Srce   Destination   Amt
001 001    2018     A      B             100
002 001    2018     A      C             200
003 001    2018     B      A             99
004 001    2018     C      A             180

Initial transfers in first two rows, reciprocal in 2nd two rows

I need to get this (1 row for each in reciprocal transfer

Prd Year Srce SourceAmount  Dest Variance Source Dest Period Year Amount
001 2018 A    100           B    1        B      A    001    2018 99
001 2018 A    100           C    20       C      A    001    2018 180

Basically, a pivoted single row showing matching source/dest dest/source with variance

Please help, thanks in advance.

What I have tried:

Recursive CTE, PIVOTs etc. , nothing seems to be getting me the results i expect

This will eventually feed an SSRS report.

Posted 6-Jun-18 7:37am

Greg Crossan

Updated 7-Jun-18 0:14am

Maciej Los

v2

Add a Solution

Comments

CHill60 7-Jun-18 4:00am

Why not post the code that came closest to the results you want and we can go from there.
You could also try explaining what a reciprocal transfer is too, you have given us no context whatsoever.
And the fact it will eventually feed into an SSRS report isn't really relevent to the problem

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 7870345 · Answer 1 · 2018-06-06T23:18:00

Please try

SQL

SELECT     period, year,
	CASE WHEN srce<destination THEN srce ELSE destination END as srce,
	CASE WHEN srce<destination THEN destination ELSE srce END as dest,
	SUM(CASE WHEN srce<destination THEN amt ELSE -amt END) as variance,
	SUM(CASE WHEN srce<destination THEN amt ELSE 0 END) as srcamount,
	SUM(CASE WHEN srce<destination THEN 0 ELSE amt END) as destamount
FROM         dbo.data
GROUP BY period,year, 
	CASE WHEN srce<destination THEN srce ELSE destination END,
	CASE WHEN srce<destination THEN destination ELSE srce END

Maciej Los · Answer 2 · 2018-06-07T00:16:00

Seems you need to combine data based on period, year and source/destination

SQL

SELECT t1.Period AS Prd, t1.Year AS Year1, t1.Srce AS Src1, t1.Amt AS SourceAmount, t1.Desttination AS Dest1,
    t2.Amt - t1.Amt AS Variance, t2.Srce As Src2, t2.Destination AS Dest2, t2.Period, t2.Year AS Year2, t2.Amd AS Amount
FROM YourTable AS t1
    INNER JOIN YourTable As t2 ON 
        t1.Period = t2.Period AND 
        t1.Year = t2.Year AND
        t1.Srce = t2.Destination AND
        t1.Key < t2.Key

More about joins: Visual Representation of SQL Joins[^]

T-SQL single row corresponding rows

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0