Pivoting Data in SQL Server 2008

Question

I have a result set like below :

MinID       MaxID   ColumnName
----------  ------- -------------------
167         34458   AccountManager
174         174     ClientDeliveryDate
184         184     LocalContactEmail
192         34459   ProjectDeveloper
194         194     ServiceType

I want a result set like :

AccountManager ClientDeliveryDate LocalContactEmail ProjectDeveloper ServiceType
-------------- ------------------ ----------------- ---------------- -----------
167            174                184               192              194
34458          174                184               34459            194

Go to http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx — Inus C, May 10 '13 at 08:42

score 7 · Accepted Answer · answered May 10 '13 at 08:45

select [AccountManager], [ClientDeliveryDate], [LocalContactEmail],[ProjectDeveloper] ,[ServiceType]
from
(
    SELECT ColumnName, TestType, score
    FROM 
    (
        SELECT ColumnName, MinID, MaxID  from table_name
    ) PivotData
    UNPIVOT
    (
        score for TestType IN (MinID, MaxID)
    ) as initialUnPivot
) as PivotSource
PIVOT 
(
MIN(score) FOR ColumnName IN ([AccountManager], [ClientDeliveryDate], [LocalContactEmail],[ProjectDeveloper] ,[ServiceType])
) AS PivotTable
order by [AccountManager] desc

SQL FIDDLE

score -1 · Answer 2 · answered May 10 '13 at 08:46

Try this query.

SELECT 
max(case when `ColumnName` = 'AccouontManager'  then `MaxID` end) as AccouontManager, 
max(case when `ColumnName` = 'ClientDeliveryDate'  then `MaxID` end) as ClientDeliveryDate,
max(case when `ColumnName` = 'LocalContactEmail'  then `MaxID` end) as LocalContactEmail,
max(case when `ColumnName` = 'ProjectDeveloper'  then `MaxID` end) as ProjectDeveloper,
max(case when `ColumnName` = 'ServiceType'  then `MaxID` end) as ServiceType
FROM `your_table` 
union all 
SELECT 
max(case when `ColumnName` = 'AccouontManager'  then `MinID` end) as AccouontManager, 
max(case when `ColumnName` = 'ClientDeliveryDate'  then `MinID` end) as ClientDeliveryDate,
max(case when `ColumnName` = 'LocalContactEmail'  then `MinID` end) as LocalContactEmail,
max(case when `ColumnName` = 'ProjectDeveloper'  then `MinID` end) as ProjectDeveloper,
max(case when `ColumnName` = 'ServiceType'  then `MinID` end) as ServiceType
FROM `your_table`

What if there are **10 columns.** 1. Will you write **10 union all** 2. What if columns values are changed. it is bound to **fail in 90 cases out of 100** — Prahalad Gaggar, May 10 '13 at 08:54
I haven't run this, it should do the job but it's not scalable. — Farhan Ahmed, May 10 '13 at 08:58

Pivoting Data in SQL Server 2008

2 Answers2