How can I UNPIVOT columns into rows?

Question

Consider the following table #document_fields

id   x   y    z
------------------
1  foo  bar  baz
2  one  two  three
3  aaa  bbb  ccc
4  123  456  789

The number of columns may vary, as do the column names. I need to pivot the columns into rows like this:

id  field value
---------------
1   x     foo
1   y     bar
1   z     baz
2   x     one
2   y     two
2   z     three
.
.
3   z     789

I'm trying to understand the way pivoting in SQL Server works but can't get any further than this:

select 
      *
into
      #document_fields_pivot
from (
      select * from (
            select *
            from #document_fields
      ) t
      pivot (
            -- ??
      ) p
  ) tbl

Can anyone help me to finish this query / explain me pivoting? Any help is greatly appreciated.

Answer 1

DECLARE @Component table (ComponentId INT,Descr VARCHAR(10),Descr1 VARCHAR(10),Descr2 VARCHAR(10))
INSERT INTO @Component (ComponentId,Descr,Descr1,Descr2)values (1,'foo','bar','baz')
INSERT INTO @Component (ComponentId,Descr,Descr1,Descr2)values (2,'one','two','three')
INSERT INTO @Component (ComponentId,Descr,Descr1,Descr2)values (3,'aaa','bbb','ccc')
INSERT INTO @Component (ComponentId,Descr,Descr1,Descr2)values (3,'123','456','789')


   select
      ComponentId,Field
   from @Component P

 UNPIVOT
 (
   Field
   FOR Value
   IN (
    [Descr],[Descr1],[Descr2]
   )
 ) PivotTable;

Answer 2

What you want is called UNPIVOT and done like so:

select id,field,value from
#document_fields
unpivot
(
 value
 for field in (x,y,z)
) as u
order by id,field

Demo