SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Vertical Query Output
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rc1138
Starting Member

Canada
35 Posts

Posted - 08/23/2010 :  21:43:45  Show Profile  Reply with Quote
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
938 Posts

Posted - 08/24/2010 :  02:27:10  Show Profile  Reply with Quote
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

Canada
35 Posts

Posted - 08/24/2010 :  22:37:47  Show Profile  Reply with Quote
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

USA
938 Posts

Posted - 08/25/2010 :  18:44:40  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000