|
|
Posted by Erland Sommarskog on 10/11/05 11:52
Thomas R. Hummel (tom_hummel@hotmail.com) writes:
> I am currently working with a data mart. The business wants a view of
> the data that shows columns from the main table along with up to 6
> codes from a related table (not related by the full PK of the main
> table though). They don't care about the order of the codes or how they
> are assigned to their columns. Here is an example, which will hopefully
> make this clearer:
>...
> I'm pretty sure that I will need to load a table with the data in this
> format (or something similar) since generating this output on the fly
> for the users will not work performance-wise (the main table is a
> partitioned table with 6 partitions, each containting 35M+ rows, while
> the related table is a single table that contains about 2M rows. There
> is additional logic that has to be done with a similar table of 90M
> rows. So, I will try to load a table with this denormalized view of the
> world. I could probably add some sort of sequence number to
> Related_Table if you think that might help (then it just becomes a
> simple series of left outer joins).
Without a sequence number in Related_Table it becomes a lot more difficult.
So I changed Related_Table to:
CREATE TABLE dbo.Related_Table (
my_id INT NOT NULL,
seq_no tinyint NOT NULL CHECK (seq_no BETWEEN 1 AND 4),
my_code CHAR(6) NOT NULL )
and produced this query (with a single LEFT JOIN):
SELECT M.my_id, M.line_number,
code1 = MIN(CASE R.seq_no WHEN 1 THEN R.my_code END),
code2 = MIN(CASE R.seq_no WHEN 2 THEN R.my_code END),
code3 = MIN(CASE R.seq_no WHEN 3 THEN R.my_code END),
code4 = MIN(CASE R.seq_no WHEN 4 THEN R.my_code END)
FROM Main_Table M
LEFT JOIN Related_Table R ON M.my_id = R.my_id
GROUP BY M.my_id, M.line_number
ORDER BY M.my_id, M.line_number
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|