none
Dynamische Spalten im Ergebnis RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    ich benötige Hilfe bei der Erstellung einer Abfrage.

    Meine Tabellen:

    In der Tabelle Zimmer kann nun bei Typ sowas stehen wie: EZ, DZ, EZ-DU, DZ-DU

    Ich muss nun eine gespeicherte Prozedur schreiben, welche anhand eines Start-und Enddatums pro Tag die Zimmerverteilung ermittelt. Das Ergebnis soll dann in Abhängigkeit der Zimmertypen so aussehen:

    @startdatum='03.06.2016'
    @enddatum='05.06.2016'
    Datum      | FreieZimmer | EZ | EZ-DU | DZ | DZ-DU
--------------------------------------------------
03.06.2016       20         5     5      5     5
04.06.2016       15         5     0      5     0
05.06.2016       10         0     5      5     0

    Mein Problem sind nun die letzten vier Spalten aus dem Beispiel, welche ja Zeilen aus der Zimmertabelle sind.

    Hat jemand einen Tipp für mich?

    Viele Grüße

    Thorsten

    Freitag, 3. Juni 2016 09:39
    |

Antworten

Alle Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hallo Thorsten,

    poste bitte CREATE TABLE Statements für beide Tabellen und dazu bitte INSERT INTO Statements mit Beispieldaten, die zu dem gewünschten Ergebnis passen.

    Das erleichtert uns die Arbeit enorm.

    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    Freitag, 3. Juni 2016 10:04
    |
    Moderator
  • Question
    Anmelden
    1
    Anmelden

    Hallo Thorsten,

    das bekommst Du entweder mit Unterabfragen (sub-queries) oder mit der Pivot Funktion hin, siehe Verwenden von PIVOT und UNPIVOT

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Als Antwort markiert eclere Freitag, 3. Juni 2016 13:23
    Freitag, 3. Juni 2016 10:30
    |
  • Question
    Anmelden
    0
    Anmelden

    Hi,

    kein Problem. Ich musste aber das Modell etwas anpassen damit es dann auch tatsächlich funktioniert. Ich wollte es oben eben so einfach wie möglich gestalten.

    USE [TestDB]
