Unpivot SQL thingie

问题:

I have some data like:

Chocolate  Strawberies  Oranges
2          3            1
4          2            4

How do i get is back as

Chocolate 2
Chocolate 4
Strawberies 3
Strawberies 2
Oranges 1
Oranges 4

Without using unions and cases?

回答1:

declare @TT table (
    Chocolate int,
    Strawberies int,
    Oranges int
)


INSERT INTO @TT
SELECT 2, 3, 1
union all select
4, 2, 4

select * from @TT


SELECT
    typename,
    numericvalue
FROM (
    SELECT
        Chocolate,
        Strawberies,
        Oranges
    FROM @TT
) p
UNPIVOT (
    numericvalue
    FOR typename IN (Chocolate, Strawberies, Oranges)
) as unpvt
order by typename