选择SQL列作为添加的行

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

我有一个SQL Server 2008表，如下所示……

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

非常感谢您的指导。谢谢

3 个解决方案

#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

#2

You can use UNION ALL to do this:

你可以用UNION ALL来做:

select ID, Others1Desc as ItemDesc, Others1Value as ItemValue
from yourtable
union all
select ID, Others2Desc as ItemDesc, Others2Value as ItemValue
from yourtable

#3

This way:

这种方式:

select ID, Others1Desc as ItemDesc,Others1Value as itemValue from
(select ID ,   Others1Desc ,   Others1Value from table1

Union all

select ID ,   Others2Desc,   Others2value from table1)as a

#1