Problem beim Aggregieren/Gruppieren von Tabellen mit String-Werten
-
Hallo zusammen,
ich habe die folgende Tabelle:
Nun möchte ich die Tabelle in der Form aggregieren, dass die Pfade, die in anderen bereits vorhanden sind ausgeblendet werden, also
z.B. der Pfad von Zeile 2 ist bereits im Pfad von Zeile 1 enthalten und kann somit ausgeblendet werden, oder
der Pfad 'A-B' ist bereits in 'A-B-E' vorhanden und ist deshalb überflüssig. Dabei enthält Pfad_Nr alle zu einem Gesamtpfad gehörigen 'Teilpfade'.Weiß hier jemand Rat, wie ich das bewerkstelligen kann, ein GROUP BY Pfad_Nr liefert nicht das gewünschte Ergebnis.?
Gruß
Jürgen
Frage
Antworten
-
Hi Jürgen,
ich habe Dir mal ein einfaches Programm mit Deinen Ausgangsdaten geschrieben. Im Programm habe ich aber nicht die Weiterleitung, auch nicht Vertauschungen und nicht Überspringen einer Ebene berücksichtigt, also nur direkte Folgen. Die anderen Sonderfälle habe ich nicht so richtig verstanden. Da fehlt mir etwas Hintergrundwissen zur Aufgabenstellung. Hier mal der Code:using Microsoft.SqlServer.Server; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Xml.Linq; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void SqlStoredProcedure1(string Anrufer) { XElement xe0 = new XElement(Anrufer ); SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Name", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene1", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene2", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene3", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene4", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene5", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene6", SqlDbType.NVarChar, 128)); using (SqlConnection connection = new SqlConnection("context connection=true;")) { connection.Open(); SqlCommand command = new SqlCommand("SELECT DISTINCT Anrufer, Angerufener FROM TestTable1 WHERE Anrufer=@Anr", connection); command.Parameters.Add("@Anr", SqlDbType.NVarChar).Value = Anrufer; foreach (XElement xe1 in GetData(1, command)) xe0.Add(xe1); } PipeResult(xe0, 0, record); } private static List<XElement> GetData(int level, SqlCommand command) { List<XElement> liste = new List<XElement>(); if (level > 6) return liste; using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read()) liste.Add(new XElement(reader["Angerufener"].ToString()));
} foreach (XElement xe1 in liste) { command.Parameters["@Anr"].Value = xe1.Name.LocalName; foreach (XElement xe2 in GetData(level + 1, command)) xe1.Add(xe2); } return liste; } private static void PipeResult(XElement xe, int level, SqlDataRecord record) { if (level > 6) { SqlContext.Pipe.Send(record); return; } bool sw = false; if (xe == null) record.SetSqlString(level, null); else { record.SetSqlString(level, xe.Name.LocalName); foreach (XElement xe1 in xe.Elements()) { sw = true; PipeResult(xe1, level + 1, record); } } if (!sw) PipeResult(null, level + 1, record); } }Und das Ergebnis:
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks- Bearbeitet Peter Fleischer Montag, 15. Oktober 2018 09:40 using reader
- Als Antwort vorgeschlagen Peter Fleischer Montag, 15. Oktober 2018 09:42
- Als Antwort markiert Jürgen Sch Montag, 15. Oktober 2018 10:14
Alle Antworten
-
Hallo Uwe,Hallo zusammen,
ich habe die folgende Tabelle:
Gruß
Jürgen
Welche Tabelle hast Du denn?
Bitte mal ein SQL-Script für Metadaten UND Daten hier einstellen; dann wäre auch eine Lösung möglich...
Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)
Hier das Tabellenscript:
USE [TestTabellenDB] GO /****** Object: Table [dbo].[TestTable1] Script Date: 09.10.2018 11:42:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TestTable1]( [Anrufer] [nvarchar](50) NULL, [Angerufener] [nvarchar](50) NULL, [Weitergeleitet] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'B', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'C', N'D'); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'B', N'E', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'F', N'G', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'X', N'C'); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'E', N'H', 'Z'); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'G', N'Y', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'K', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'K', N'L', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'L', N'M', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'M', N'N', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'N', N'O', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'O', N'P', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'B', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'E', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'O', N'P', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'D', NULL);
Gruß Jürgen -
Hallo zusammen,
ich habe meine Testtabelle noch um einige Datensätze erweitert, die entsprechende Zirkelbezüge enthalten:
/****** Object: Table [dbo].[TestTable1] Script Date: 14.10.2018 11:33:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TestTable1]( [Anrufer] [nvarchar](50) NULL, [Angerufener] [nvarchar](50) NULL, [Weitergeleitet] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'B', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'C', N'D') INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'B', N'E', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'F', N'G', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'X', N'C') INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'E', N'H', N'Z') INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'G', N'Y', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'K', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'K', N'L', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'L', N'M', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'M', N'N', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'N', N'O', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'O', N'P', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'B', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'E', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'O', N'P', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'B', N'A', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'F', N'G', N'A') INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'D', NULL)
Meine SQL-Anweisung (Ansatz von Christoph):
With t as ( Select t1.Anrufer, t1.Angerufener, t1.Weitergeleitet, cast(t1.Anrufer + '-' + t1.Angerufener + coalesce('-' + t1.Weitergeleitet, '') as nvarchar(4000)) as Pfad, ROW_NUMBER() OVER(ORDER BY t1.Anrufer) as Pfad_Nr, case when t1.Weitergeleitet is not null then 2 else 1 end as Element, t1.Anrufer as Name, t1.Angerufener as Ebene1, case when t1.Weitergeleitet is not null then t1.Weitergeleitet else NULL end as Ebene2, cast(NULL as nvarchar(50)) as Ebene3, cast(NULL as nvarchar(50)) as Ebene4, cast(NULL as nvarchar(50)) as Ebene5, cast(NULL as nvarchar(50)) as Ebene6 from dbo.TestTable1 t1 where t1.Anrufer = 'A' UNION ALL Select t.Angerufener as Anrufer, t2.Angerufener, t2.Weitergeleitet, cast(t.Pfad + '-' + t2.Angerufener + coalesce('-' + t2.Weitergeleitet, '') as nvarchar(4000)) as Pfad, t.Pfad_Nr, case when t2.Weitergeleitet is not null then t.Element + 2 else t.Element + 1 end as Element, t.Name, t.Ebene1, case when t.Element = 1 then t2.Angerufener else t.Ebene2 end as Ebene2, case when t.Element = 1 and t2.Weitergeleitet is not null then t2.Weitergeleitet when t.Element = 2 then t2.Angerufener else t.Ebene3 end as Ebene3, case when t.Element = 2 and t2.Weitergeleitet is not null then t2.Weitergeleitet when t.Element = 3 then t2.Angerufener else t.Ebene4 end as Ebene4, case when t.Element = 3 and t2.Weitergeleitet is not null then t2.Weitergeleitet when t.Element = 4 then t2.Angerufener else t.Ebene5 end as Ebene5, case when t.Element = 4 and t2.Weitergeleitet is not null then t2.Weitergeleitet when t.Element = 5 then t2.Angerufener else t.Ebene6 end as Ebene6 from t Inner Join dbo.TestTable1 t2 on t.Angerufener = t2.Anrufer ), Aufbereitung as ( Select t.Pfad_Nr, t.Pfad, ROW_NUMBER() OVER(PARTITION BY Pfad_Nr ORDER BY LEN(Pfad) DESC) as Reihenfolge, Element, Name, Ebene1, Ebene2, Ebene3, Ebene4, Ebene5, Ebene6 from t ) Select a.Pfad, a.Reihenfolge, Element, Name, Ebene1, Ebene2, Ebene3, Ebene4, Ebene5, Ebene6 from Aufbereitung a where Reihenfolge = 1 GROUP BY a.Pfad, a.Reihenfolge, Element, Name, Ebene1, Ebene2, Ebene3, Ebene4, Ebene5, Ebene6 order by Element DESC goläuft dabei in den Fehler:
Jetzt bin ich völlig durcheinander????
Gruß Jürgen
-
Hi Jürgen,
ich habe Dir mal ein einfaches Programm mit Deinen Ausgangsdaten geschrieben. Im Programm habe ich aber nicht die Weiterleitung, auch nicht Vertauschungen und nicht Überspringen einer Ebene berücksichtigt, also nur direkte Folgen. Die anderen Sonderfälle habe ich nicht so richtig verstanden. Da fehlt mir etwas Hintergrundwissen zur Aufgabenstellung. Hier mal der Code:using Microsoft.SqlServer.Server; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Xml.Linq; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void SqlStoredProcedure1(string Anrufer) { XElement xe0 = new XElement(Anrufer ); SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Name", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene1", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene2", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene3", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene4", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene5", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene6", SqlDbType.NVarChar, 128)); using (SqlConnection connection = new SqlConnection("context connection=true;")) { connection.Open(); SqlCommand command = new SqlCommand("SELECT DISTINCT Anrufer, Angerufener FROM TestTable1 WHERE Anrufer=@Anr", connection); command.Parameters.Add("@Anr", SqlDbType.NVarChar).Value = Anrufer; foreach (XElement xe1 in GetData(1, command)) xe0.Add(xe1); } PipeResult(xe0, 0, record); } private static List<XElement> GetData(int level, SqlCommand command) { List<XElement> liste = new List<XElement>(); if (level > 6) return liste; using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read()) liste.Add(new XElement(reader["Angerufener"].ToString()));
} foreach (XElement xe1 in liste) { command.Parameters["@Anr"].Value = xe1.Name.LocalName; foreach (XElement xe2 in GetData(level + 1, command)) xe1.Add(xe2); } return liste; } private static void PipeResult(XElement xe, int level, SqlDataRecord record) { if (level > 6) { SqlContext.Pipe.Send(record); return; } bool sw = false; if (xe == null) record.SetSqlString(level, null); else { record.SetSqlString(level, xe.Name.LocalName); foreach (XElement xe1 in xe.Elements()) { sw = true; PipeResult(xe1, level + 1, record); } } if (!sw) PipeResult(null, level + 1, record); } }Und das Ergebnis:
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks- Bearbeitet Peter Fleischer Montag, 15. Oktober 2018 09:40 using reader
- Als Antwort vorgeschlagen Peter Fleischer Montag, 15. Oktober 2018 09:42
- Als Antwort markiert Jürgen Sch Montag, 15. Oktober 2018 10:14
-
Hi Jürgen,
wenn Du das Ergebnis als Set von Rows haben willst, dann nutze folgenden Code:using Microsoft.SqlServer.Server; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Xml.Linq; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void SqlStoredProcedure1(string Anrufer) { XElement xe0 = new XElement(Anrufer ); SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Name", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene1", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene2", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene3", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene4", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene5", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene6", SqlDbType.NVarChar, 128)); using (SqlConnection connection = new SqlConnection("context connection=true;")) { connection.Open(); SqlCommand command = new SqlCommand("SELECT DISTINCT Anrufer, Angerufener FROM TestTable1 WHERE Anrufer=@Anr", connection); command.Parameters.Add("@Anr", SqlDbType.NVarChar).Value = Anrufer; foreach (XElement xe1 in GetData(1, command)) xe0.Add(xe1); } SqlContext.Pipe.SendResultsStart(record); PipeResult(xe0, 0, record); SqlContext.Pipe.SendResultsEnd(); } private static List<XElement> GetData(int level, SqlCommand command) { List<XElement> liste = new List<XElement>(); if (level > 6) return liste; using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) liste.Add(new XElement(reader["Angerufener"].ToString())); } foreach (XElement xe1 in liste) { command.Parameters["@Anr"].Value = xe1.Name.LocalName; foreach (XElement xe2 in GetData(level + 1, command)) xe1.Add(xe2); } return liste; } private static void PipeResult(XElement xe, int level, SqlDataRecord record) { if (level > 6) { SqlContext.Pipe.SendResultsRow(record); return; } bool sw = false; if (xe == null) record.SetSqlString(level, null); else { record.SetSqlString(level, xe.Name.LocalName); foreach (XElement xe1 in xe.Elements()) { sw = true; PipeResult(xe1, level + 1, record); } } if (!sw) PipeResult(null, level + 1, record); } }Das Ergebnis sieht dann so aus (in einem WPF-DataGrid):
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks
