How to remove the all rows before first occurance

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)

How to remove the all rows before first occurance

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2010-05-11 : 16:07:36

in my sql table i have 8 columns

1) please tell me, how can i delete all previous rows, of all first occurance of all null values coulmns

for example
-------------
select * from table1

col1 col2 col3 col4 col5
---- ---- ---- ---- ----
AA1 NULL AA3 AA4 AA5
AA1 AA2 AA3 AA4 NULL
AA1 AA2 NULL NULL NULL
AA1 AA2 NULL AA4 AA5
AA1 AA2 AA3 AA4 AA5
AA1 AA2 AA3 AA4 AA5
NULL NULL NULL NULL NULL
BB1 BB2 BB3 BB4 BB5
BB1 BB2 BB3 BB4 BB5
BB1 BB2 BB3 BB4 BB5
NULL NULL NULL NULL NULL
CC1 CC2 CC3 CC4 CC5
CC1 CC2 CC3 CC4 CC5

so as of above illustration how can i remove upto 7 th column (because that is first occurance of all null values row)

2) in my sql table column (it is actually loads from excel file to sql table through ssis) i have values like this

01/10
01/27/10
02/24/10-02
12/24/09 +02/10/10

how can i convert any type of value to MMM/YYYY
so for example the above ones need to look like as
Jan/2010
Jan/2010
Feb/2010
Dec/2009

please help me

Thanks in advance
Dhani

dhani
Posting Yak Master

132 Posts

Posted - 2010-05-11 : 17:38:51

First One solved with the help of Naom

here it is the solution, may useful to some others

declare @t table (col1 char(3),col2 char(3), col3 char(3), col4 char(3), col5 char(3))

insert into @t
select
'AA1', NULL, 'AA3', 'AA4', 'AA5'
union all select
'AA1', 'AA2', 'AA3', 'AA4', NULL
union all select
'AA1', 'AA2', null, null, null
union all select
'AA1', 'AA2', NULL, 'AA4', 'AA5'
union all select
NULL, NULL, NULL, NULL, NULL
union all select
'BB1', 'BB2', 'BB3', 'BB4', 'BB5'
union all select
'BB1', 'BB2', 'BB3', 'BB4', 'BB5'
union all select
'BB1', 'BB2', 'BB3', 'BB4', 'BB5'
union all select
NULL, NULL, NULL, NULL, NULL
union all select
'CC1', 'CC2', 'CC3', 'CC4', 'CC5'
union all select
'CC1', 'CC2', 'CC3', 'CC4', 'CC5'

;with cte as (select *,
case when col1 IS NULL and Col2 is NULL and col3 is null
and col4 is null and col5 is null then 1 else 10000000000 end * ROW_NUMBER() over (order by (select null)) as Row from @t)
delete from cte where Row < (select MIN(Row) * 10000000000 as MinNulls from cte) and Row >=10000000000

select * from @t

Thanks

please help me in regards to 2nd date column format please................

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-12 : 05:59:19

Where do you want to show formatted dates?
If you use front end application, do formation there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources