asked on

SQL pivot unpivot

I need to change this

Commissioner Code        Reason Access Provided
1512        Responsible Commissioner
1512        Resident
1512        Mentioned
1412        Responsible commissioner
1412        Mentioned

into

Commissioner Code       Responsible_Commissioner       Resident        Mentioned
1512        1        1        1
1412        1        0        1

Any help appreciated.

Cheers

T

Sharath S

It depends on how many "Reason Access Provided" you have. if you have a finite number of reasons, you can try like this.

select [Commissioner Code],
       max(case [Reason Access Provided] when 'Responsible Commissioner' then 1 else 0 end) as Responsible_Commissioner,
	   max(case [Reason Access Provided] when 'Resident' then 1 else 0 end) as Resident,
	   max(case [Reason Access Provided] when 'Mentioned' then 1 else 0 end) as Mentioned
  from your_table
 group by [Commissioner Code]

Open in new window

David Todd

Hi,

Where you really wanting to use the pivot operator, or does Sharath's answer above fit the bill?

What version of SQL are you using - just to confirm that its at least 2005 for the pivot operator.

Regards
David

Tony303

I have kinda got there.
I put your data into a table called PivotExample.

Struggling to get the individual Commissioner Code in the result.

SELECT [Responsible Commissioner],[Resident],[Mentioned]
FROM
(SELECT [Commissioner Code], [Reason Access Provided]
    FROM [PivotExample] ) AS SourceTable
PIVOT
(
COUNT([Commissioner Code]) 
FOR [Reason Access Provided] IN ([Responsible Commissioner],[Resident],[Mentioned])
) AS PivotTable;

Open in new window

ASKER CERTIFIED SOLUTION

Tony303

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trawley

ASKER

Thanks all

Will test em out shortly.

trawley

ASKER

Thanks all. I wanted specifically a pivot example and whilst all of the other anwsers were good this one was the best for the particular requirement in mind.

I havent used pivot unpivot yet so wanted to see how it worked.

Thanks Tony

T