| Author |
 Topic  |
|
|
rc1138
Starting Member
Canada
35 Posts |
 Posted - 08/23/2010 : 21:43:45
|
Hi All,
Was hoping that someone could help me regarding this vertical Query output
I have 3 tables
First table
Name: Table1
AccountNames
Design
AccountName varchar(50)
AccountName
Account1
Account2
Account3
Account4
Second Table
Name: Table2
Design
Id Int
SiteNbr Int
Account1 Numeric(9,2)
Account2 Numeric(9,2)
Account3 Numeric(9,2)
Account4 Numeric(9,2)
Id SiteNbr Account1 Account2 Account3 Account4
1 3145 5.99 null null 2.00
Third Table
Name: Table3
Design
Id Int
SiteNbr Int
Account1 Numeric(9,2)
Account2 Numeric(9,2)
Account3 Numeric(9,2)
Account4 Numeric(9,2)
Id SiteNbr Account1 Account2 Account3 Account4
1 3145 null 3.50 null null
The output should be
Account Name Budget1 Budget2
Account1 5.99 0.00
Account2 0.00 0.00
Account3 0.00 0.00
Account4 2.00 2.00
When I tried to create the query I used a left join to output the Account names as shown above but I can't seem to pattern the amounts properly. Will I have to use the pivot function to get this result ?
Thanks again
|
|
|
nathans
Aged Yak Warrior
USA
933 Posts |
Posted - 08/24/2010 : 02:27:10
|
Again, your test data looks a little off... heres what I think youre after:
select a.AccountName, sum(isnull(d.Amt2,0)), sum(isnull(d.Amt3,0))
from AccountNames a
left
join ( select account,
case when t=2 then amt else 0 end,
case when t=3 then amt else 0 end
from ( select '2' [t], SiteNbr, Account1, Account2, Account3, Account4 from Table2 union all
select '3', SiteNbr, Account1, Account2, Account3, Account4 from Table3
) as [p]
unpivot ( Amt for Account in (Account1, Account2, Account3, Account4)
)as [u]
) d (Account, Amt2, Amt3) on
a.AccountName = d.Account
group
by AccountName
Nathan Skerl |
 |
|
|
rc1138
Starting Member
Canada
35 Posts |
Posted - 08/24/2010 : 22:37:47
|
Thanks Nathan!
That definately worked
Now one last question I need to add 2 more columns to this query but the information unfortunately comes from 4 other source tables
(Webform input :( )
It will have the same table structures as above
Table 4
Design
Id Int
SiteNbr Int
Account1 Numeric(9,2)
Account2 Numeric(9,2)
Account3 Numeric(9,2)
Account4 Numeric(9,2)
Id SiteNbr Account1 Account2 Account3 Account4
1 3145 null null null 2.00
Table 5
Design
Id Int
SiteNbr Int
Account1 Numeric(9,2)
Account2 Numeric(9,2)
Account3 Numeric(9,2)
Account4 Numeric(9,2)
Id SiteNbr Account1 Account2 Account3 Account4
1 3145 12.00 null null null
Table 6
Design
Id Int
SiteNbr Int
Account1 Numeric(9,2)
Account2 Numeric(9,2)
Account3 Numeric(9,2)
Account4 Numeric(9,2)
Id SiteNbr Account1 Account2 Account3 Account4
1 3145 5.99 null null 2.00
Table 7
Design
Id Int
SiteNbr Int
Account1 Numeric(9,2)
Account2 Numeric(9,2)
Account3 Numeric(9,2)
Account4 Numeric(9,2)
Id SiteNbr Account1 Account2 Account3 Account4
1 3145 null 3.45 null 2.00
Table 4 and 5 should be one column as well as 6 and 7
Is it possible to add the amounts from 4 and 5 (same account numbers) as well as 6 and 7 ?
The output should be similar to the earlier output but with the 2 columns added
Account Name Budget1 Budget1Spent Budget2 Budget2Spent
Account1 5.99 12.00 0.00 5.99
Account2 0.00 0.00 0.00 3.45
Account3 0.00 0.00 0.00 0.00
Account4 2.00 2.00 2.00 4.00
Thanks again!
|
|
|
|
nathans
Aged Yak Warrior
USA
933 Posts |
Posted - 08/25/2010 : 18:44:40
|
Try adding the columns using my query as a template. Follow what I did with tables 1 & 2. Your new tables can work the same way. Post back here if you get stuck.
- n |
Edited by - nathans on 08/25/2010 18:45:05 |
|
|
| |
Topic |
|
|
|
Vertical Query Output
