Stair data in Table - 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 2000 Forums

Transact-SQL (2000)

Stair data in Table

Author

Topic

Pumkin
Starting Member

20 Posts

Posted - 2005-12-27 : 05:50:05

Hi everybody,
I have a table like this :

One NULL
One NULL
One NULL
NULL Two
NULL Two
NULL Two

Does anybody knows how I can obtain :

One Two
One Two
One Two
???

Thank you very much in advanced.
PS. Happy holydays

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-12-27 : 07:30:44

Just an Idea!

Hope it helps

Create table test

(
a varchar(20),
b varchar(20)
)

Insert into test(A,B) values ('One',null)
Insert into test(A,B) values ('One',null)
Insert into test(A,B) values ('One',null)

Insert into test(A,B) values (null,'Two')
Insert into test(A,B) values (null,'Two')
Insert into test(A,B) values (null,'Two')

Select top 3 A.a + isnull(B.A,''), B.b + isnull(A.b,'') from
(Select * from TEST where B is null) a,
(Select * from TEST where A is null) b

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

Pumkin
Starting Member

20 Posts

Posted - 2005-12-27 : 07:42:34

Thank you very much for the idea. Unfortunatelly, I cannot constrain the number of rows. That was just an example. I really don't know the number of rows.
I will try to play a little bit with your suggestion.
Thak you again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-27 : 07:58:00

Why do you need this?
Where do you want to show these?
If the table has identity or primary column then you can get desired result


Declare @t table(id int identity(1,1),col1 varchar(10), col2 varchar(10))
Insert into @t 
Select 'one' as col1, Null as col2
Union all
Select 'one' as col1, Null as col2
Union all
Select 'one' as col1, Null as col2
Union all
Select 'one' as col1, Null as col2
Union all
Select Null as col1, 'Two' as col2
Union all
Select Null as col1, 'Two' as col2
Union all
Select Null as col1, 'Two' as col2
Union all
Select Null as col1, 'Two' as col2

--sachinsamuel method
Select distinct A.id,A.col1 + isnull(B.col1,'') as col1, B.col2 + isnull(A.col2,'') as col2 from 
(Select * from @t where col2 is null) a, 
(Select * from @t where col1 is null) b

--Other method
select  distinct t1.id,t1.col1,t2.col2 from @t t1 cross join @t t2
where t1.col1 is not null and t2.col2 is not null

Madhivanan

Failing to plan is Planning to fail

Pumkin
Starting Member

20 Posts

Posted - 2005-12-27 : 08:28:29

I have a dynamically generated table and the data is coming in that format. Only that I need it in a normal format to work with it. I can put a primary key on my table.
Thanks for the idea. I'm trying it now.

Pumkin
Starting Member

20 Posts

Posted - 2005-12-27 : 08:35:04

Yeeeei...It works. Thak you very much.
I completely forgot about cross join.
Thank you again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources