Sql Server PIVOT Example

very good example from http://www.insidesql.org/blogs/cmu/sql_server/pivot-mit-2-wertefeldern

Set Nocount on
go
CREATE TABLE #p
( Name varchar(10),
ErfüllungsgradIst INTEGER,
ErfüllungsgradSoll INTEGER,
Maschine varchar(20) )
GO
INSERT INTO #p VALUES ('Maier', 3, 4, 'Drehbank')
INSERT INTO #p VALUES ('Maier', 2, 3, 'Fräsmaschine')
INSERT INTO #p VALUES ('Maier', 4, 4, 'Bohrmaschine')
INSERT INTO #p VALUES ('Huber', 1, 2, 'Drehbank')
INSERT INTO #p VALUES ('Huber', 2, 3, 'Fräsmaschine')
    
select *
from #p
      
Select Name, 'ErfüllungsgradSoll' as Erfüllungsgrad, coalesce(Drehbank, 0) as Drehbank,
coalesce(Fräsmaschine, 0) as Fräsmaschine, coalesce(Bohrmaschine, 0) as Bohrmaschine
from
(Select Name, ErfüllungsgradSoll , Maschine from #p) as SourceTable
PIVOT
(
min(ErfüllungsgradSoll)
FOR Maschine IN ( [Drehbank],[Fräsmaschine], [Bohrmaschine])
) as PivotTable
Union ALL
Select Name, 'ErfüllungsgradIst', coalesce(Drehbank,0), coalesce(Fräsmaschine,0), coalesce(Bohrmaschine, 0)
from
(Select Name, ErfüllungsgradIst, Maschine from #p) as SourceTable
PIVOT
(
min(ErfüllungsgradIst)
FOR Maschine IN ( [Drehbank],[Fräsmaschine], [Bohrmaschine])
) as PivotTable
Order By Name, Erfüllungsgrad
     

GO
drop Table #p