How do I do this? (It's hard) - 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)

How do I do this? (It's hard)

Author

Topic

AsherSQL
Starting Member

3 Posts

Posted - 2010-01-26 : 17:51:37

Sorry for the vague title, but my question is too complicated to fit into the subject field. Essentially, what I want to do is find a way to take several different 'results' of the same query (just with a different date filter) and then 'mash' them all into the same table (by adding the data as COLUMNS, not rows). I don't even know where to begin doing this, but if I can figure it out I'll be a lot more productive. Here's what I'm trying to do:

Table 1 has Columns (A1, B1, C1) Table 2 has Columns (A2, B2, C2) Table 3 has Columns (A3, B3, C3) and the 'Result' set has Columns (A1, B1, C1, A2, B2, C2, A3, B3, C3)

The reason why I want to figure this out is that I'm a data miner and run custom reports for the marketing department that involve running the same query (meaning same fundamental list of column definitions) over and over again with a different filter (date range, sales category, etc) placed over the same source set of data that need to be lined up column-to-column for comparison. I can effectively accomplish this by using a looped select statement and then format the result sets with excel, or by creating a HUGE hard-coded (meaning useless in the future) query that can produce the results I need. My current strategy is to join temporary tables, but the scope of my projects is advancing to the point where manually defining and filling ~30 temp tables is an ineffective use of time (assuming, like I do, that there MUST be a better way).

For the life of me I cannot figure out how to do this, but I think the solution would include a loop, some dynamic sql, and a function that returns a table.

Can anyone help?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-26 : 18:15:29

I'm not sure this is any better than what you are doing, but maybe you can use UNION to combine the results?For example:

SELECT 
	MAX(A1) AS A1,
	MAX(B1) AS B1,
	MAX(A2) AS A2,
	MAX(B2) AS B2
FROM
(
	SELECT A1, B1, NULL AS A2, NULL AS B2
	FROM Table1

	UNION ALL

	SELECT NULL, NULL, A2, B2
	FROM Table2
) AS D

AsherSQL
Starting Member

3 Posts

Posted - 2010-01-26 : 19:19:40

Heh, unfortunately it turns out that UNION only slaps the data as rows on the bottom of the table, for a second there I thought my problem was solved!

I also forgot to mention one of the real kickers: I'd need to be able to do this without knowing how many columns the result set will have - the report (and most other future reports) I'm generating has a variable date range, and thus a variable total number of columns. Quite the pickle, eh?

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 04:20:04

"unfortunately it turns out that UNION only slaps the data as rows on the bottom of the table"

Do you mean that if there is an ID in Table1 and the same ID in Table2 you want them on a single row, rather than two rows?

If so you need a FULL OUTER JOIN between the two tables.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-01-27 : 07:45:55

Perhaps a very small sample of what you are doing would help?
Does this look anything like what you have to deal with?
http://www.mssqltips.com/tip.asp?tip=1019

If not, then follow the "How to ask" link in my signature so you can give us some DDL and DML.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-27 : 08:39:52

Can you not put a Row_Number() function on each generated table and join on that? (you would need some dynamic SQL to generate the variable table amounts)

But having something like table 1 (A1, B1, C1, RN1) table 2 (A2, B2, C2, RN2) etc....

Select t1.*, t2.*, ... From t1 Left outer join t2 on RN1 = RN2 Left Outer join...etc...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources