how to separate value columns my matching two flag

Question

Post reply

how to separate value columns my matching two flag

yingchai

SSCommitted

Points: 1669
More actions
May 4, 2012 at 3:16 am

#260513

Hi SQL Gurus,
I have a requirement here to transform the table and need your advise on this. Below is my source table.
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!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

What makes 'aaa' to be only "inbound" but 'bbb' and 'ccc' only "outbound"?

You should have some kind of flag/rule.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

yingchai SSCommitted Points: 1669 More actions · Answer 2

Hi Eugene,

There is really no rule to make 'aaa' to be only "inbound" but 'bbb' and 'ccc' only "outbound.

If there is an amount for 'aaa' to 'bbb' (FLAG1 to FLAG2) and vice versa, either 'aaa' or 'bbb' can be inbound or outbound.

Actually, there is a solution for this query but without being separated by the 'DATA_TYPE' column from the source table.

The solution can be found from here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=173432

Just to share with you.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

yingchai (5/4/2012)
Hi Eugene,
There is really no rule to make 'aaa' to be only "inbound" but 'bbb' and 'ccc' only "outbound.
If there is an amount for 'aaa' to 'bbb' (FLAG1 to FLAG2) and vice versa, either 'aaa' or 'bbb' can be inbound or outbound.
Actually, there is a solution for this query but without being separated by the 'DATA_TYPE' column from the source table.
The solution can be found from here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=173432
Just to share with you.

The why you didn't put lines in expected results with 'bbb' and 'ccc' being INBOUND?

Looks like there is a rule, which I overlooked the last time. INBOUND records have positive amounts!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

imex Ten Centuries Points: 1234 More actions · Answer 4

Hi,

Try:

select

s.FLAG1 as INBOUND,

s.AMOUNT as BASE_AMOUNT,

Coalesce( (select i.AMOUNT from @Sample as i

where (i.FLAG1 = s.FLAG1) and

(i.FLAG2 = s.FLAG2) and

(i.YEAR = s.YEAR) and

(i.DATA_TYPE = 'INTERCO') ), 0) as INTERCO_AMOUNT,

s.FLAG2 as OUTBOUND,

Coalesce( (select i.AMOUNT from @Sample as i

where (i.FLAG1 = s.FLAG2) and

(i.FLAG2 = s.FLAG1) and

(i.YEAR = s.YEAR) and

(i.DATA_TYPE = 'BASE') ), 0) as BASE_AMOUNT_B,

Coalesce( (select i.AMOUNT from @Sample as i

where (i.FLAG1 = s.FLAG2) and

(i.FLAG2 = s.FLAG1) and

(i.YEAR = s.YEAR) and

(i.DATA_TYPE = 'INTERCO') ), 0) as INTERCO_AMOUNT_B,

s.YEAR

from @Sample as s

where DATA_TYPE = 'BASE'

Hope this helps.

https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

imex (5/4/2012)
Hi,
Try:
select
s.FLAG1 as INBOUND,
s.AMOUNT as BASE_AMOUNT,
Coalesce( (select i.AMOUNT from @Sample as i
where (i.FLAG1 = s.FLAG1) and
(i.FLAG2 = s.FLAG2) and
(i.YEAR = s.YEAR) and
(i.DATA_TYPE = 'INTERCO') ), 0) as INTERCO_AMOUNT,
s.FLAG2 as OUTBOUND,
Coalesce( (select i.AMOUNT from @Sample as i
where (i.FLAG1 = s.FLAG2) and
(i.FLAG2 = s.FLAG1) and
(i.YEAR = s.YEAR) and
(i.DATA_TYPE = 'BASE') ), 0) as BASE_AMOUNT_B,
Coalesce( (select i.AMOUNT from @Sample as i
where (i.FLAG1 = s.FLAG2) and
(i.FLAG2 = s.FLAG1) and
(i.YEAR = s.YEAR) and
(i.DATA_TYPE = 'INTERCO') ), 0) as INTERCO_AMOUNT_B,
s.YEAR
from @Sample as s
where DATA_TYPE = 'BASE'
Hope this helps.

That will return a row for 'bbb' as INBOUND with negative amount...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

imex Ten Centuries Points: 1234 More actions · Answer 6

You're right Eugene.

I believe it is necessary to improve the Where clause to get the expected result.

https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

yingchai SSCommitted Points: 1669 More actions · Answer 7

Hi imex and Eugene,

Yeah, you guys are right. If 'aaa' exists in INBOUND and 'bbb' exists in OUTBOUND, then 'bbb' and 'aaa' should not exists in INBOUND and OUTBOUND which is happening in this case. Will try to improve the sql...

Thanks.