February 13, 2014

Display Row Values In Columns In SQL Server 2008

There are several ways that you can transform data from multiple rows into columns. 

1. In SQL Server you can use the PIVOT function to transform the data from 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;

2. If you don't want to use the PIVOT function, then you can use an aggregate function with a CASE expression.
select
  max(case when columnname = 'FirstName' then value end) Firstname,
  max(case when columnname = 'Amount' then value end) Amount,
  max(case when columnname = 'PostalCode' then value end) PostalCode,
  max(case when columnname = 'LastName' then value end) LastName,
  max(case when columnname = 'AccountNumber' then value end) AccountNumber
from yourtable
See SQL Fiddle with Demo.

3. If you have an unknown number of column names that you want to transpose, then you can use the Dynamic SQL using PIVOT.
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 = 'SELECT ' + @cols + ' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + ')
            ) p '

execute(@query)
Hope these options will help you out. Let me know if any query remains.

Cheers

No comments:

Post a Comment