pivot 1 column sql server code example

Example 1: sql server pivot rows to columns

select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
  select value, columnname
  from yourtable
) d
pivot
(
  max(value)
  for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;

Example 2: pivot table sql server

SELECT SalesAgent AS PivotSalesAgent, India, US, UK FROM tblAgentsSales
PIVOT 
(
	SUM(SalesAmount) FOR SalesCountry IN (India, US, UK)
)
AS testPivotTable


SELECT SalesAgent GrpBySalesAgent, SalesCountry, SUM(SalesAmount) Sales from tblAgentsSales 
GROUP BY SalesAgent, SalesCountry


select SalesAgent TableSalesAgent, SalesCountry, SalesAmount from tblAgentsSales

Example 3: sql server pivot rows to columns

-- Dynamic Pivot with unknown number of columnnames

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;