locked
select all columns which are not null RRS feed

  • Question

  • I have table which have 60 columns.

    first row have null values in 32,44,55, columns

    second row have null values in 11,22,24 columns.....etc for all the other 600 rows.

    I need to select the data which do not have null values.

    result set should not display columns which are not null.

    Thanks in advance.

    Wednesday, August 22, 2012 1:59 AM

Answers

  • Aplologize to be late.

    I have Hardcoded The Query. I understood it is quite a huge task to dynamically select the columns which are not nulls.

    • Marked as answer by cpcudi Thursday, August 23, 2012 6:18 PM
    Thursday, August 23, 2012 6:18 PM

All replies

  • Could you explain with few sample rows of input and desired output?

    http://www.rad.pasfu.com

    Wednesday, August 22, 2012 2:11 AM
  • Thank you for Asking this Question.

    select * from table -- gives the following output

    server   col2   col3   col4   col5   col6    col7   col8    col9    col10

    1        1234    null    null   678    987   890   null     567      null

    1        4565    null    null    234      67   56    null     345      null

    2        null     null     578  567    234   null    73        18       null

    2       null      null    626   289     395  null    84        399     null

    3      567      null    845   null      987 674    null      null       648

    3      654      null     463   null     876   523   null      null      921

    Desired Output:

    select * from where server = 1 and _______________

    sample out put  Should look like this.

    server  col2      col5    col6    col7     col9

       1        1234    678    987    890      567

       1        4565    234       67      56     345

    Remember i have 60 columns.

    • Proposed as answer by Atul Utturkar Sunday, December 22, 2013 11:44 AM
    • Unproposed as answer by Atul Utturkar Sunday, December 22, 2013 11:45 AM
    Wednesday, August 22, 2012 4:21 AM
  • why you don't have col3, col4,col8 and col10 in desired output?

    do you just check null values in first record?

    I am asking because Col4 for example have values in other rows than first and second, so I think it might appear in output, or if not what is the logic?


    http://www.rad.pasfu.com

    Wednesday, August 22, 2012 6:01 AM
  • Greetings Cpcudi,

    I was pondering on the issue but i can find quite a few work around to achieve what you want, as i understand you want a dataset over multiple 'servers'.
    All dirty as hell, and i would rather suggest you change the table-layout to reflect a database design.

    Anyways the possibility i would suggest is:
    View - Server1 (Select Col2, Col5, Col6, Col7, Col9 FROM table where Server = 1)
    View - Server2 (select Col4, Col5, Col6, Col8, Col9 FROM table where Server = 1)

    View - Servers(Select * from Server1 UNION Select * from Server2);


    Wednesday, August 22, 2012 7:36 AM
  • Sebastian

    What if tomorrow someone insert/update the col2 value to NULL , you will have to exclude that column from the view....

    cpcudi

    Your request is strange...It is impossible to solve by simple T-SQL , especially if  the data have changed frequently....


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Wednesday, August 22, 2012 8:26 AM
  • Hi cpcudi,

    A possible solution is to first unpivot the data to a dataset with three column: server column value

    After that you can filter out the null valued rows with a conditional split. And then pivot that data back to the desired data structure.


    -- JP [Inter Access] (As stated by multiple people: please vote/mark as aswer)

    Wednesday, August 22, 2012 9:30 AM
  • Its not possible to dynamically remove columns per row in a dataset. Alternatively You can push all your data into a staging table and then use multiple "Execute SQL task" to achieve some custom processing per server.

    http://btsbee.wordpress.com/

    Wednesday, August 22, 2012 11:07 AM
  • Thank you for Asking this Question.

    select * from table -- gives the following output

    server   col2   col3   col4   col5   col6    col7   col8    col9    col10

    1        1234    null    null   678    987   890   null     567      null

    1        4565    null    null    234      67   56    null     345      null

    2        null     null     578  567    234   null    73        18       null

    2       null      null    626   289     395  null    84        399     null

    3      567      null    845   null      987 674    null      null       648

    3      654      null     463   null     876   523   null      null      921

    Desired Output:

    select * from where server = 1 and _______________

    sample out put  Should look like this.

    server  col2      col5    col6    col7     col9

       1        1234    678    987    890      567

       1        4565    234       67      56     345

    Remember i have 60 columns.

    if you know that there will be fixed number of columns will be not null say 4 columns in each row will not null, then i guess you can work somehow.

    you can write a cursor to read each line one at a time, then check each column for not null values till you get not null column values equal to the column number mentioned above.

    store each row values to a temp table or table variable. at the end of the cursor you will get complete values.

    regards

    joon


    • Edited by Joon84 Wednesday, August 22, 2012 12:04 PM
    Wednesday, August 22, 2012 12:03 PM
  • OR provided that there will be fixed number of not null columns in each row, you can use

    declare @tbl1 table(server int,col2 int,col3 int,col4 int,col5 int,col6 int,col7 int, col8 int, col9 int,col10 int)
    insert into @tbl1 values(1,1234,null,null,678,987,890,null,567,null)
    insert into @tbl1 values(1,4565,null,null,234,67,56,null,345,null)
    insert into @tbl1 values(2,null,null,578,567,234,null,73,18,null)
    insert into @tbl1 values(2,null,null,626,289,395,null,84,399,null)
    insert into @tbl1 values(3,567,null,845,null,987,674,null,null,648)
    insert into @tbl1 values(3,654,null,463,null,876,523,null,null,921)
    SELECT * FROM @tbl1 FOR XML PATH('NotNull')

    it will give you an xml file where only not null columns are present for each row, then depending on your need, you can apply XSLT or script task to get the result from this input as tabular format.

    regards

    joon




    • Edited by Joon84 Wednesday, August 22, 2012 1:01 PM
    Wednesday, August 22, 2012 12:57 PM
  • Hi Cpcudi,

    I agree with Uri Dimant, as the column list can not be dynamically changed in the query result. Moreover, one row may have a null value for a given column but another row might again have a non null value.

    Alternately, you can have a fixed column list with aliasing and use a Coalesce function for the non-null value.


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    • Proposed as answer by DC_010 Monday, January 22, 2018 11:10 PM
    Wednesday, August 22, 2012 1:30 PM
  • Aplologize to be late.

    I have Hardcoded The Query. I understood it is quite a huge task to dynamically select the columns which are not nulls.

    • Marked as answer by cpcudi Thursday, August 23, 2012 6:18 PM
    Thursday, August 23, 2012 6:18 PM
  • I also recently faced the same situation, below query might help:

    DECLARE @TableName AS VARCHAR(50) = 'KS.WELL'
    DECLARE @UnpivotColumns AS VARCHAR(MAX)
    DECLARE @FinalSql AS VARCHAR(MAX)

    SELECT @UnpivotColumns = SUBSTRING((SELECT DISTINCT CONCAT(',', QUOTENAME(name)) FROM sys.all_columns
    WHERE OBJECT_ID = OBJECT_ID(@TableName)
    FOR XML PATH('')),2,200000)

    SET @FinalSql = (SELECT CONCAT('SELECT PV.columnnames COLUMN_NAME, PV.columnname RecordCount FROM(', CONCAT('SELECT ',SUBSTRING((SELECT ', ' + CONCAT('SUM(IIF(', name,' is not null, 1, 0)) ', name) FROM sys.all_columns
    WHERE OBJECT_ID = OBJECT_ID(@TableName)
    FOR XML PATH('')),2,2000000), ' FROM ', @TableName, ' )A UNPIVOT (columnname FOR columnnames IN (', @UnpivotColumns,')) PV
    WHERE PV.columnname > 0')))

    EXEC (@FinalSql)

    https://mastermindashutosh.wordpress.com/2018/06/12/select-all-columns-which-are-not-null-in-a-table-in-sql-server/

    Tuesday, June 12, 2018 9:07 AM