GO
/****** Object:  Table [dbo].[Belege]    Script Date: 03.06.2016 12:38:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Belege](
	[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Belege_Id]  DEFAULT (newid()),
	[Bezeichnung] [nvarchar](100) NOT NULL,
	[Beginn] [datetime2](7) NOT NULL,
	[Ende] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Belege] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[BelegPositionen]    Script Date: 03.06.2016 12:38:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BelegPositionen](
	[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_BelegPositionen_Id]  DEFAULT (newid()),
	[Beleg_Id] [uniqueidentifier] NOT NULL,
	[Zimmer_Id] [uniqueidentifier] NOT NULL,
	[Bezeichnung] [nvarchar](100) NULL,
	[Beginn] [datetime] NOT NULL,
	[Ende] [datetime] NOT NULL,
 CONSTRAINT [PK_BelegPositionen] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[Zimmer]    Script Date: 03.06.2016 12:38:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Zimmer](
	[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Zimmer_Id]  DEFAULT (newid()),
	[Typ] [varchar](55) NOT NULL,
	[Zimmernummer] [char](3) NULL,
	[Bezeichnung] [nvarchar](100) NOT NULL,
	[BettenAnzahl] [int] NOT NULL,
 CONSTRAINT [PK_Zimmer#] PRIMARY KEY CLUSTERED 
(
	[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
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Belege] ([Id], [Bezeichnung], [Beginn], [Ende]) VALUES (N'f25e5bdc-fe4f-42ff-b2fa-5301bbf658fe', N'Übernachtung im Einzelzimmer', CAST(N'2016-06-04 00:00:00.0000000' AS DateTime2), CAST(N'2016-06-06 00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[Belege] ([Id], [Bezeichnung], [Beginn], [Ende]) VALUES (N'd7eebf0a-486f-47ce-bf9f-7e63127f919f', N'Übernachtung im Doppelzimmer', CAST(N'2016-06-05 00:00:00.0000000' AS DateTime2), CAST(N'2016-06-07 00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[Belege] ([Id], [Bezeichnung], [Beginn], [Ende]) VALUES (N'dc73c687-5b18-4d6a-a103-c90d764a8afc', N'Übernachtung im Einzelzimmer', CAST(N'2016-06-06 00:00:00.0000000' AS DateTime2), CAST(N'2016-06-07 00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[Belege] ([Id], [Bezeichnung], [Beginn], [Ende]) VALUES (N'58de88fe-baeb-4f4a-b0c1-f28d5cfe8f52', N'Übernachtung im Doppelzimmer', CAST(N'2016-06-07 00:00:00.0000000' AS DateTime2), CAST(N'2016-06-08 00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[BelegPositionen] ([Id], [Beleg_Id], [Zimmer_Id], [Bezeichnung], [Beginn], [Ende]) VALUES (N'1a7ab68c-3471-45c8-9327-216b1dcbcf60', N'd7eebf0a-486f-47ce-bf9f-7e63127f919f', N'fdb33b5f-b644-46dd-822d-751222541805', N'Doppelzimmer', CAST(N'2016-06-05 00:00:00.000' AS DateTime), CAST(N'2016-06-07 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[BelegPositionen] ([Id], [Beleg_Id], [Zimmer_Id], [Bezeichnung], [Beginn], [Ende]) VALUES (N'096c6df4-522f-4709-ab95-7887c8e97835', N'dc73c687-5b18-4d6a-a103-c90d764a8afc', N'8f4446f4-874c-4b44-b389-830fd620bb0c', N'Einzelzimmer', CAST(N'2016-06-06 00:00:00.000' AS DateTime), CAST(N'2016-06-07 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[BelegPositionen] ([Id], [Beleg_Id], [Zimmer_Id], [Bezeichnung], [Beginn], [Ende]) VALUES (N'2642c262-e885-4668-9146-7965fb8ba12e', N'f25e5bdc-fe4f-42ff-b2fa-5301bbf658fe', N'c305a32b-ba20-44a3-933e-cd54e112307d', N'Einzelzimmer', CAST(N'2016-06-04 00:00:00.000' AS DateTime), CAST(N'2016-06-06 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[BelegPositionen] ([Id], [Beleg_Id], [Zimmer_Id], [Bezeichnung], [Beginn], [Ende]) VALUES (N'63bacc8f-455b-4ed1-b125-c14acfaee104', N'58de88fe-baeb-4f4a-b0c1-f28d5cfe8f52', N'fdb33b5f-b644-46dd-822d-751222541805', N'Doppelzimmer', CAST(N'2016-06-07 00:00:00.000' AS DateTime), CAST(N'2016-06-08 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Zimmer] ([Id], [Typ], [Zimmernummer], [Bezeichnung], [BettenAnzahl]) VALUES (N'f3311049-9cc1-477d-a1e2-63a5fb0642c0', N'DZ', N'004', N'Doppelzimmer', 2)
GO
INSERT [dbo].[Zimmer] ([Id], [Typ], [Zimmernummer], [Bezeichnung], [BettenAnzahl]) VALUES (N'fdb33b5f-b644-46dd-822d-751222541805', N'DZ-DU', N'003', N'Doppelzimmer mit Dusche', 2)
GO
INSERT [dbo].[Zimmer] ([Id], [Typ], [Zimmernummer], [Bezeichnung], [BettenAnzahl]) VALUES (N'8f4446f4-874c-4b44-b389-830fd620bb0c', N'EZ-DU', N'002', N'Einzelzimmer mit Dusche', 1)
GO
INSERT [dbo].[Zimmer] ([Id], [Typ], [Zimmernummer], [Bezeichnung], [BettenAnzahl]) VALUES (N'c305a32b-ba20-44a3-933e-cd54e112307d', N'EZ', N'001', N'Einzelzimmer', 1)
GO
ALTER TABLE [dbo].[BelegPositionen]  WITH CHECK ADD  CONSTRAINT [FK_BelegPositionen_Belege] FOREIGN KEY([Beleg_Id])
REFERENCES [dbo].[Belege] ([Id])
GO
ALTER TABLE [dbo].[BelegPositionen] CHECK CONSTRAINT [FK_BelegPositionen_Belege]
GO
ALTER TABLE [dbo].[BelegPositionen]  WITH CHECK ADD  CONSTRAINT [FK_BelegPositionen_Zimmer] FOREIGN KEY([Zimmer_Id])
REFERENCES [dbo].[Zimmer] ([Id])
GO
ALTER TABLE [dbo].[BelegPositionen] CHECK CONSTRAINT [FK_BelegPositionen_Zimmer]
GO

    Gruß Thorsten

    Freitag, 3. Juni 2016 10:38
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo Olaf,

    so wie ich das verstehe muss ich bei Pivot ja auch die Anzahl der Spalten kennen. Zumindest in dem Beispiel ist das so gemacht.

    Gruß Thorsten

    Freitag, 3. Juni 2016 10:44
    |
  • Question
    Anmelden
    1
    Anmelden

    Hallo Thorsten,

    zu PIVOT mit dynamischen Spalten hatten wir gestern oder vorgestern schon einen Thread. Schau da mal bitte rein.

      https://social.msdn.microsoft.com/Forums/de-DE/820c4f1c-f387-495f-9ac2-384bee113ea5/abfrage-per-pivot?forum=sqlserverexpressde

    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    • Als Antwort markiert eclere Freitag, 3. Juni 2016 13:22
    Freitag, 3. Juni 2016 10:53
    |
    Moderator
  • Question
    Anmelden
    0
    Anmelden

    Hallo,

    einen kleinen Tippp brauche ich noch:)

    CREATE TABLE #Test
(Bezeichnung VARCHAR(40), Beginn date, Objekte int)

insert into #Test VALUES ('EZ', '03.06.2015', 2)
insert into #Test VALUES ('EZ', '03.06.2015', 3)
insert into #Test VALUES ('DZ', '04.06.2015', 6)
insert into #Test VALUES ('DZ', '05.06.2015', 11)
insert into #Test VALUES ('DZ', '06.06.2015', 7)
insert into #Test VALUES ('EZ-DU', '07.06.2015', 2)
insert into #Test VALUES ('DZ-DU', '08.06.2015', 2)


SELECT  * FROM #Test

SELECT * FROM #Test
PIVOT(sum(Objekte) for Bezeichnung in (['ÜN im DZ'], ['ÜN im EZ'])) as PVTABLE 

SELECT SUM(Objekte) from #Test

drop Table #Test

    Ich bekomme keine Summe in der Pivot-Tabelle. Weiss jemand warum?

    Gruß Thorsten

    Freitag, 3. Juni 2016 13:19
    |
  • Question
    Anmelden
    0
    Anmelden

    Mist, hätte ich noch eine Minute länger probiert...

    Die Hochkommas sind schuld. Jetzt bekomme ich es hin.

    Danke für die Tipps.

    Gruß

    Thorsten

    Freitag, 3. Juni 2016 13:22
    |