locked
Select multiple column values to multiple rows RRS feed

  • Question

  • User-809753952 posted

    I have one table like this:

    Country orno1 orno2 orno3
    USA 100 102
    USA 400
    USA 350

    I need a select query that will give result as follows:

    Country Orno
    USA 100
    USA 102
    USA 350
    USA 400

    Monday, February 25, 2019 2:07 PM

Answers

  • User77042963 posted
    CREATE TABLE mytable(
       Country VARCHAR(3) NOT NULL 
      ,orno1   INT 
      ,orno2   INT 
      ,orno3   INT 
    );
    INSERT INTO mytable(Country,orno1,orno2,orno3) VALUES
     ('USA',100,102,NULL)
    ,('USA',NULL,NULL,400)
    ,('USA',NULL,350,NULL);
    
    Select Country, orno from mytable
    cross apply (values(orno1),(orno2),(orno3) ) d(orno)
    Where orno is not null
    
    --or
    Select Country, orno from mytable
    unpivot (orno for col in (orno1,orno2,orno3) ) unpvt
    
    drop  table mytable

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 25, 2019 2:58 PM