Query-Suche...

Question

Hi,

Ausgangspunkt ist eine Tabelle mit 3 Spalten (User, Date, Status). Diese enthält eine n-Anzahl an sich wiederholenden UserKeys(int), zu jedem Key ein beliebiges Datum und einen Status (alphanumerisch, einstellig).

Jetzt muss ich eine Tabelle pro Monat erzeugen, in welcher die Userkeys (Distinct) in Spalte 1 stehen und weitere 30/31 Spalten erzeugt werden, in welche jeweils der Status für den User und den entsprechenden Tag des Monats geschrieben werden. Wie kann ich das bewerkstelligen?

In der Hoffnung, das mir jemand von Euch einen Lösungstipp geben kann,

Klaus

---

No Brain - No Pain

Answer 1

Hallo Klaus,

das was Du da möchtest entspricht aber nicht gerade der Arbeitsweise von Datenbanken. Eine Tabelle für jeden Monat ist von der Arbeitsweise sowie vom Datenbankdesign her nicht gerade der beste Weg. Dies entspricht eher der Arbeitsweise von Excel und genau da kannst Du dies auch sehr gut darstellen. Dies geht in dem Du in einer parametrisierten SQL Abfrage die Daten aus Deiner Ausgangstabelle holst und mit DISTINCT und PIVOT solltest Du auch die entsprechende Formatierung hin bekommen.

---

Gruß Falk

Answer 2

Hallo Falk,

ich denke mal, Klaus meinte eine Abfrage (und die Ausgabe in einer Tabelle, bspw. in einer WinForms Anwendung oder ähnlichem).

 

---

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

Answer 3

Dein Problembeschreibung ist nicht präzise genug. Im Besonderen mit dieser Voraussetzung:

zu jedem Key ein beliebiges Datum und einen Status

d.h.du kannst für einen User zwei Einträge an einem Tag mit unterschiedlichen Stati haben. Diese Möglichkeit kollidiert mit deiner Anforderung:

jeweils der Status für den User und den entsprechenden Tag

d.h.ein Status pro Tag und User.

Für ein Pivot oder Cross-Tab brauchst du eine Kalenderhilfstabelle (Calendar Tally) oder eine normale Nummerntabelle:

CREATE TABLE #Sample 
    (
      [User] VARCHAR(32) ,
      [Date] DATE ,
      [Status] VARCHAR(32)
    ) ;
    
INSERT  INTO #Sample
VALUES  ( '1', '20110101', '1a' ),
        ( '1', '20110102', '2a' ),
        ( '1', '20110103', '1b' ),
        ( '1', '20110104', '1a' ),
        ( '1', '20110105', '1a' ) ,
        ( '2', '20110107', '1a' ),
        ( '2', '20110102', '2a' ),
        ( '2', '20110103', '1b' ),
        ( '2', '20110104', '1a' ),
        ( '3', '20110103', '1a' ) ;

DECLARE @Columns VARCHAR(MAX) ;
WITH    Numbers ( N )
          AS ( SELECT   1
               UNION ALL
               SELECT   N + 1
               FROM     Numbers
               WHERE    N < 31
             )
    SELECT  @Columns = COALESCE(@Columns + ', ' + QUOTENAME(N), QUOTENAME(N))
    FROM    Numbers ;

DECLARE @Sql VARCHAR(MAX) = '
SELECT  *
FROM    ( SELECT    [User] ,
                    [Status] ,
                    DAY([Date]) AS DateDay
          FROM      #Sample
        ) Src PIVOT ( MAX([Status]) FOR [DateDay] IN ( ' + @Columns + ' ) ) pvt ;
' ;

EXEC (@Sql);

Answer 4

Hallo Klaus,

das hier wäre ein Lösungsansatz:

CREATE TABLE #tbl_temp (
	[User] nvarchar(50) not null, 
	[Date] date not null, 
	[Status] nvarchar(10)
)
GO

