How to Convert Columns to Rows in Sql Server 2008 R2?

Question

I have a table like this

and the result should be like this

i am little bit confused about Pivot and unpivot and cross apply. can anyone help me from this.

Answer 1

You are pivoting on two columns (department, [check/uncheck]). As far as I know, that means you cannot use SQL Server's pivot syntax.

One way is to "unpivot" (aka "normalize") checked in a subquery. You can then "pivot" (aka "denormalize") the tools column in the outer query:

select  department
,       [Check/Uncheck]
,       sum(case when tools = 'engine' then nr else 0 end) as engine
,       sum(case when tools = 'oils' then nr else 0 end) as oils
,       sum(case when tools = 'grease' then nr else 0 end) as grease
,       sum(case when tools = 'sounds' then nr else 0 end) as sounds
,       sum(case when tools = 'wapers' then nr else 0 end) as wapers
from    (
        select  department
        ,       tools
        ,       'Checked' as [Check/Uncheck]
        ,       checked as nr
        from    dbo.YourTable
        union all
        select  department
        ,       tools
        ,       'Unchecked'
        ,       unchecked
        from    dbo.YourTable
        ) as SubQueryAlias
group by
        Department
,       [Check/Uncheck]
order by
        Department
,       [Check/Uncheck]

Live example at SQL Fiddle.