2

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?

R0b0tn1k
  • 4,256
  • 14
  • 46
  • 64

1 Answers1

4
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
Gabriel McAdams
  • 56,921
  • 12
  • 61
  • 77
  • Actually, i think i cracked it before, but it turns out SQL studio is cracking even when i try to save this as a view (just the pivot part). – R0b0tn1k Feb 15 '10 at 21:38