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.
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 outputI have 3 tablesFirst tableName: Table1AccountNamesDesign AccountName varchar(50)AccountNameAccount1Account2Account3Account4Second TableName: Table2DesignId IntSiteNbr IntAccount1 Numeric(9,2)Account2 Numeric(9,2)Account3 Numeric(9,2)Account4 Numeric(9,2)Id SiteNbr Account1 Account2 Account3 Account41 3145 5.99 null null 2.00Third TableName: Table3DesignId IntSiteNbr IntAccount1 Numeric(9,2)Account2 Numeric(9,2)Account3 Numeric(9,2)Account4 Numeric(9,2)Id SiteNbr Account1 Account2 Account3 Account41 3145 null 3.50 null nullThe output should beAccount Name Budget1 Budget2Account1 5.99 0.00Account2 0.00 0.00Account3 0.00 0.00Account4 2.00 2.00When 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 aleftjoin ( 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.Accountgroupby AccountName Nathan Skerl |
|
|
rc1138
Starting Member
35 Posts |
Posted - 2010-08-24 : 22:37:47
|
Thanks Nathan!That definately workedNow 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 aboveTable 4DesignId IntSiteNbr IntAccount1 Numeric(9,2)Account2 Numeric(9,2)Account3 Numeric(9,2)Account4 Numeric(9,2)Id SiteNbr Account1 Account2 Account3 Account41 3145 null null null 2.00Table 5DesignId IntSiteNbr IntAccount1 Numeric(9,2)Account2 Numeric(9,2)Account3 Numeric(9,2)Account4 Numeric(9,2)Id SiteNbr Account1 Account2 Account3 Account41 3145 12.00 null null nullTable 6DesignId IntSiteNbr IntAccount1 Numeric(9,2)Account2 Numeric(9,2)Account3 Numeric(9,2)Account4 Numeric(9,2)Id SiteNbr Account1 Account2 Account3 Account41 3145 5.99 null null 2.00Table 7DesignId IntSiteNbr IntAccount1 Numeric(9,2)Account2 Numeric(9,2)Account3 Numeric(9,2)Account4 Numeric(9,2)Id SiteNbr Account1 Account2 Account3 Account41 3145 null 3.45 null 2.00Table 4 and 5 should be one column as well as 6 and 7Is 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 Budget2SpentAccount1 5.99 12.00 0.00 5.99Account2 0.00 0.00 0.00 3.45Account3 0.00 0.00 0.00 0.00Account4 2.00 2.00 2.00 4.00Thanks again! |
|
|
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 |
|
|
|
|
|
|
|