INSERT INTO #tbl_temp ([User], [Date], [Status]) Values 
	('User1', CONVERT(DATE,'2011-01-01'),'A'),
	('User1', CONVERT(DATE,'2011-01-02'),'B'),
	('User1', CONVERT(DATE,'2011-01-03'),'C'),
	('User1', CONVERT(DATE,'2011-01-04'),'A'),
	('User1', CONVERT(DATE,'2011-01-05'),'B'),
	('User1', CONVERT(DATE,'2011-01-06'),'C'),
	('User1', CONVERT(DATE,'2011-01-07'),'D'),
	('User1', CONVERT(DATE,'2011-01-08'),'A'),
	('User1', CONVERT(DATE,'2011-01-09'),'B'),
	('User1', CONVERT(DATE,'2011-01-10'),'A'),
	('User2', CONVERT(DATE,'2011-01-01'),'a'),
	('User2', CONVERT(DATE,'2011-01-02'),'b'),
	('User2', CONVERT(DATE,'2011-01-03'),'c'),
	('User2', CONVERT(DATE,'2011-01-04'),'a'),
	('User2', CONVERT(DATE,'2011-01-05'),'a'),
	('User2', CONVERT(DATE,'2011-01-06'),'a'),
	('User2', CONVERT(DATE,'2011-01-07'),'a'),
	('User2', CONVERT(DATE,'2011-01-08'),'b'),
	('User2', CONVERT(DATE,'2011-01-09'),'b'),
	('User2', CONVERT(DATE,'2011-01-10'),'b');
	
	
SELECT [User],
	MAX(CASE WHEN DAY([Date]) = 1 THEN [Status] ELSE '' END) AS '1.',
	MAX(CASE WHEN DAY([Date]) = 2 THEN [Status] ELSE '' END) AS '2.',
	MAX(CASE WHEN DAY([Date]) = 3 THEN [Status] ELSE '' END) AS '3.',
	MAX(CASE WHEN DAY([Date]) = 4 THEN [Status] ELSE '' END) AS '4.',
	MAX(CASE WHEN DAY([Date]) = 5 THEN [Status] ELSE '' END) AS '5.',
	MAX(CASE WHEN DAY([Date]) = 6 THEN [Status] ELSE '' END) AS '6.',
	MAX(CASE WHEN DAY([Date]) = 7 THEN [Status] ELSE '' END) AS '7.',
	MAX(CASE WHEN DAY([Date]) = 8 THEN [Status] ELSE '' END) AS '8.',
	MAX(CASE WHEN DAY([Date]) = 9 THEN [Status] ELSE '' END) AS '9.',
	MAX(CASE WHEN DAY([Date]) = 10 THEN [Status] ELSE '' END) AS '10.',
	MAX(CASE WHEN DAY([Date]) = 11 THEN [Status] ELSE '' END) AS '11.'
	
FROM #tbl_temp
WHERE MONTH([Date]) = 1
GROUP BY [user]
GO

DROP TABLE  #tbl_temp
GO

 

Vielleicht kannst Du darauf aufsetzen.

Gruß Thomas

Answer 5

Hallo Stefan,

Danke für das Snippet! Habe durch den Tipp von Falk eine Pivotabfrage zum testen erstellt, welche eigentlich das erledigt, worauf ich hinaus will. Das Problem der Ausgabespalten würde ich in der Programmlogik lösen, welche den Query schlussendlich generiert, da hier die benötigten Zeiträume nicht immer exakt auf einen Monat begrenzt werden können.

select * from
(SELECT UKEY, CONVERT(VARCHAR(10),DAYS,104) [dd] ,USTATUS FROM TEST) p
PIVOT
(
MAX(USTATUS) FOR 
[dd] IN
(
[01.11.2011],
[02.11.2011],
[03.11.2011],
[04.11.2011],
[05.11.2011],
....
)
)PivotTable

Nun durfte ich aber feststellen, das es in den Ausgangsdaten vorkommen kann, das ein User an einem Tag mit 2 Stati aufgeführt ist (2 Zeilen). Wie könnte ich diese in der Abfrage zusammenführen, so das als Ausgabe beispielsweise

UKEY   [01.11.2011]   [02.11.2011]

1         A                      AB

herauskommt?

Danke für Eure Hilfe!

Klaus

---

No Brain - No Pain

Answer 6

Mit meinem Beispiel von oben:

DECLARE @Sql VARCHAR(MAX) = '
WITH    Compacted
          AS ( SELECT   [User] ,
                        [Date]
               FROM     #Sample
               GROUP BY [User] ,
                        [Date]
             ),
        Concatenated
          AS ( SELECT   * ,
                        ( STUFF(( SELECT    '', '' + I.[Status]
                                  FROM      #Sample I
                                  WHERE     I.[User] = O.[User]
                                            AND I.[Date] = O.[Date]
                                FOR
                                  XML PATH('''')
                                ), 1, 2, '''') ) AS [Status]
               FROM     Compacted O
             )
    SELECT  *
    FROM    ( SELECT    [User] ,
                        [Status] ,
                        DAY([Date]) AS DateDay
              FROM      Concatenated
            ) Src PIVOT ( MAX([Status]) FOR [DateDay] IN ( ' + @Columns + ' ) ) pvt ;
' ;

Answer 7

Top! Vielen vielen Dank Stefan !!!!!! :-)

 

Schöne Grüße,

Klaus

---

No Brain - No Pain