Posted by Thomas R. Hummel on 09/30/59 11:52

Hello,

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:

CREATE TABLE dbo.Main_Table (
my_id INT NOT NULL,
line_number SMALLINT NOT NULL,
some_other_columns VARCHAR(20) NULL )
GO

ALTER TABLE dbo.Main_Table
ADD CONSTRAINT PK_Main_Table PRIMARY KEY CLUSTERED (my_id, line_number)
GO

CREATE TABLE dbo.Related_Table (
my_id INT NOT NULL,
my_code CHAR(6) NOT NULL )
GO

ALTER TABLE dbo.Related_Table
ADD CONSTRAINT PK_Related_Table PRIMARY KEY CLUSTERED (my_id, my_code)
GO

-- The two tables above are related by my_id

INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (1, 1)
INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (1, 2)
INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (1, 3)
INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (2, 1)
INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (2, 2)

INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (1, '22.63')
INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (1, '73.09')
INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (1, '51.23')
INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (2, '26.42')
GO

The results that they would want to see are:

my_id line_number my_code_1 my_code_2 my_code_3 my_code_4
1 1 22.63 73.09 51.23 NULL
1 2 22.63 73.09 51.23 NULL
1 3 22.63 73.09 51.23 NULL
2 1 26.42 NULL NULL NULL
2 2 26.42 NULL NULL NULL

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).

Any thoughts?

Thanks!
-Tom.

[Back to original message]