select clause with very poorly designed db

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)

select clause with very poorly designed db

Author

Topic

Vaba_
Starting Member

2 Posts

Posted - 2007-08-13 : 07:25:09

I have a very poorly designed database (sorry, I can't redesign it) and I should get some information with sql.

Database has 5 tables and each of them has one column.
Each table has the same amount of rows.
I have a table which has same columns as those 5 tables.
Now I should get the information from those 5 tables into this one table.
The difficulty is that those 5 tables don't have anything that joins them together. Only thing is that they have the same amount of rows.

Example:


Table1:
column_name1
1
2
3

Table2:
column_name2
a
c
b

Table3:
column_name3
asd
sdf
dfg

Table4:
column_name4
1234
7654
9876

Table5:
column_name5
y
y
n

Result should be (table already exists, I should get the data):


My_Table:
column_name1  column_name2  column_name3  column_name4  column_name5
1             a             asd           1234          y
2             b             sdf           7654          y
3             c             dfg           9876          n

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-13 : 07:52:18

If the tables are in the order you want then you can do something like this, which gives you something to join on

select a.col1,b.col1,c.col1,d.col1,e.col1
from
(
select 'rownum' = row_number() over (order by col1),col1 from @table1
) a
inner join
(
select 'rownum' = row_number() over (order by col1),col1 from @table1
) b
on
a.rownum = b.rownum
inner join
(
select 'rownum' = row_number() over (order by col1),col1 from @table1
) c
on
a.rownum = c.rownum
inner join
(
select 'rownum' = row_number() over (order by col1),col1 from @table1
) d
on
a.rownum = d.rownum
inner join
(
select 'rownum' = row_number() over (order by col1),col1 from @table1
) e
on
a.rownum = e.rownum

or use the IDENTITY function similarly. But when you finish you will have now have 6 tables that don't link up instead of 5.

Jim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-13 : 07:58:36

[code]DECLARE @table1 TABLE
(
column_name1 int
)

INSERT INTO @table1 SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

DECLARE @table2 TABLE
(
column_name2 CHAR(1)
)

INSERT INTO @table2 SELECT 'a' UNION ALL SELECT 'c' UNION ALL SELECT 'b'

DECLARE @table3 TABLE
(
column_name3 varchar(3)
)
INSERT INTO @table3 SELECT 'asd' UNION ALL SELECT 'sdf' UNION ALL SELECT 'dfg'

DECLARE @table4 TABLE
(
column_name4 int
)
INSERT INTO @table4 SELECT 1234 UNION ALL SELECT 7654 UNION ALL SELECT 9876

DECLARE @table5 TABLE
(
column_name5 CHAR(1)
)
INSERT INTO @table5 SELECT 'y' UNION ALL SELECT 'y' UNION ALL SELECT 'n'

SELECT t1.column_name1, t2.column_name2, t3.column_name3, t4.column_name4, t5.column_name5
FROM (
SELECT row = row_number() OVER (ORDER BY column_name1),
column_name1
FROM @table1
) t1
INNER JOIN
(
SELECT row = row_number() OVER (ORDER BY column_name2),
column_name2
FROM @table2
) t2 ON t1.row = t2.row
INNER JOIN
(
SELECT row = row_number() OVER (ORDER BY column_name3),
column_name3
FROM @table3
) t3 ON t1.row = t3.row
INNER JOIN
(
SELECT row = row_number() OVER (ORDER BY column_name4),
column_name4
FROM @table4
) t4 ON t1.row = t4.row
INNER JOIN
(
SELECT row = row_number() OVER (ORDER BY column_name5 DESC),
column_name5
FROM @table5
) t5 ON t1.row = t5.row
/*
column_name1 column_name2 column_name3 column_name4 column_name5
------------ ------------ ------------ ------------ ------------
1 a asd 1234 y
2 b dfg 7654 y
3 c sdf 9876 n
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-13 : 07:59:22