Hi,
I have 3 tables with the follwing schema
Table <Category>
{
UniqueID,
LastDate DateTime
}
Assume the follwing tables with data following the above schema
Table Cat1
{
1, D1
2, D2
3, D3
}
Table Cat2
{
2, D4
3,D5
4, D6
}
Table Cat3
{
1, D7
3,D8
5,D9
}
I have a Master and the schema is as follows
Table master
{
UniqueId,
Cat1 DateTime, -- This is same as the Table name
Cat2 DateTime, -- This is same as the Table name
Cat3 DateTime -- This is same as the Table name
}
After inserting the data from all these 3 tables, I want the my master table to look like this
Table Master
{
UniqueId cat1 cat2 Cat3
- --
1 D1 NULL D7
2 D2 D4 NULL
3 D3 D5 D8
4 NULL D6 NULL
5 NULL NULL D9
}
Please remember the column names will be same as that of table names
can any one pelase let me know the query t o acheive this
Thanks for your quick response
~Mohan Babu
Try:
Code Snippet
insert into table_master([uniqueid], cat1, cat2, cat3)
select
[id],
[Cat1],
[Cat2],
[Cat3]
from
(
select [id], 'Cat1' as table_name, lastdate from dbo.Cat1
union all
select [id], 'Cat2' as table_name, lastdate from dbo.Cat2
union all
select [id], 'Cat3' as table_name, lastdate from dbo.Cat3
) as a
pivot
(
max(lastdate)
for table_name in ([Cat1], [Cat2], [Cat3])
) as pvt
go
AMB
|||Can you use full outter join style?
CREATE Table #Cat1
(
id int,
val varchar(10)
)
insert into #cat1 (id, val) values (1, 'D1')
insert into #cat1 (id, val) values (2, 'D2')
insert into #cat1 (id, val) values (3, 'D3')
CREATE Table #Cat2
(
id int,
val varchar(10)
)
insert into #cat2 (id, val) values (2, 'D4')
insert into #cat2 (id, val) values (3, 'D5')
insert into #cat2 (id, val) values (4, 'D6')
SELECT
CASE
WHEN C1.Id IS NOT NULL THEN C1.Id
WHEN C2.Id IS NOT NULL THEN C2.Id
ELSE NULL
END AS [Id],
C1.val,
C2.val
FROM #Cat1 c1
FULL OUTER JOIN #cat2 C2 ON
(C1.Id = C2.Id)
DROP TABLE #Cat1
DROP TABLE #Cat2
|||
Code Snippet
Create Table #cat1 (
id int,
[LastDate] datetime
);
Insert Into #cat1 Values('1','01/01/07');
Insert Into #cat1 Values('2','01/02/07');
Insert Into #cat1 Values('3','01/03/07');
Create Table #cat2 (
id int,
[LastDate] datetime
);
Insert Into #cat2 Values('2','01/02/07');
Insert Into #cat2 Values('3','01/03/07');
Insert Into #cat2 Values('4','01/04/07');
SELECT c.id, c.LastDate, d.LastDate FROM (SELECT a.id, b.LastDate FROM (SELECT id FROM #cat1
union
SELECT id FROM #cat2) a LEFT JOIN #cat1 b ON a.id=b.id) c LEFT JOIN #cat2 d ON c.id=d.id
|||Hi AMB and limon
Thank you bothe very much for your queries.
Both works perfect.
AMB: Can you pelase explain me why u have used Pivt and what is the impact of it in terms of performace and also can u please explain me why you have used max(lastUpdate) and what is the use of it
Limon : Though it works fine i feel the query is bit long.
My concern is that i may have N number of category tables and i have to merge them into a single table.
So i need to build the a query dynamically based on the number of the tables and merge them.
Can any one please explaine me which one executes faster as i might have millions of rows in the category table
Thanks for your time
~Mohan
|||Dave,
This query inserts null if the ID doesn't exist in botht the tables.
Thanks for your time
~Mohan
|||Hi mohandbabud,
why did I use pivot?
Because that is what you are doing. You are pivoting rows to columns. When using pivot operator, you need an aggregation function, and It does not make sense to use SUM, AVG, etc, with a [datetime] data type. you could use MIN also. BTW, I supposed that the [id] is unique in each category table.
About which one one will execute faster, I do not know, because both queries are doing heavy stuff. You can tell us when you have finished.
AMB
