SQL: von Zeilendarstellung in Spaltendarstellung

[omata]

Zitat von mkinzler:

Sind die Gründe fest vorgegeben?

Wenn ja...

SQL-Code:

SELECT Name, 
       COALESCE(SUM(U.tage), 0) Urlaub, 
       COALESCE(SUM(K.tage), 0) Krank,
       COALESCE(SUM(S.tage), 0) Seminar,
       COALESCE(SUM(F.tage), 0) Feiertag
FROM tabelle t1
LEFT JOIN tabelle U
  ON     t1.name = U.name
     AND U.abwesenheitsgrund = 'Urlaub'
LEFT JOIN tabelle K
  ON     t1.name = K.name
     AND K.abwesenheitsgrund = 'Krank'
LEFT JOIN tabelle S
  ON     t1.name = S.name
     AND S.abwesenheitsgrund = 'Seminar'
LEFT JOIN tabelle F
  ON     t1.name = F.name
     AND F.abwesenheitsgrund = 'Feiertag'
GROUP BY name
ORDER BY name

Wenn nein...
Test-Tabellen-Definition

SQL-Code:

create table tabelle (
  Name varchar(20),
  Abwesenheitsgrund varchar(20),
  Datum datetime
)

insert into tabelle values ('Meier', 'Urlaub', '01.05.2009')
insert into tabelle values ('Meier', 'Urlaub', '02.05.2009')
insert into tabelle values ('Meier', 'Seminar', '03.05.2009')

Stored-Procedure...

SQL-Code:

CREATE PROCEDURE proc_Pivot() 
AS
DECLARE @SQL NVARCHAR(4000)
DECLARE @spalte VARCHAR(50)

DECLARE cursor_spalten CURSOR FOR
SELECT DISTINCT abwesenheitsgrund
FROM tabelle
ORDER BY abwesenheitsgrund

SELECT @SQL = 'CREATE TABLE ##temp (Name VARCHAR(20))'
EXEC sp_executesql @SQL, N''

OPEN cursor_spalten
FETCH cursor_spalten INTO @spalte
WHILE @@FETCH_STATUS = 0 BEGIN
  SELECT @SQL = 'ALTER TABLE ##temp ADD ' + @spalte + ' VARCHAR(20)'
  EXEC sp_executesql @SQL, N''
  FETCH cursor_spalten INTO @spalte
END
CLOSE cursor_spalten
DEALLOCATE cursor_spalten

DECLARE @name              VARCHAR(20)
DECLARE @abwesenheitsgrund VARCHAR(20)
DECLARE @tage              INT

DECLARE cursor_person CURSOR FOR
SELECT name, abwesenheitsgrund, COUNT(*) tage
FROM tabelle
GROUP BY name, abwesenheitsgrund

INSERT INTO ##temp (name)
SELECT DISTINCT name
FROM tabelle

OPEN cursor_person
FETCH cursor_person INTO @name, @abwesenheitsgrund, @tage
WHILE @@FETCH_STATUS = 0 BEGIN
  SELECT @SQL = 'UPDATE ##temp ' +
                'SET ' + @abwesenheitsgrund + ' = ' + CONVERT(VARCHAR, @tage) + ' ' +
                'WHERE name = ''' + @name + ''''
  EXEC sp_executesql @SQL, N''
  
  FETCH cursor_person INTO @name, @abwesenheitsgrund, @tage
END
CLOSE cursor_person
DEALLOCATE cursor_person

SELECT *
FROM ##temp
ORDER BY NAME

DROP TABLE ##temp

Aufruf...
EXEC proc_Pivot