SQL: von Zeilendarstellung in Spaltendarstellung

[Captnemo]

Hallo Leute,

ich hab eine Abfrage werche mir folgendes Ergebis liefert:

Delphi-Quellcode:

Name        Abwesenheitsgrund            Tage

Meier       Urlaub                       5
Meier       Krank                        3
Meier       Seminar                      4
Meier       Feiertag                     2
Müller      Krank                        2
Müller      Feiertag                     2
Schulze     Urlaub                       1
Schulze     Seminar                      1
Schulze     Feiertag                     2

Ich brauche aber folgende Darstellung dieser Information:

Delphi-Quellcode:

Name        Urlaub          Krank        Seminar           Feiertag

Meier       5               3            4                 2
Müller      0               2            0                 2
Schulze     1               0            1                 2

Mir ist nicht klar, wie ich das über einen SQL-Befehl lösen könnte. Hab auch keine Idee für einen Ansatz.
Zu erwähnen wäre noch, dass wenn ein Wert in der oberen Tabelle nicht vorhanden ist, dieser aber trotzdem in der
Spaltendarstellung mit einer 0 vorhanden sein sollte.

Die eigentliche Urspungstabelle sieht ungefähr so aus:

Delphi-Quellcode:

Name        Abwesenheitsgrund         Datum

Meier       Urlaub                    01.05.2009
Meier       Urlaub                    02.05.2009
Meier       Seminar                   03.05.2009
...usw.

Vielleicht kann man mehrere Select-Anweisung so zusammensetzen, damit eine spaltenartige Darstellung ensteht.

Bin für jede Idee dankbar.

[mkinzler]

Nennt man PIVOT

Sind die Gründe fest vorgegeben?

[omata]

Zitat:

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...

SQL-Code:

EXEC proc_Pivot

[alzaimar]

Ich hab auch einen:

SQL-Code:

Select Name,
       Sum (Case when U.Abwesenheitsgrund = 'Urlaub'   then 1 else 0 end) as 'Urlaub',
       Sum (Case when U.Abwesenheitsgrund = 'Krank'    then 1 else 0 end) as 'Krank',
       Sum (Case when U.Abwesenheitsgrund = 'Seminar'  then 1 else 0 end) as 'Seminar',
       Sum (Case when U.Abwesenheitsgrund = 'Feiertag' then 1 else 0 end) as 'Feiertag'
From Tabelle
  Group by Name

Dynamische Erzeugung:

SQL-Code:

create procedure CreatePivotTable 
        @TableName VarChar(80),
   @GroupField VarChar(80),
   @PivotField VarChar(80)
as
declare @SelectCmd VarChar(8000), @PivotElement VarChar(80)
--
-- 1. Alle Spaltennamen erzeugen
create table #fields (elements varchar(80))
execute('insert into #fields select distinct '+ @PivotField+' from '+@TableName)

--
-- 2. SELECT Befehl erzeugen
set @SelectCmd = 'select '+@GroupField
declare C cursor for select * from #fields
  
open C
fetch next from C into @PivotElement
while (@@fetch_status = 0) begin
  set @SelectCmd = @SelectCmd + ','+Char(13)+char(10)+
     '   Sum(case when '+@PivotField+' = '''+@PivotElement+''' then 1 else 0 end) as '+@PivotElement
  fetch next from C into @PivotElement
end
close C
deallocate C
drop table #fields
set @SelectCmd = @SelectCmd + Char(13)+char(10)+'from '+@TableName
+ Char(13)+char(10)+'Group by '+@GroupField

print @SelectCmd --  <<---- Testausgabe
execute (@SelectCmd)

Ausführung durch

SQL-Code:

exec CreatePivotTable 'tabelle','Name','Abwesenheitsgrund'

Ist leider nicht ganz so allgemeingültig, sonder basiert auf der Annahme, das das @PivotField ein String ist. Das könnte man noch optimieren

[omata]

Kleine Korrektur...

SQL-Code:

SELECT Name,
       SUM(CASE WHEN abwesenheitsgrund = 'Urlaub'   THEN 1 ELSE 0 END) AS Urlaub,
       SUM(CASE WHEN abwesenheitsgrund = 'Krank'    THEN 1 ELSE 0 END) AS Krank,
       SUM(CASE WHEN abwesenheitsgrund = 'Seminar'  THEN 1 ELSE 0 END) AS Seminar,
       SUM(CASE WHEN abwesenheitsgrund = 'Feiertag' THEN 1 ELSE 0 END) AS Feiertag
FROM tabelle
GROUP BY name

[alzaimar]

omata, was haste denn korrigiert? :gruebel:

[Jürgen Thomas]

Zitat:

Zitat von alzaimar

omata, was haste denn korrigiert? :gruebel:

Den Tabellen-Alias U entfernt. Jürgen

[alzaimar]

Na, nicht nur den. Nur steht genau diese Lösung in meinem Beitrag eins drunter. Daher dachte ich, er hätte mich verbessert.

[mkinzler]

Zitat:

Zitat von alzaimar

Na, nicht nur den. Nur steht genau diese Lösung in meinem Beitrag eins drunter. Daher dachte ich, er hätte mich verbessert.

Hat er ja auch, bei dir sthet der Alias U noch in den Case-Fällen

[alzaimar]

Zitat:

Zitat von mkinzler

Zitat:

Zitat von alzaimar

Na, nicht nur den. Nur steht genau diese Lösung in meinem Beitrag eins drunter. Daher dachte ich, er hätte mich verbessert.

Hat er ja auch, bei dir sthet der Alias U noch in den Case-Fällen

Alter Schwede. Bin ich blind. :wall: :wall: :wall: