Removing Nulls and Reordering sequentially 2

[HoustonGuy]

I have a table with names and sequences that was loaded with some null values.
The table is larger than this example, but this is the erratic Null pattern that can occur.

Example:
Name Sequence
------------------
John 1
Null 2
Null 3
Bill 4
Null 5
Tom 6
Jim 7

I need to remove the nulls and move the names into those spots so that the nulls are at the end.
The Null sequence positions are not predictable.

Name Sequence
------------------
John 1
Bill 2
Tom 3
Jim 4
Null 5
Null 6
Null 7

Any ideas on the best way to do this?
Thanks in advance!

 

[drlex]

If you're using SQL server 2005 and above, I'd have thought that the [red]row_number[/red] function would assist here, sorting the non-null names using the existing sequence.

Possible solution, using common table expressions ans assuming source table is "nametable"

with tablesort as (select [name], sequence, row_number() over (order by sequence) as newsequence
from nametable where [name] is not null)
,
newnumbers as 
(select nametable.sequence, coalesce(newsequence, row_number() over (order by newsequence desc)) as newsequence from nametable left outer join tablesort on nametable.sequence = tablesort.sequence)

Update nametable
Set sequence = newsequence from nametable inner join newnumbers on nametable.sequence = newnumbers.sequence

select * from nametable order by sequence --check results

tablesort comprises an ordered list of only the rows with content; table newnumbers adds in new sequence numbers for the null rows, found with a left outer join to tablesort.

The "order by" in newnumbers is descending, so that the NULL content "name" field rows are after the rows with names.

(Warning - with an Update statement, please test first on development/test environment)



soi là, soi carré

 

[imex]

Try:

with CTE_R as
(
    select 
        *,
        ROW_NUMBER() OVER(ORDER BY case when Sequence is null then 2 else 1 end, Sequence) as NewSequence
    from MyTable
)

update CTE_R
set Sequence = NewSequence

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[drlex]

imex,
That's a more elegant solution, but may I offer this amended version?

with CTE_R as
(
    select 
        *,
        ROW_NUMBER() OVER(ORDER BY case when [red][b][Name][/b][/red] is null then 2 else 1 end, Sequence) as NewSequence
    from nameTable
)

update [red][b]nametable[/b][/red]
set Sequence = NewSequence [b][red]from nametable join CTE_R on nametable.sequence = CTE_R.sequence[/red][/b]

[i]soi là, soi carré[/i]

 

[HoustonGuy]

Much thanks for the responses, but I gave an improper example. I apologize for that! (16 hour day.) :-(

The data is flat:

StoreNo   Field1   Field2    Field3   Field4   Field5    Field6     Field 7  etc...
123456    John      Null      Null     Bill    Null       Tom        Jim
etc...

I don't have anything that justifies the values yet - maybe nested case statements.
Any ideas would be great - sorry for the incorrect scenario.

 

[TamarGranor]

The right answer here is that anytime you start having fields with numbers at the end, you really need another table. Each record would represent one of those fields for one of those rows. That is, in this case, the structure would be:

StoreNo
Name

and maybe another field that indicates which column it came from, if that's significant.

Tamar

 

[drlex]

I'm sure we've all worked with tables that are inefficient/unsuitable for present needs, but can't change structures. In HoustonGuy's situation, an unpivoting operation would allow the use of the row_numbering method already suggested.

Using the second example data set, and modifying the last code suggested:

with [testdata] as
(
SELECT 123456 as StoreNo, 'John' as Field1, NULL as Field2,
NULL as Field3, 'Bill' as Field4, NULL as Field5, 
'Tom' as Field6, 'Jim' as Field7
)

,[unpivoted] as
(Select StoreNo, 1 as [sequence], Field1 as [name] from [testdata]
UNION ALL
Select StoreNo, 2 as [sequence], Field2 as [name] from [testdata]
UNION ALL
Select StoreNo, 3 as [sequence], Field3 as [name] from [testdata]
UNION ALL
Select StoreNo, 4 as [sequence], Field4 as [name] from [testdata]
UNION ALL
Select StoreNo, 5 as [sequence], Field5 as [name] from [testdata]
UNION ALL
Select StoreNo, 6 as [sequence], Field6 as [name] from [testdata]
UNION ALL
Select StoreNo, 7 as [sequence], Field7 as [name] from [testdata]
)

,CTE_R as
 (
 select 
*,
 ROW_NUMBER() OVER(ORDER BY case when [Name] is null then 2 else 1 end, Sequence) as NewSequence
 from [unpivoted]
 )
 
 select * from CTE_R

soi là, soi carré