sql pivot rows to columns

1SELECT *        -- Total invoices per gender
2FROM (
3    SELECT invoice, gender
4    FROM sales
5) d
6PIVOT (
7    sum(invoice)
8    FOR gender IN ('F' AS "Women", 'M' AS "Men")
9);
10-- Table Sales:
11CREATE TABLE sales (
12    gender VARCHAR2(1 BYTE),		-- 'F' or 'M'
13    invoice NUMBER
14);

1select Firstname, Amount, PostalCode, LastName, AccountNumber
2from
3(
4  select value, columnname
5  from yourtable
6) d
7pivot
8(
9  max(value)
10  for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
11) piv;

1-- Dynamic Pivot with unknown number of columnnames
2
3DECLARE @cols AS NVARCHAR(MAX),
4    @query  AS NVARCHAR(MAX)
5
6select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
7                    from yourtable
8                    group by ColumnName, id
9                    order by id
10            FOR XML PATH(''), TYPE
11            ).value('.', 'NVARCHAR(MAX)') 
12        ,1,1,'')
13
14set @query = N'SELECT ' + @cols + N' from 
15             (
16                select value, ColumnName
17                from yourtable
18            ) x
19            pivot 
20            (
21                max(value)
22                for ColumnName in (' + @cols + N')
23            ) p '
24
25exec sp_executesql @query;