SQL Server 2008 R2, convert columns to rows and rows to columns

Question

Current Data in table

id  AA  BB  CC  DD  EE  FF
1   a   b   c   d   e   f
2   a1  b2  c3  d2  e4  f2

And I want to :

Colx   1   2
AA     a   a1 
BB     b   b2
CC     c   c3
DD     d   d2 
EE     e   e4 
FF     f   f2

Please Help me.

Answer 1

well, after carefully looking at your question, I think that this is not duplicate, you not only want to convert columns to rows, you want to convert rows to columns simultaneously.
So you have to combine two solutions - unpivoting columns to rows and pivoting rows to columns, something like:

select
    c.name as ColX,
    max(case when t.id = 1 then c.value else null end) as [1],
    max(case when t.id = 2 then c.value else null end) as [2]
from Table1 as t
    cross apply (values
        ('AA', t.AA),
        ('BB', t.BB),
        ('CC', t.CC),
        ('DD', t.EE)
     ) as c(name, value)
group by c.name

sql fiddle demo

you also could use standard pivot and unpivot from SQL Server:

select *
from Table1 as t
unpivot (
    value for
    colx in (AA, BB, CC, DD, EE, FF)
) as unpiv
pivot (
    max(unpiv.value)
    for id in ([1], [2])
) as piv

sql fiddle demo

If you don't want to specify values of id and columns, try dynamic SQL:

declare @stmt1 nvarchar(max), @stmt2 nvarchar(max), @stmt nvarchar(max)

select @stmt1 =
    isnull(@stmt1 + ', ', '') + 
    '(''' + c.column_name + ''', t.' + c.column_name + ')'
from information_schema.columns as c
where c.table_name = 'Table1' and c.column_name <> 'id'

select @stmt2 =
    isnull(@stmt2 + ', ', '') + 'max(case when t.id = ' + t.id + ' then c.value end) as [' + t.id + ']'
from (select distinct cast(id as nvarchar(max)) as id from Table1) as t

select @stmt = '
    select
        c.name as ColX, ' + @stmt2 + '
    from Table1 as t
        cross apply (values ' + @stmt1 + ') as c(name, value)
    group by c.name'

exec sp_executesql @stmt = @stmt

sql fiddle demo