joining 70 tables - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

joining 70 tables

Author

Topic

clement.store
Starting Member

25 Posts

Posted - 2008-12-12 : 03:59:58

Dear all,

I would like to do a consolidation reports based on more than 70 tables with same structure. I would like to consolidate tables tbla, tblb, tblc which hae different account codes and values as seen in the diagram. And the desired result is ResTbl where amount fields of different tables are added together.

I wonder what is the most efficient way to code the consol. report of this kind ( best with sample coding)?

Thanks so much.

Clement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-12 : 04:02:50

[code]SELECT accnt_code,
sum(amount) AS amount
FROM (
select accnt_code, amount from tbla union all
select accnt_code, amount from tblb union all
select accnt_code, amount from tblc
) AS d
group by accnt_code[/code]

E 12°55'05.63"
N 56°04'39.26"

clement.store
Starting Member

25 Posts

Posted - 2008-12-12 : 04:24:31

cheers Peso! If I have a table listing that has all the table names in it. Could I do it using a loop in T-SQL so that I dont need to code 70 selct statements? say the table is called mastable and the field with table name listing is mastable.compname

THanks!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-12 : 04:27:19

What!?
Wicky design...

But, yes, you can manage this with dynamic SQL.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-12 : 04:33:19

[code]-- Prepare sample data
CREATE TABLE #Sample
(
tableName SYSNAME
)

INSERT #Sample
SELECT 'tblA' UNION ALL
SELECT 'tblB' UNION ALL
SELECT 'tblC'

-- Here starts the solution
DECLARE @sql VARCHAR(MAX)

SELECT @sql = COALESCE(@sql, '') + ' UNION ALL SELECT accnt_code, amount FROM ' + QUOTENAME(tableName)
FROM #Sample

SET @sql = 'INSERT ResTbl (accnt_code, amount) SELECT accnt_code, SUM(amount) FROM ('
+ SUBSTRING(@sql, 12, DATALENGTH(@sql))
+ ') AS d GROUP BY accnt_code'

EXEC (@sql)

-- Clean up sample data
DROP TABLE #Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources