How to get the first column that is not null

Sorry if the title is bad. But I do not know how to address the problem. I've been really struggling with a query in sql. I do not know if this question has been asked before. But I need to get the first column that is not null. I have a table like this:

ID 0 1 2 3 4 5 6 7 8 9 59 NULL NULL NULL NULL NULL NULL NULL NULL NULL text1 185 NULL NULL NULL NULL NULL text1 text2 text3 text4 text5 428 NULL NULL NULL NULL NULL NULL text1 text2 text3 text4 53 NULL NULL NULL NULL NULL NULL NULL NULL text1 text2 452 NULL NULL NULL NULL NULL NULL NULL NULL NULL text1 267 text1 text2 text3 text4 text5 text6 text7 text8 text9 text10

And I would like the output to be like this:

ID 0 1 2 3 4 5 6 7 8 9 59 text1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 185 text1 text2 text3 text4 text5 NULL NULL NULL NULL NULL 428 text1 text2 text3 text4 NULL NULL NULL NULL NULL NULL 53 text1 text2 NULL NULL NULL NULL NULL NULL NULL NULL 452 text1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 267 text1 text2 text3 text4 text5 text6 text7 text8 text9 text10

The ID column is unique per row.

My failed attempt was to create a scalared function and run it for each column. The function looks like this:

CREATE FUNCTION GetCulumnsThatIsNotNull ( @column1 VARCHAR(500), @column2 VARCHAR(500), @column3 VARCHAR(500), @column4 VARCHAR(500), @column5 VARCHAR(500), @column6 VARCHAR(500), @column7 VARCHAR(500), @column8 VARCHAR(500), @column9 VARCHAR(500), @column10 VARCHAR(500) ) RETURNS VARCHAR(500) AS BEGIN RETURN COALESCE(@column1, COALESCE(@column2, COALESCE(@column3, COALESCE(@column4, COALESCE(@column5, COALESCE(@column6, COALESCE(@column8, COALESCE(@column9, COALESCE(@column10,null))))))))) END

But the problem with this function is that it is just picking the first column that are null. So the result get like this:

ID 0 1 2 3 4 5 6 7 8 9 59 text1 text1 text1 text1 text1 text1 text1 text1 text1 text1 185 text1 text1 text1 text1 text1 text1 text1 text1 text1 text1 428 text1 text1 text1 text1 text1 text1 text1 text1 text1 text1 53 text1 text1 text1 text1 text1 text1 text1 text1 text1 text1 452 text1 text1 text1 text1 text1 text1 text1 text1 text1 text1 267 text1 text1 text1 text1 text1 text1 text1 text1 text1 text1

Any suggestions?

EDIT

This will not work ether.

COALESCE(pvt.[0], pvt.[1], pvt.[2], pvt.[3], pvt.[4], pvt.[5], pvt.[6], pvt.[7], pvt.[8], pvt.[9])

Because it will result in the same output

-------------Problems Reply------------

I would normalize the data in a view or CTE or something, then run an ordinary pivot. This way, should you ever actually normalize the table, you'll be able to reuse code.

-- Setup test data
declare @texts table (
ID int not null primary key
, Col0 varchar(10) null
, Col1 varchar(10) null
, Col2 varchar(10) null
, Col3 varchar(10) null
, Col4 varchar(10) null
, Col5 varchar(10) null
, Col6 varchar(10) null
, Col7 varchar(10) null
, Col8 varchar(10) null
, Col9 varchar(10) null
)
insert into @texts select 59, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'text1'
insert into @texts select 185, NULL, NULL, NULL, NULL, NULL, 'text1', 'text2', 'text3', 'text4', 'text5'
insert into @texts select 428, NULL, NULL, NULL, NULL, NULL, NULL, 'text1', 'text2', 'text3', 'text4'
insert into @texts select 53, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'text1', 'text2'
insert into @texts select 452, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'text1'
insert into @texts select 267, 'text1', 'text2', 'text3', 'text4', 'text5', 'text6', 'text7', 'text8', 'text9', 'text10'

-- Normalize and get new column in a CTE
;with cte as (
select *
, row_number() over (partition by ID order by Col) - 1 as NewCol
from (
select ID, 0 as Col
, (select Col0 from @texts where ID = a.ID) as Val
from @texts a
union all
select ID, 1 as Col
, (select Col1 from @texts where ID = a.ID) as Val
from @texts a
union all
select ID, 2 as Col
, (select Col2 from @texts where ID = a.ID) as Val
from @texts a
union all
select ID, 3 as Col
, (select Col3 from @texts where ID = a.ID) as Val
from @texts a
union all
select ID, 4 as Col
, (select Col4 from @texts where ID = a.ID) as Val
from @texts a
union all
select ID, 5 as Col
, (select Col5 from @texts where ID = a.ID) as Val
from @texts a
union all
select ID, 6 as Col
, (select Col6 from @texts where ID = a.ID) as Val
from @texts a
union all
select ID, 7 as Col
, (select Col7 from @texts where ID = a.ID) as Val
from @texts a
union all
select ID, 8 as Col
, (select Col8 from @texts where ID = a.ID) as Val
from @texts a
union all
select ID, 9 as Col
, (select Col9 from @texts where ID = a.ID) as Val
from @texts a
) as b
where b.Val is not null
)
-- Run a pivot of the CTE
select ID
, (select Val from cte where ID = a.ID and NewCol = 0) as Col0
, (select Val from cte where ID = a.ID and NewCol = 1) as Col1
, (select Val from cte where ID = a.ID and NewCol = 2) as Col2
, (select Val from cte where ID = a.ID and NewCol = 3) as Col3
, (select Val from cte where ID = a.ID and NewCol = 4) as Col4
, (select Val from cte where ID = a.ID and NewCol = 5) as Col5
, (select Val from cte where ID = a.ID and NewCol = 6) as Col6
, (select Val from cte where ID = a.ID and NewCol = 7) as Col7
, (select Val from cte where ID = a.ID and NewCol = 8) as Col8
, (select Val from cte where ID = a.ID and NewCol = 9) as Col9
from cte a
group by ID
order by ID

select *
from
(
select T.ID, C.C, row_number() over(partition by T.ID order by C.N) - 1 as rn
from @T as T
cross apply (values (C0, 0),(C1, 1),(C2, 2),(C3, 3),(C4, 4),
(C5, 5),(C6, 6),(C7, 7),(C8, 8),(C9, 9)) as C(C, N)
where C.C is not null
) as T
pivot
(
min(C) for rn in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9])
) as P

Try on SE-Data

Category:tsql Views:0 Time:2012-04-12

Related post

  • NHibernate: Column does not allow nulls. INSERT fails 2009-02-26

    I have 2 entities Person and Address, Person has one Address. EDIT: The Address already exists, Im just wanting to save the foreign key. When I do this: PersonDTO person = new PersonDTO(); person.Age = "Bob"; person.Address = new AddressDTO {Key = 12

  • MySql Says Column can't be null for a column that is not null! [using named parameters] 2009-08-07

    I am trying to execute an INSERT INTO query using .Net through the MySql/.NEt connector. The query makes uses of Parameters. It is rather simple: INSERT INTO post ( ID, content, post_url, blogID, title, addedOn, updatedDate, commentsFeedURL, active,

  • testing inequality with columns that can be null 2009-12-02

    So, I asked a question this morning, which I did not phrase correctly, so I got a lot of responses as to why NULL compared to anything will give NULL/FALSE. My actual question was, what is the time honored fashion in which db guys test inequalities f

  • Return value of stored procedure is correct but column values are all NULL 2009-12-10

    I'm working with the C# membership provider and transferring it to LINQ along the way. I'm having trouble pulling the results from a stored procedure in MS SQL. The procedure does some work to set variables and then selects the variables before setti

  • Can a cast a nullable column to a NOT NULL column? 2009-12-28

    In SQL 2005/8 I'm looking to convert a column in select query to a NOT NULL column. coalease() and isnull(), although fine functions, is not what I'm looking for. I want the select to throw an error is any of the data is NULL. Is this possible? [Upda

  • How to solve "Cannot add a NOT NULL column with default value NULL" in SQLite3? 2010-07-03

    I am getting the following error while trying to add a NOT NULL column to an existing table. Why is it happening ?. I tried rake db:reset thinking that the existing records are the problem, but even after resetting the DB, the problem persists. Can y

  • How to check if a column is empty or null using SQL query select statement? 2011-01-24

    How do I check if a column is empty or null using a SQL select statement? For instance, if I want to check: select * from UserProfile WHERE PropertydefinitionID in (40, 53) and PropertyValue is null or empty --------------Solutions------------- Does

  • JPA composite key issue: Column 'Person_ID' cannot be null 2011-02-11

    this is hurdle number 43 tonight in trying to get JPA/Hibernate working in a new project. When trying to create and then persist my Staffer class, I get: SEVERE: Column 'Person_ID' cannot be null SEVERE: Could not synchronize database state with sess

  • Column does not allow nulls 2011-03-14

    Please have a look at my code below and tell me where i am going wrong. case "particulars.aspx": dt = JobCardManager.GetParticularsByJobId(id); hidJobId.Value = id.ToString(); if (dt != null && dt.Rows.Count > 0) { gvParticulars.DataSource

  • Integrity constraint violation: 1048 Column 'url_id' cannot be null 2011-05-05

    I am getting this error in Zend Framework: Integrity constraint violation: 1048 Column 'url_id' cannot be null My code is: $stmt = $this->select()->where('url_address = ?', $url_address)->query(); $r = $stmt->fetchAll(); print_r($r); if(c

  • How to query a column which value is null in JPA? 2011-06-12

    I am using JPA namedQuery to select data from DB. @NamedQuery(name = "Concept.findByRefTableNull", query = "SELECT c FROM Concept c WHERE c.conceptName = :conceptName and c.refTable = :refTable"), /// List<Concept> attributeList = em.createName

  • Django Column 'id' cannot be null 2011-08-12

    I am having a weird problem with my mysql database and django. I created an app with a model imported from an existing database with inspectdb. It was working fine until yesterday I removed the django automatically created tables (django_, auth_, sit

  • MySQL ERROR: Column 'Time' cannot be null 2011-08-19

    I get the error: Column 'Time' cannot be null when using the query below, it works fine the first time when there is no duplicate but then when trying to update again I get the error: Column 'Time' cannot be null mysql_query(" INSERT INTO $table(User

  • Is it possible to put a constraint on a table whereas a column can't be null if another specified column is not null? 2011-10-06

    I have two columns on a table I am concerned with. If both columns are null, that's ok. If they are both not null, that's ok. But if column one is not null, column two can't be null. Is there a way to add this type of constraint? --------------Soluti

  • VB.net Error 'Argument Null Exception was unhandled, column argument cannot be null' 2011-10-20

    I am trying to create a treeview in VB.net, the data has to be loaded from MSAccess 2010 database. When I try to run this program I get error : Argument Null Exception was unhandled, 'column' argument cannot be null and the program crashes. Here is t

  • How do I check if a column is empty or null in mysql 2011-12-12

    I have a column in a table which might contain null or empty values. How do I check if a column is empty or null in the rows present in a table. (e.g. null or '' or ' ' or ' ' and ...) --------------Solutions------------- This will select all rows wh

  • `execute_non_query': Cannot add a NOT NULL column with default value NULL (DataObjects::SyntaxError) 2012-03-24

    class User include DataMapper::Resource property :id, Serial property :name, String property :email, String has n, :records end class Project include DataMapper::Resource property :id, Serial property :name, String has n, :records ????? end # class R

  • Can't assign id, "Column 'id' cannot be null" 2012-08-30

    I inherited a database using Rails that doesn't set id incrementally, and also has two primary keys: CREATE TABLE `t_user_history` ( `id` int(11) NOT NULL, `history_no` int(11) NOT NULL, `user_login_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL, `

  • Is it possible to customize error message "Column does not allow nulls" in a datatable? 2013-11-18

    I have a form with controls bound to a datatable in VB.net. When keeping empty a field that should be filled, I'm receiving the error message : Column does not allow nulls. Is it possible to replace this error message string by another one ? --------

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.094 (s). 11 q(s)