| Author |
 Topic  |
|
|
yingchai
Starting Member
33 Posts |
 Posted - 05/08/2012 : 04:25:11
|
Hi SQL experts,
I have a new requirements here to transform the table and need your advise on this.
FLAG1 | FLAG2 | YEAR | PERIOD | DATA_TYPE | AMOUNT
---------------------------------------------------------
aaa | bbb | 2012 | 01 | BASE | 100
aaa | ccc | 2012 | 02 | BASE | 50
bbb | aaa | 2012 | 01 | BASE | -100
aaa | bbb | 2012 | 01 | INTERCO | 90
aaa | ccc | 2012 | 02 | INTERCO | 40
bbb | aaa | 2012 | 01 | INTERCO | -70
ccc | aaa | 2012 | 02 | INTERCO | -60
How can I transform the source into the view below?
INBOUND | BASE_AMOUNT | INTERCO_AMOUNT | OUTBOUND | BASE_AMOUNT | INTERCO_AMOUNT | YEAR | PERIOD
----------------------------------------------------------------------------------------------------
aaa | 100 | 90 | bbb | -100 | -70 | 2012 | 01
aaa | 50 | 40 | ccc | 0 | -60 | 2012 | 02
Below is the DDL and sample data for this:
DECLARE @Sample TABLE
(
Flag1 CHAR(3) NOT NULL,
Flag2 CHAR(3) NOT NULL,
[Year] SMALLINT NOT NULL,
Period CHAR(2) NOT NULL,
DATA_TYPE CHAR(8) NOT NULL,
Amount INT NOT NULL
)
INSERT @Sample
VALUES ('aaa', 'bbb', 2012, '01', 'BASE', 100),
('aaa', 'ccc', 2012, '02', 'BASE', 50),
('bbb', 'aaa', 2012, '01', 'BASE', -100),
('aaa', 'bbb', 2012, '01', 'INTERCO', 90),
('aaa', 'ccc', 2012, '02', 'INTERCO', 40),
('bbb', 'aaa', 2012, '01', 'INTERCO', -70),
('ccc', 'aaa', 2012, '02', 'INTERCO', -60)
Kindly advise. Thanks!
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 05/08/2012 : 04:30:12
|
can you explain the business logic on the transformation ?
KH
Time is always against us
|
 |
|
|
yingchai
Starting Member
33 Posts |
Posted - 05/08/2012 : 05:08:30
|
quote:
Originally posted by khtan
can you explain the business logic on the transformation ?
KH
Time is always against us
Hi khtan,
Basically, there are two important business logic here...
1. The value populated in the BASE_AMOUNT and INTERCO_AMOUNT column depends from DATA_TYPE value at the source table.
2. If 'aaa' exists in INBOUND and 'bbb' exists in OUTBOUND, then 'bbb' and 'aaa' should not exists in INBOUND and OUTBOUND again.
Currently, I am trying to improve the sql which I receive from this forum: http://www.sqlservercentral.com/Forums/Topic1295099-392-1.aspx?Update=1. Hopefully you get some idea of what I mean.
Thanks. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 05/08/2012 : 06:13:56
|
for the aaa & bbb pair how do you identify which is inbound, which is outbound ?
KH
Time is always against us
|
|
|
|
yingchai
Starting Member
33 Posts |
Posted - 05/08/2012 : 10:00:05
|
quote:
Originally posted by khtan
for the aaa & bbb pair how do you identify which is inbound, which is outbound ?
KH
Time is always against us
if the aaa & bbb pair appears first in FLAG1 and FLAG2 pair, then aaa is inbound, bbb is outbound.
if the bbb & aaa pair appears first in FLAG1 and FLAG2 pair, then bbb is inbound, aaa is outbound.
it all depends which flag values appear first. from my example, aaa & bbb pair appear first rather than bbb & aaa pair. therefore, aaa is inbound while bbb is outbound.
thanks. |
|
|
| |
Topic |
|
|
|
how to separate value columns my matching two flag
