[MSSQL] Query mit dynamischer Spaltenselection

[drfuture]

Servus zusammen,
habe mal ein evtl. auch für den ein oder anderen interessantes Problem

Ausgangslage:
Ich habe eine vorgegebene Tabellenstruktur einer CMDB (Struktur-Beispiel weiter unten)
Jedes CI kann dabei eine freie Anzahl von Eigenschaften annehmen und den Wert zu diesen Eigenschaften speichern.
Zusätzlich können weitere Eigenschaften für Ci's erstellt werden.

Ziel:
Die Ausgabe einer Tabelle mit allen Ci's und ihren jeweiligen Eigenschaften + Werten.
z.B. bei einem Computer + einem Monitor als Ausgabe:

Name, CPU, Ram. Größe(zoll), Display
pc1, 2, 2, null, null
tft1, null, null, 24, LED

Da die Anzahl der Eigenschaften und somit die Anzahl der Spalten sich jederzeit (und je nach selektion der Ci's z.B. nach Namen) ändern kann - müssen die Spalten Dynamisch generiert werden.

Aktueller Stand:

Aktuell habe ich einen Query der keinen Syntax-Error erzeugt und, insofern nur ein Eintrag in der Tabelle für Attributs-Verknüpfungen steht - auch genau das richtige Ergebnis angezeigt wird (Im Beispiel it_cmdb_ci_attr)
Insofern dort aber mehrere Einträge erstellt werden - werden die ausgegebenen Einträge zum ² mehr ... das heißt irgendwo habe ich eine Schleife drin - oder eine Begrenzung fehlt.
Ich bin hier nun leider mit meinem Latein am Ende - und weiß im Moment auch nicht wie ich den Query so Debuggen kann das ich verstehe wo der Fehler ist.

Damit man sich das ganze besser vorstellen kann habe ich ein Demoszenario erstellt das in einer leeren DB alles passende anlegt. Am Ende Steht die Query

Beispiel (MSSQL!):
[src=mysql]CREATE TABLE [dbo].[it_cmdb_ci_attr](
[cmdb_ci_attr_id] [int] NOT NULL,
[change_id] [int] NULL,
[cmdb_ci_id] [int] NULL,
[cmdb_product_attr_id] [int] NULL,
[content] [varchar](512) NULL,
[product_id] [int] NULL,
[im_attr_id] [int] NULL,
[immediate_update] [char](1) NULL,
CONSTRAINT [PK_it_cmdb_ci_attr] PRIMARY KEY CLUSTERED
(
[cmdb_ci_attr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[it_product_label](
[product_label_id] [int] NOT NULL,
[data_type] [varchar](15) NULL,
[label_active] [char](1) NULL,
[product_label] [varchar](25) NULL,
[product_label_type] [varchar](25) NULL,
[immediate_update] [char](1) NULL,
CONSTRAINT [PK_it_product_label] PRIMARY KEY CLUSTERED
(
[product_label_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


GO


CREATE TABLE [dbo].[it_cmdb_product_attr](
[cmdb_product_attr_id] [int] NOT NULL,
[label_from] [varchar](20) NULL,
[label_to] [varchar](20) NULL,
[mandatory] [char](1) NULL,
[product_id] [int] NULL,
[product_label_id] [int] NULL,
[importkey] [varchar](200) NULL,
CONSTRAINT [PK_it_cmdb_product_attr] PRIMARY KEY CLUSTERED
(
[cmdb_product_attr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Go


INSERT INTO [dbo].[it_cmdb_product_attr]
([cmdb_product_attr_id] ,
[label_from],
[label_to] ,
[mandatory],
[product_id],
[product_label_id],
[importkey])
VALUES
(11081,NULL,NULL,'Y',10336,10001,'Beamer-Modell')
GO


INSERT INTO [dbo].[it_product_label]
( [product_label_id],
[data_type] ,
[label_active] ,
[product_label] ,
[product_label_type] ,
[immediate_update])
VALUES
(10001,'Text','Y','Modell','Hardware',NULL)
GO


INSERT INTO [dbo].[it_cmdb_ci_attr]
( [cmdb_ci_attr_id] ,
[change_id] ,
[cmdb_ci_id] ,
[cmdb_product_attr_id] ,
[content] ,
[product_id],
[im_attr_id] ,
[immediate_update])
VALUES
(177718,NULL,61910,11081,'CY9400DBXMP',10347,NULL,NULL)
GO








DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)


select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Product_label)
from it_product_label
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')


set @query = 'select it_cmdb_ci_attr.cmdb_ci_id, ' + @cols + ' from
it_product_label,it_cmdb_ci_attr, (
select it_cmdb_ci_attr.cmdb_ci_id, it_product_label.product_label, it_product_label.product_label_type, it_cmdb_ci_attr.content
from it_cmdb_ci_attr
left join it_cmdb_product_attr on it_cmdb_product_attr.cmdb_product_attr_id = it_cmdb_ci_attr.cmdb_product_attr_id
left join it_product_label on it_cmdb_product_attr.product_label_id = it_product_label.product_label_id
) x
pivot
(
min(content)
for Product_label in (' + @cols + ')
) p '


execute(@query)
[/src]

 

[BurnerR]

Da die Anzahl der Eigenschaften und somit die Anzahl der Spalten sich jederzeit (und je nach selektion der Ci's z.B. nach Namen) ändern kann - müssen die Spalten Dynamisch generiert werden.

Meines Erachtens nach ist das Problem auf der DB Design Ebene zu verorten.
Erster Ansatz wäre ein Auslagern der Eigenschaften in eine eigene Tabelle. Spalten: id, name, wert

Am besten du gibst hier im Thread erstmal deine DB Struktur nieder. Ich habe den Eindruck dass du einfach nur an eine einzelne Tabelle denkst.

 

[drfuture]

öhm - die DB-Struktur, bzw. der relevante Teil ist im Beispiel mit drin... und an der Struktur kann ich nichts ändern...

 

[ElricM]

Hallo DrFuture,

auch auf die Gefahr hin, dass ich nun Prügel bekomme. Aber select distincts und dynamisches SQL sind aus Performancegründen pfui. Sagt Dir Full-Table-Scan etwas ? Den Optimizer der Datenbank kannst Du bei solchen Abfragen auch vergessen.
Solche Logik kommt heute nicht mehr in die Datenbank. Ich kämpfe seit Jahren in diversen Projekten um die Ablöse solcher Ansätze. Kommst Du aus der C#-Ecke ? PLINQ und EF wären hier prima.

Grüße
ElricM

 

[drfuture]

Ach was Haue bekommt von mir keiner ;D
Aber in dem Fall geht mehr oder weniger "nur" eine solche Abfrage... da eben die Datenbankstruktur vorgegeben ist und das Ergebnis eine View sein muss die dann weiter verwendet wird. Die Abfrage wird max. 1x im Monat ausgeführt von 1-2 Personen... die Last ist damit zu vernachlässigen...
Das das nicht sonderlich Performant ist weiß ich schon.. in dem Fall geht Nutzbarkeit für die Zielgruppe über die Performance... da die Benutzer im obersten Stockwerk sitzen *fg*