how to separate value columns my matching two flag
-
Hi SQL gurus,
I have a table below;
FLAG1 | FLAG2 | YEAR | PERIOD | AMOUNT
---------------------------------------------
aaa | bbb | 2012 | 01 | 100
aaa | ccc | 2012 | 02 | 50
bbb | aaa | 2012 | 01 | -100
How can I transform the source into the view below?
INBOUND | AMOUNT | OUTBOUND | AMOUNT | YEAR | PERIOD | DIFF
------------------------------------------------------------------
aaa | 100 | bbb | -100 | 2012 | 01 | 0
aaa | 50 | ccc | 0 | 2012 | 02 | 50
Kindly advise. Thanks.
-
After 320 visits, you should know by now that you need to provide DDL, readily consumable sample data and expected results as per this article.[/url]
First, I've build some sample data as per your post.
--Build the sample dataSELECT FLAG1,FLAG2,YEAR,PERIOD,AMOUNT
INTO #yourSampleData
FROM (VALUES('aaa','bbb',2012,01,100),
('aaa','ccc',2012,02,50),
('bbb','aaa',2012,01,-100)) a(FLAG1,FLAG2,YEAR,PERIOD,AMOUNT);
See how I've made it so that anyone can pick that up and execute it to create the exact table and data that I've used as part of my solution? This makes it much easier for the unpaid volunteers of this site to help provide solutions to your problem.
Based on the sample data that I've knocked up, you could do it like this: -
--Actual solutionSELECT
a.FLAG1 AS INBOUND, a.AMOUNT AS AMOUNT, a.FLAG2 AS OUTBOUND,
ISNULL(b.AMOUNT,0) AS AMOUNT, a.YEAR, a.PERIOD,
a.AMOUNT + ISNULL(b.AMOUNT,0) AS DIFF
FROM #yourSampleData a
LEFT OUTER JOIN #yourSampleData b ON a.FLAG2 = b.FLAG1 AND a.YEAR = b.YEAR
AND a.PERIOD = b.PERIOD;
Which produces
INBOUND AMOUNT OUTBOUND AMOUNT YEAR PERIOD DIFF------- ----------- -------- ----------- ----------- ----------- -----------
aaa 100 bbb -100 2012 1 0
aaa 50 ccc 0 2012 2 50
bbb -100 aaa 100 2012 1 0
To get results identical to how you want them, we need an ID of some form that differentiates the rows. I could do a ROW_NUMBER based on FLAG1 (e.g. alphabetical order), but you didn't provide me with enough information to know whether or not this is correct.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Cadavre (4/10/2012)
After 320 visits, you should know by now that you need to provide DDL, readily consumable sample data and expected results as per this article.[/url]First, I've build some sample data as per your post.
--Build the sample dataSELECT FLAG1,FLAG2,YEAR,PERIOD,AMOUNT
INTO #yourSampleData
FROM (VALUES('aaa','bbb',2012,01,100),
('aaa','ccc',2012,02,50),
('bbb','aaa',2012,01,-100)) a(FLAG1,FLAG2,YEAR,PERIOD,AMOUNT);
See how I've made it so that anyone can pick that up and execute it to create the exact table and data that I've used as part of my solution? This makes it much easier for the unpaid volunteers of this site to help provide solutions to your problem.
Based on the sample data that I've knocked up, you could do it like this: -
--Actual solutionSELECT
a.FLAG1 AS INBOUND, a.AMOUNT AS AMOUNT, a.FLAG2 AS OUTBOUND,
ISNULL(b.AMOUNT,0) AS AMOUNT, a.YEAR, a.PERIOD,
a.AMOUNT + ISNULL(b.AMOUNT,0) AS DIFF
FROM #yourSampleData a
LEFT OUTER JOIN #yourSampleData b ON a.FLAG2 = b.FLAG1 AND a.YEAR = b.YEAR
AND a.PERIOD = b.PERIOD;
Which produces
INBOUND AMOUNT OUTBOUND AMOUNT YEAR PERIOD DIFF------- ----------- -------- ----------- ----------- ----------- -----------
aaa 100 bbb -100 2012 1 0
aaa 50 ccc 0 2012 2 50
bbb -100 aaa 100 2012 1 0
To get results identical to how you want them, we need an ID of some form that differentiates the rows. I could do a ROW_NUMBER based on FLAG1 (e.g. alphabetical order), but you didn't provide me with enough information to know whether or not this is correct.
Sorry for the inconveniences. Will provide the DDL next time.
Currently, the table does not have some form of primary key. But I can try insert a primary key to make each row unique. Not sure if this is what you want.
Here is my DDL with primary key column included...
SELECT ID,FLAG1,FLAG2,YEAR,PERIOD,AMOUNT
INTO #yourSampleData
FROM (VALUES(1,'aaa','bbb',2012,01,100),
(2,'aaa','ccc',2012,02,50),
(3,'bbb','aaa',2012,01,-100)) a(ID,FLAG1,FLAG2,YEAR,PERIOD,AMOUNT);
Kindly advise.
-
Not really.
What I want to know is how can you tell which is inbound and which is outbound?
In your sample data, aaa and bbb are the FLAG1 and FLAG2 of eachother. So one of those is inbound and the other is outbound. Physical ordering in the table is not guaranteed by SQL Server, so you can't just say that the 1st one is inbound and the second is outbound. What we need is a way to determine which is inbound and which is outbound.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Guessing here
WITH CTE AS (SELECT CASE WHEN AMOUNT>0 THEN FLAG1 ELSE FLAG2 END AS INBOUND,
CASE WHEN AMOUNT>0 THEN AMOUNT ELSE 0 END AS INBOUNDAMOUNT,
CASE WHEN AMOUNT>0 THEN FLAG2 ELSE FLAG1 END AS OUTBOUND,
CASE WHEN AMOUNT>0 THEN 0 ELSE AMOUNT END AS OUTBOUNDAMOUNT,
YEAR,
PERIOD
FROM #yourSampleData)
SELECT INBOUND,
SUM(INBOUNDAMOUNT) AS AMOUNT,
OUTBOUND,
SUM(OUTBOUNDAMOUNT) AS AMOUNT,
YEAR,PERIOD,
SUM(INBOUNDAMOUNT)+SUM(OUTBOUNDAMOUNT) AS DIFF
FROM CTE
GROUP BY INBOUND,OUTBOUND,YEAR,PERIOD
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Cadavre (4/10/2012)
Not really.What I want to know is how can you tell which is inbound and which is outbound?
In your sample data, aaa and bbb are the FLAG1 and FLAG2 of eachother. So one of those is inbound and the other is outbound. Physical ordering in the table is not guaranteed by SQL Server, so you can't just say that the 1st one is inbound and the second is outbound. What we need is a way to determine which is inbound and which is outbound.
Yes. From the example, aaa and bbb are the FLAG1 and FLAG2 of each other. But there is no specific ordering to indicate which one is outbound or inbound. The rule here is if there is a record for INBOUND(aaa) and OUTBOUND(bbb) being displayed, then it should not display another record for INBOUND(bbb) and OUTBOUND(aaa) again.
-
yingchai (4/10/2012)
Cadavre (4/10/2012)
Not really.What I want to know is how can you tell which is inbound and which is outbound?
In your sample data, aaa and bbb are the FLAG1 and FLAG2 of eachother. So one of those is inbound and the other is outbound. Physical ordering in the table is not guaranteed by SQL Server, so you can't just say that the 1st one is inbound and the second is outbound. What we need is a way to determine which is inbound and which is outbound.
Yes. From the example, aaa and bbb are the FLAG1 and FLAG2 of each other. But there is no specific ordering to indicate which one is outbound or inbound. The rule here is if there is a record for INBOUND(aaa) and OUTBOUND(bbb) being displayed, then it should not display another record for INBOUND(bbb) and OUTBOUND(aaa) again.
I think you're talking about physical ordering, which is not guaranteed by SQL Server.
If I change your sample data to this: -
SELECT FLAG1,FLAG2,YEAR,PERIOD,AMOUNTINTO #yourSampleData
FROM (VALUES('bbb','aaa',2012,01,-100),
('aaa','bbb',2012,01,100),
('aaa','ccc',2012,02,50)) a(FLAG1,FLAG2,YEAR,PERIOD,AMOUNT);
Which one is inbound and which is outbound?
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Hi Cadavre,
bbb will be Inbound and aaa will be Outbound.
-
yingchai (4/10/2012)
Hi Cadavre,bbb will be Inbound and aaa will be Outbound.
This is your problem.
You're relying on the physical order of the table. This is not guaranteed by SQL Server so should not be used.
It's like writing a SELECT TOP 1 with no ORDER BY clause, it is not guaranteed to be the same every time.
You need some way of determining which is the earliest record; whether that be an IDENTITY column, a computed column with GETDATE or some other method doesn't matter.
Try reading this link and see if it helps
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
yingchai (4/10/2012)
Hi Cadavre,bbb will be Inbound and aaa will be Outbound.
Which columns determine that?
SQL does not have any concept of a row's position within a table, a table is an unordered set, so your data is simply a group of three rows, all of these are identical sets of data.
('bbb','aaa',2012,01,-100),
('aaa','bbb',2012,01,100),
('aaa','ccc',2012,02,50)
('bbb','aaa',2012,01,-100),
('aaa','ccc',2012,02,50)
('aaa','bbb',2012,01,100),
('aaa','bbb',2012,01,100),
('bbb','aaa',2012,01,-100),
('aaa','ccc',2012,02,50)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (4/10/2012)
yingchai (4/10/2012)
Hi Cadavre,bbb will be Inbound and aaa will be Outbound.
Which columns determine that?
SQL does not have any concept of a row's position within a table, a table is an unordered set, so your data is simply a group of three rows, all of these are identical sets of data.
('bbb','aaa',2012,01,-100),
('aaa','bbb',2012,01,100),
('aaa','ccc',2012,02,50)
('bbb','aaa',2012,01,-100),
('aaa','ccc',2012,02,50)
('aaa','bbb',2012,01,100),
('aaa','bbb',2012,01,100),
('bbb','aaa',2012,01,-100),
('aaa','ccc',2012,02,50)
I'll take the threes sets of your data as an example:
Set 1:
('bbb','aaa',2012,01,-100),
('aaa','bbb',2012,01,100),
('aaa','ccc',2012,02,50)
>> Since the first record starts with 'bbb' followed by 'aaa', then 'bbb' is Inbound while 'aaa' is Outbound.
Set 2:
('bbb','aaa',2012,01,-100),
('aaa','ccc',2012,02,50),
('aaa','bbb',2012,01,100),
>> Since the first record starts with 'bbb' followed by 'aaa', then 'bbb' is Inbound while 'aaa' is Outbound.
Set 3:
('aaa','bbb',2012,01,100),
('bbb','aaa',2012,01,-100),
('aaa','ccc',2012,02,50)
>> Since the first record starts with 'aaa' followed by 'bbb', then 'aaa' is Inbound while 'bbb' is Outbound.
Do you understand what I meant?
-
You're missing my point.
All three of those sets of data are identical, there is no such thing as 'order' of rows in a table. You cannot say that a row is the 1st, 2nd or 3rd, because there is no defined order
So, what column within your data defines which row is 1st, 2nd or 3rd?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (4/10/2012)
You're missing my point.All three of those sets of data are identical, there is no such thing as 'order' of rows in a table. You cannot say that a row is the 1st, 2nd or 3rd, because there is no defined order
So, what column within your data defines which row is 1st, 2nd or 3rd?
I think I got the results that I wanted from the solution posted from another fellow forumer here...
WITH CTE AS (
SELECT CASE WHEN AMOUNT>0 THEN FLAG1 ELSE FLAG2 END AS INBOUND,
CASE WHEN AMOUNT>0 THEN AMOUNT ELSE 0 END AS INBOUNDAMOUNT,
CASE WHEN AMOUNT>0 THEN FLAG2 ELSE FLAG1 END AS OUTBOUND,
CASE WHEN AMOUNT>0 THEN 0 ELSE AMOUNT END AS OUTBOUNDAMOUNT,
YEAR,
PERIOD
FROM #yourSampleData)
SELECT INBOUND,
SUM(INBOUNDAMOUNT) AS AMOUNT,
OUTBOUND,
SUM(OUTBOUNDAMOUNT) AS AMOUNT,
YEAR,PERIOD,
SUM(INBOUNDAMOUNT)+SUM(OUTBOUNDAMOUNT) AS DIFF
FROM CTE
GROUP BY INBOUND,OUTBOUND,YEAR,PERIOD
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply