Home 2017 November SQL Puzzle: SQL Advance Query – Remove all NULL value and arrange other NON NULL

SQL Puzzle: SQL Advance Query – Remove all NULL value and arrange other NON NULL

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to remove all NULL records and arrange NON NULL value in an Order.

Input Data:

Expected Output:

Create a table with data:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Puzzle: SQL Advance Query - Do PIVOT - UNPIVOT and Sort the resultSQL Server: When you last executed DBCC CHECKDB? (Using sp_readerrorlog)
Comments: 5
  1. Dinesh I.S
    November 23, 2017 at 10:14 am

    Select * From tbl_Numbers
    UNPIVOT(Orders For Description In(Col1,Col2,Col3,Col4))A
    PIVOT(Max(Orders) For Description In([Col1],[Col2],[Col3],[Col4])) A

    ReplyCancel
  2. Dinesh I.S
    November 23, 2017 at 10:18 am

    Hi Sir

    We are getting wrong result please check the once.

    Col1:-20 but in your result showing 10

    SELECT
    [1] as Col1, [2] as Col2, [3] as Col3, [4] as Col4
    FROM
    (SELECT isnull(Col1,0)+isnull(Col2,0)+isnull(Col3,0)+isnull(Col4,0) as SumAll,ROW_NUMBER () over (order by (select 0)) as t
    FROM tbl_Numbers ) AS SourceTable
    PIVOT
    (
    sum(SumAll)
    FOR t IN ( [1], [2], [3], [4])
    ) AS PivotTable

    ReplyCancel
  3. Ankush
    September 14, 2018 at 9:45 am

    Select sum(col1) as col1, sum(col2) as col2 from
    (Select distinct coalesce(col1,0)as col1 ,coalesce(col2,0)as col2 from (
    Select 10 as col1, NULL as col2 from dual
    UNION
    Select NULL as col1, 20 as col2 from dual)foo)

    Situation would be different when there are no numbers but characters!!

    ReplyCancel
  4. Lakshman
    May 4, 2019 at 11:04 am

    select max(col1) as col1,max(col2) as col2,max(col3) as col3,max(col4) as col4 from tbl_Numbers

    ReplyCancel

Leave a Reply to Ankush Cancel reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

November 16, 2017 5 Comments SQL Puzzle, , , , , , , , , ,