how to separate value columns my matching two flag

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to separate value columns my matching two flag

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2012-05-08 : 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)

17689 Posts

Posted - 2012-05-08 : 04:30:12

can you explain the business logic on the transformation ?

KH
[spoiler]Time is always against us[/spoiler]

yingchai
Starting Member

33 Posts

Posted - 2012-05-08 : 05:08:30

quote:
Originally posted by khtan

can you explain the business logic on the transformation ?

KH
[spoiler]Time is always against us[/spoiler]

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: [url]http://www.sqlservercentral.com/Forums/Topic1295099-392-1.aspx?Update=1[/url]. Hopefully you get some idea of what I mean.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-08 : 06:13:56

for the aaa & bbb pair how do you identify which is inbound, which is outbound ?

KH
[spoiler]Time is always against us[/spoiler]

yingchai
Starting Member

33 Posts

Posted - 2012-05-08 : 10:00:05

quote:
Originally posted by khtan

for the aaa & bbb pair how do you identify which is inbound, which is outbound ?

KH
[spoiler]Time is always against us[/spoiler]

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources