Transformations-Problem SQL Server

[bavarian]

Hallo Zusammen,

ich habe folgendes Transformierungs-Problem unter SQL Server und komme so gar nicht weiter: Es gibt drei Tabellen
- roles,
- app_objects und
- app_object_role_properties.

In der ersten Tabelle befinden sich alle möglichen Rollen und in der zweiten alle möglichen Objekte. Der Kombination aus Objekt und Rolle können Eigenschaften zugewiesen werden. Die Struktur der Tabellen sieht (vereinfacht) so aus:

roles
==========================
id
role

app_objects
==========================
id
application_object

app_object_role_properties
==========================
role_id
app_object_id
property

Ich brauche die vorliegenden Daten jedoch in einer anderen Struktur, das Ergebnis sollte wie folgt aussehen:

| role_1 role_2 ... role_n
=============|==========================
app_1 prop_1 | true true ... true
app_1 prop_2 | true true ... false
... |
app_1 prop_m | false false ... true
... |
app_k prop_l | false false ... false

Der Wert true|false gibt dabei an, ob es den Eintrag role_id, app_object_id, property gibt!

Ist dies bei nem SQL Server möglich und wenn ja wie??? Ich bin kurz vorm Verzweifeln!!

Danke,
Bav

[Andreas Kosch]

Hallo,
wenn die Daten der Ergebnismenge einer Abfrage "gedreht" werden sollen (d.h. Zeilen werden zu Spalten), stehen beim MS SQL Server drei Optionen zur Verfügung: <br>
a) SQL Server 2005: <b>PIVOT</b>-Anweisung <br>
b) SQL Server 2000: <b>CASE</b>-Verschachtelung <br>
c) Alle Versionen: <b>CURSOR</b>-Schleife baut die Ergebnismenge in einer <b>TABLE</b>-Variablen zusammen, um am Ende ein SELECT auf die TABLE-Variable zu machen

[bavarian]

Hallo und Danke für die Antwort,

eigentlich soll die Abfrage aus Zeilen Spalten machen, also in gewisser Weise "drehen", ja. Jedoch ist die Anzahl der Zeilen und entsprechend die Anzahl der Spalten variabel. Der Spaltenname soll dabei den Werten der Zeilen entsprechen. Im nächsten Schritt soll dann eine Matrix erstellt werden.

Ich arbeite momentan noch mit SQL Server 2000 und habe es auch mit nem Cursor versucht. Meine Lösung sieht momentan so aus, dass ich mir über nen Cursor ein Select Statement zusammen "bastle" und dies über EXEC ausführe. Elegant ist jedoch was anderes...

Könnte ich vielleicht ein konkretes Beispiel sehen, wie ich das mit CASE machen könnte

[Andreas Kosch]

Hallo,
angenommen, die Quell-Tabelle hat die folgende Struktur:
<code>
CREATE TABLE Verkauf
(
verkauf_id INTEGER NOT NULL IDENTITY PRIMARY KEY,
jahr INTEGER NOT NULL DEFAULT 2005,
quartal INTEGER NOT NULL,
produkt NVARCHAR(19) NOT NULL,
umsatz DECIMAL(9,2) NOT NULL
)
GO
</code>
Dann könnte die Kreuztabellen-Abfrage über CASE mit Rückgriff auf eine <i>Derived Table</i> so aussehen:
<code>
SELECT jahr,
SUM(CASE WHEN produkt = 'A' THEN Umsatz END) AS A,
SUM(CASE WHEN produkt = 'B' THEN Umsatz END) AS B,
SUM(CASE WHEN produkt = 'C' THEN Umsatz END) AS C
FROM (SELECT jahr,produkt,umsatz FROM dbo.Verkauf) AS P
GROUP BY jahr
</code>
Allerdings legt auch hier die SQL-Anweisung die Anzahl der Spalten in der Ergebnismenge fest

[bavarian]

Hallo!

Ja, das ist dann so ähnlich wie mit DECODE auf ner Oracle DB... allerdings ist das eben nicht das was ich suche! Sind in meiner roles Tabelle vier Einträge, dann soll die Matrix auch vier haben, hat die roles fünf, dann...

Und mit dieser Lösung scheine ich wohl auch das Select-Statement generieren zu müssen. Zumindest fällt mir da spontan keine andere Lösung ein.

Trotzdem Danke

[joerg obergfell]

Beispiel aus einer meiner Anwendungen in der ich ein ähnliches Problem hatte.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure ShowCompleteAnalyse
@charge_nr varchar(9)
as
begin
declare @charge varchar(10)
declare @sqltxt nvarchar(4000)
declare @element varchar(6)
declare @comma varchar(2)
declare @nr bigint
declare @value float
declare @kalib varchar(2)
declare @qkezi varchar(1)

declare @analyecur cursor
Set @charge=@charge_nr+'%'
set @comma='['

-- Erstellen einer Temporären Tabelle mit statischen Kopfspalten
create Table #overview
(
Nr bigint,
Mandant smallint,
DATUM DateTime,
ZEIT Datetime
)
create index ix_Nr_overview on #overview (Nr)

set @sqltxt='Alter table #Overview add '

set @analyecur =cursor local scroll for
-- Auslesen der Zukünftigen Spaltennamen
Select distinct element,id
from Elementwerte where Analyse like @charge order by ID
--Sql-Script zum Erweitern der lokalen Tabelle zusammen bauen
open @analyecur
Fetch next from @analyeCur
into @element,@nr
while @@fetch_status=0
begin
set @sqltxt=@sqltxt+@comma+@element+'] float '
set @comma=',['
Fetch next from @analyeCur
into @element,@nr
end
print @sqltxt

-- hier wird ein in der SP generierter Code ausgeführt.
exec sp_executesql @sqltxt

close @analyecur

Füllen der Tabelle mit den Kopfwerten
Insert into #overview (Nr ,
Mandant ,
DATUM ,
ZEIT )
Select Nr , Mandt , DAT , TIM
from ANALYSEKopf where ANALYSE like @charge

-- Cursor zum Füllen der Variablen Felder
set @analyecur = Cursor local scroll for
Select KopfNr,Element,Elementwert from zmianalye where ANALYSE like @charge
open @analyecur
fetch next from @analyecur
into @nr,@element,@value
while @@fetch_status=0
begin
-- Update script zusammen basteln
Set @sqltxt ='update #overview set ['+@element+']='+Convert(varchar(10),@value,0)
set @sqltxt=@sqltxt+' where nr='+Convert(varchar(10),@nr)
print @sqltxt
-- update script ausführen
exec sp_executesql @sqltxt
fetch next from @analyecur
into @nr,@element,@value
end
Deallocate @analyecur
Select * from #overview order by Nr
end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Achtung!!!
Das generieren von SQL Befehlen und ausführen mittels
"exec sp_executesql " ist nicht ungefährlich, da es bei falscher Verwendung auch für Hackerangriffe anfällig ist.
Desweiteren ist diese Methode nicht die Schnellste, aber ich habe keine andere Lösung mit SQlServer 2k gefunden.

Gruß Jör