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