Select SQL Columns as added rows

Question

I have a SQL Server 2008 table as below...

ID    Others1Desc    Others1Value    Others2Desc    Others2value
--    -----------    ------------    -----------    ------------
id_x  xyz            12.50           pqr            10.00
id_y  abc            NULL            mno             1.05

Now, I want the select result as below...

ID    ItemDesc       ItemValue
--    -----------    ------------
id_x  xyz            12.50
id_x  pqr            10.00
id_y  abc             NULL
id_y  mno             1.05

Any guidance is highly appreciated. Thanks

Answer 1

And another way is using cross apply and values clause like this

DECLARE @temp table (ID [varchar](100), Others1Desc [varchar](100), Others1Value decimal(11, 2), Others2Desc [varchar](100), Others2Value decimal(11, 2));

INSERT @temp (ID, Others1Desc, Others1Value, Others2Desc, Others2Value) VALUES ('id_x', 'xyz', 12.50, 'pqr', 10.00);
INSERT @temp (ID, Others1Desc, Others1Value, Others2Desc, Others2Value) VALUES ('id_y', 'abc', NULL, 'mno', 1.05);

select ID, t.* from @temp
cross apply
(
    values (Others1Desc, Others1Value),
            (Others2Desc, Others2Value)
) t(ItemDesc, ItemValue)

Result

ID     ItemDesc  ItemValue
--------------------------
id_x    xyz       12.50
id_x    pqr       10.00
id_y    abc       NULL
id_y    mno       1.05