locked
SQL query help RRS feed

  • Question

  • User-1216534573 posted
    SQL Query help Below is the Order table I need the output  as mentioned below. 
    Order Table
    OrderName OrderData1 OrderData2
    A          100  
    A          200  
    A          300  
    A            400
    A            500
    Output needed in below format
    OrderName OrderData1 OrderData2
    A          100 400
    A          200 500
    A          300  

    Anyone help is appreciated.

    Tuesday, June 28, 2016 5:06 AM

Answers

  • User1559292362 posted

    Hi Magesh,

    Am not getting the result as shown in output. Please help me

    Could you please describe you issue in detail. Does it throws any exception, or you couldn't retrieve the records what you want (if so, please provide related records).

    In addition, I create a complete code as below for you reference. please try to test it.

    DECLARE @OrderTable TABLE
    (
      OrderName varchar(50), 
      OrderData1 int,
      OrderData2 int
    )
    
    insert into @OrderTable values ('A',100,null)
    insert into @OrderTable values ('A',200,null)
    insert into @OrderTable values ('A',300,null)
    insert into @OrderTable values ('A',null,400)
    insert into @OrderTable values ('A',null,500)
    
    ;WITH d1 (OrderName, OrderData1,rownumber)  
    AS  
    (  
        SELECT OrderName, OrderData1,ROW_NUMBER() over(order by OrderName)  rownumber
        FROM @OrderTable
        WHERE OrderData1 IS NOT NULL  
    )  
    ,
    d2 (OrderName, OrderData2,rownumber)
    AS
    (
      SELECT OrderName, OrderData2,ROW_NUMBER() over(order by OrderName)   rownumber
        FROM @OrderTable
        WHERE OrderData2 IS NOT NULL  
    )
    select COALESCE(d1.OrderName, d2.OrderName) OrderName, d1.OrderData1,d2.OrderData2  from d1 full join d2 on d1.rownumber = d2.rownumber 
    
    

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 30, 2016 7:21 AM

All replies

  • User1559292362 posted

    Hi Magesh,

    SQL Query help Below is the Order table I need the output  as mentioned below. 

    Based on your requirement, I create a demo as below for your reference.

    WITH d1 (OrderName, OrderData1,rownumber)  
    AS  
    (  
        SELECT OrderName, OrderData1,ROW_NUMBER() over(order by OrderName)  rownumber
        FROM OrderTable
        WHERE OrderData1 IS NOT NULL  
    )  
    ,
    d2 (OrderName, OrderData2,rownumber)
    AS
    (
      SELECT OrderName, OrderData2,ROW_NUMBER() over(order by OrderName)   rownumber
        FROM OrderTable
        WHERE OrderData2 IS NOT NULL  
    )
    select COALESCE(d1.OrderName, d2.OrderName) OrderName, d1.OrderData1,d2.OrderData2  from d1 full join d2 on d1.rownumber = d2.rownumber 
    

    Best regards,

    Cole Wu

    Wednesday, June 29, 2016 4:22 AM
  • User-1216534573 posted

    Dear Cole,

    Am not getting the result as shown in output. Please help me

    Wednesday, June 29, 2016 8:13 AM
  • User1559292362 posted

    Hi Magesh,

    Am not getting the result as shown in output. Please help me

    Could you please describe you issue in detail. Does it throws any exception, or you couldn't retrieve the records what you want (if so, please provide related records).

    In addition, I create a complete code as below for you reference. please try to test it.

    DECLARE @OrderTable TABLE
    (
      OrderName varchar(50), 
      OrderData1 int,
      OrderData2 int
    )
    
    insert into @OrderTable values ('A',100,null)
    insert into @OrderTable values ('A',200,null)
    insert into @OrderTable values ('A',300,null)
    insert into @OrderTable values ('A',null,400)
    insert into @OrderTable values ('A',null,500)
    
    ;WITH d1 (OrderName, OrderData1,rownumber)  
    AS  
    (  
        SELECT OrderName, OrderData1,ROW_NUMBER() over(order by OrderName)  rownumber
        FROM @OrderTable
        WHERE OrderData1 IS NOT NULL  
    )  
    ,
    d2 (OrderName, OrderData2,rownumber)
    AS
    (
      SELECT OrderName, OrderData2,ROW_NUMBER() over(order by OrderName)   rownumber
        FROM @OrderTable
        WHERE OrderData2 IS NOT NULL  
    )
    select COALESCE(d1.OrderName, d2.OrderName) OrderName, d1.OrderData1,d2.OrderData2  from d1 full join d2 on d1.rownumber = d2.rownumber 
    
    

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 30, 2016 7:21 AM
  • User475983607 posted

    Dear Cole,

    Am not getting the result as shown in output. Please help me

    While Cole's answer does in fact produce the expected results using the example data.  It's unlikely, however, that the code will work in the real world because the example data does explain why OrderData1 (100) goes with OrderData2 (400).  Cole used order which produces the expected results as requested but in my experience basing relationship on record order is very fragile and generally means there is an underlying design issue.

    What are the business rules that relate OrderData1 to OrderData2?  Is it the record order?  If so, then Cole's code is the solution.  Otherwise, you'll need to do a bit of analysis work on the existing processes that populate the records.  I suspect you have a many-to-many relationship and rather than adding a new table to handle the relationship a new column was created.

    Thursday, June 30, 2016 6:38 PM