SQL Server dynamic selection of output column

Question

Just started using SQL Server. This seems like it should be super easy, but haven't found an answer yet.

Trying to go from this:

id  fruit  value
1   apple   10
2   orange  a
3   orange  b
4   apple   20
5   apple   30

To this:

fruit  quantitative qualitative
apple   10  NULL
orange  NULL    a
orange  NULL    b
apple   20  NULL
apple   30  NULL

Something like...

• If apple -> put the value in column 2, column 3 = NULL
• If orange -> put the value in column 3, column 2 = NULL

Thank you!

Answer 1

You can use ISNUMERIC and CASE:

SELECT fruit,
       quantitative=CASE WHEN ISNUMERIC(value)=1 THEN value ELSE NULL END,
       qualitative=CASE WHEN ISNUMERIC(value)=0 THEN value ELSE NULL END
FROM dbo.Fruits

Demo

FRUIT   QUANTITATIVE    QUALITATIVE
apple   10              (null)
orange  (null)           a
orange  (null)           b
apple   20              (null)
apple   30              (null)

Answer 2

select 
fruit,
case when fruit = 'apple' then value else null end as quantitative,
case when fruit = 'orange' then value else null end as qualitative
from mytable

SqlFiddle