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
 General SQL Server Forums
 New to SQL Server Programming
 Vertical Query Output

Author  Topic 

rc1138
Starting Member

35 Posts

Posted - 2010-08-23 : 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

938 Posts

Posted - 2010-08-24 : 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
Go to Top of Page

rc1138
Starting Member

35 Posts

Posted - 2010-08-24 : 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!



Go to Top of Page

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-08-25 : 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
Go to Top of Page
   

- Advertisement -