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.
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.
You can use the PIVOT function to get the result, but first you will want to unpivot the unchecked
and checked
columns. Since you are using SQL Server 2008r2, you can use CROSS APPLY to UNPIVOT the columns into multiple rows.
The basic syntax for CROSS APPLY will be:
select department, tools,
[check/uncheck],
value
from yourtable
cross apply
(
values
('checked', checked),
('unchecked', unchecked)
) c([check/uncheck], value);
See Demo, this gets your data into the format:
| DEPARTMENT | TOOLS | CHECK/UNCHECK | VALUE |
|------------|--------|---------------|-------|
| Maintain | engine | checked | 0 |
| Maintain | engine | unchecked | 0 |
| Maintain | oils | checked | 0 |
| Maintain | oils | unchecked | 2 |
Once the data has been converted into this format, then you can use the PIVOT function to turn your tools
into columns:
select department,
[check/uncheck],
engine, oils, grease, sounds, wapers
from
(
select department, tools,
[check/uncheck],
value
from yourtable
cross apply
(
values
('checked', checked),
('unchecked', unchecked)
) c([check/uncheck], value)
) d
pivot
(
sum(value)
for tools in (engine, oils, grease, sounds, wapers)
) piv
order by department;
See SQL Fiddle with Demo. This will give a result of:
| DEPARTMENT | CHECK/UNCHECK | ENGINE | OILS | GREASE | SOUNDS | WAPERS |
|------------|---------------|--------|------|--------|--------|--------|
| Maintain | checked | 0 | 0 | 5 | 0 | 0 |
| Maintain | unchecked | 0 | 2 | 1 | 0 | 0 |
| Operations | checked | 1 | 2 | 1 | 5 | 0 |
| Operations | unchecked | 0 | 1 | 2 | 1 | 2 |
You are pivoting on two columns
(department, [check/uncheck])
. As far as I know, that means you cannot use SQL Server'spivot
syntax.One way is to "unpivot" (aka "normalize")
checked
in a subquery. You can then "pivot" (aka "denormalize") thetools
column in the outer query:Live example at SQL Fiddle.