none
Werte aus Zeilen in Spalten bringen RRS feed

  • Frage

  • Hallo zusammen,


    ich bräuchte eine unbekannte Zahl von Datensätzen nummeriert in Spalten angezeigt. Beispiel:


    Select 
    	t2.fldBeprobungID, 
    	t2.fldDatumMessung
    from
    	tblNachbeprobung t2
    where
    	t2.fldBeprobungID = 32
    
    Ergebnis:
    ID      Datum
    32	2013-08-22
    32	2013-09-10
    32	2013-11-05
    32	2014-11-19

    Wunschergebnis:


    ID    Datum1        Datum2       Datum3        Datum4
    
    32   2013-08-22   2013-09-10    2013-11-05     2014-11-19


    Mit MIN + MAX hab ich schon mal 2 Datensätze, aber wie krieg ich die unbekannte Anzahl der Datensätze dazwischen und in die richtige Reihenfolge und eine vernünftige Benennung der Spalten on the Fly hin? Geschwindigkeit der Ausführung ist nicht wichtig. Kann mich jemand in die richtige Richtung schubsen?

    Vielen Dank schon im Voraus.


    Servus
    Winfried

    Gruppenrichtlinien
    HowTos zum WSUS Package Publisher
    WSUS Package Publisher
    HowTos zum Local Update Publisher
    NNTP-Bridge für MS-Foren

    Mittwoch, 28. Oktober 2015 16:03

Antworten

  • Hallo Winfried,

    use tempdb; go -- Testdaten erstellen create table #tblNachbeprobung (fldBeprobungID int, fldDatumMessung varchar (30)); insert into #tblNachbeprobung (fldBeprobungID, fldDatumMessung) select 32, '2013-08-22' union select 32, '2013-09-10' union select 32, '2013-11-05' union select 32, '2014-11-19'; select * from #tblNachbeprobung -- max. Anzahl Messungen pro ID ermitteln declare @maxfldDatumMessung int; with cte (fldDatumMessung) AS ( select row_number() over (partition by fldBeprobungID order by fldDatumMessung) as rownumber from #tblNachbeprobung ) select @maxfldDatumMessung = max(fldDatumMessung) from cte; declare @cols nvarchar(max); declare @stmt nvarchar(max); declare @counter int; -- string für die Pivot Spalten set @counter = 0; while @counter < @maxfldDatumMessung begin set @counter = @counter + 1; set @cols = ISNULL( @cols + ']' + ', [', '' ) + cast(@counter as nvarchar(max)); end set @cols = '[' + @cols + ']'; -- Pivot dynamisch set @stmt = N'SELECT * FROM ( SELECT fldBeprobungID, row_number() OVER (PARTITION BY fldBeprobungID ORDER BY fldBeprobungID) AS rownumber, fldDatumMessung FROM #tblNachbeprobung ) As u PIVOT ( MAX(u.fldDatumMessung) FOR u.rownumber IN (' + @cols + ')) AS pv ORDER BY fldBeprobungID'; print @stmt; exec sp_executesql @stmt;

    drop table #tblNachbeprobung;

    Schönen Abend.

    Mittwoch, 28. Oktober 2015 20:22
  • Guter Vorschlag!
    Ich habe ihn noch leicht angepasst, damit die Überschriften
    passen.
    use tempdb;
     go
     -- Testdaten erstellen
     create table 
     #tblNachbeprobung (fldBeprobungID int, fldDatumMessung date);
    
     insert into 
     #tblNachbeprobung (fldBeprobungID, fldDatumMessung)
     select 32, '2013-08-22' 
     union
     select 32, '2013-09-10' union
     select 32, '2013-11-05' 
     union
     select 32, '2014-11-19' union
     select 32, '2014-11-20' 
     union
     select 32, '2015-09-10' union
     select 32, '2015-11-05' 
     union
     select 32, '2015-11-19' union
     select 32, '2015-11-20' 
     union
     select 32, '2015-11-21';
    
     select * from 
     #tblNachbeprobung;
    
     -- max. Anzahl Messungen pro ID ermitteln
     declare 
     @maxfldDatumMessung int;
     with cte (fldDatumMessung) AS
     (
     select 
     row_number() over (partition by fldBeprobungID
     order by fldDatumMessung) as 
     rownumber
     from #tblNachbeprobung
     )
     select @maxfldDatumMessung = 
     max(fldDatumMessung) from cte;
    
     declare @cols nvarchar(max);
     declare 
     @stmt nvarchar(max);
     declare @counter int;
    
    -- string für die Pivot 
     Spalten
     set @counter = 0;
     while @counter < @maxfldDatumMessung
         
     begin
             set @counter = @counter + 1;
             set @cols = ISNULL( 
     @cols + ']' + ', [', '' ) + 'Datum' + 
     cast(@counter as 
     nvarchar(max));
         end;
    
     set @cols = '[' + @cols + ']';
    
    
     -- 
     Pivot dynamisch
     set @stmt =
     N'SELECT * FROM (
     SELECT 
     fldBeprobungID,
     ''Datum'' + cast(row_number() OVER (PARTITION BY 
     fldBeprobungID ORDER BY 
     fldBeprobungID) as nvarchar(max)) AS 
     rownumber,
     fldDatumMessung
     FROM #tblNachbeprobung) As u
     PIVOT ( 
     MAX(u.fldDatumMessung)
    FOR u.rownumber IN
     (' + @cols + ')) AS pv
     ORDER 
     BY fldBeprobungID';
    
     print @stmt;
     exec sp_executesql @stmt;
     drop 
     table #tblNachbeprobung;
    


    Einen schönen Tag
    noch,
    Christoph
    --
    Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Donnerstag, 29. Oktober 2015 08:20

Alle Antworten

  • Hallo Winfried,

    schau mal ob der hier Dir weiterhilft:

    https://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-SQL/

    Grüße Alexander

    Mittwoch, 28. Oktober 2015 17:22
  • Hallo Winfried,

    use tempdb; go -- Testdaten erstellen create table #tblNachbeprobung (fldBeprobungID int, fldDatumMessung varchar (30)); insert into #tblNachbeprobung (fldBeprobungID, fldDatumMessung) select 32, '2013-08-22' union select 32, '2013-09-10' union select 32, '2013-11-05' union select 32, '2014-11-19'; select * from #tblNachbeprobung -- max. Anzahl Messungen pro ID ermitteln declare @maxfldDatumMessung int; with cte (fldDatumMessung) AS ( select row_number() over (partition by fldBeprobungID order by fldDatumMessung) as rownumber from #tblNachbeprobung ) select @maxfldDatumMessung = max(fldDatumMessung) from cte; declare @cols nvarchar(max); declare @stmt nvarchar(max); declare @counter int; -- string für die Pivot Spalten set @counter = 0; while @counter < @maxfldDatumMessung begin set @counter = @counter + 1; set @cols = ISNULL( @cols + ']' + ', [', '' ) + cast(@counter as nvarchar(max)); end set @cols = '[' + @cols + ']'; -- Pivot dynamisch set @stmt = N'SELECT * FROM ( SELECT fldBeprobungID, row_number() OVER (PARTITION BY fldBeprobungID ORDER BY fldBeprobungID) AS rownumber, fldDatumMessung FROM #tblNachbeprobung ) As u PIVOT ( MAX(u.fldDatumMessung) FOR u.rownumber IN (' + @cols + ')) AS pv ORDER BY fldBeprobungID'; print @stmt; exec sp_executesql @stmt;

    drop table #tblNachbeprobung;

    Schönen Abend.

    Mittwoch, 28. Oktober 2015 20:22
  • Guter Vorschlag!
    Ich habe ihn noch leicht angepasst, damit die Überschriften
    passen.
    use tempdb;
     go
     -- Testdaten erstellen
     create table 
     #tblNachbeprobung (fldBeprobungID int, fldDatumMessung date);
    
     insert into 
     #tblNachbeprobung (fldBeprobungID, fldDatumMessung)
     select 32, '2013-08-22' 
     union
     select 32, '2013-09-10' union
     select 32, '2013-11-05' 
     union
     select 32, '2014-11-19' union
     select 32, '2014-11-20' 
     union
     select 32, '2015-09-10' union
     select 32, '2015-11-05' 
     union
     select 32, '2015-11-19' union
     select 32, '2015-11-20' 
     union
     select 32, '2015-11-21';
    
     select * from 
     #tblNachbeprobung;
    
     -- max. Anzahl Messungen pro ID ermitteln
     declare 
     @maxfldDatumMessung int;
     with cte (fldDatumMessung) AS
     (
     select 
     row_number() over (partition by fldBeprobungID
     order by fldDatumMessung) as 
     rownumber
     from #tblNachbeprobung
     )
     select @maxfldDatumMessung = 
     max(fldDatumMessung) from cte;
    
     declare @cols nvarchar(max);
     declare 
     @stmt nvarchar(max);
     declare @counter int;
    
    -- string für die Pivot 
     Spalten
     set @counter = 0;
     while @counter < @maxfldDatumMessung
         
     begin
             set @counter = @counter + 1;
             set @cols = ISNULL( 
     @cols + ']' + ', [', '' ) + 'Datum' + 
     cast(@counter as 
     nvarchar(max));
         end;
    
     set @cols = '[' + @cols + ']';
    
    
     -- 
     Pivot dynamisch
     set @stmt =
     N'SELECT * FROM (
     SELECT 
     fldBeprobungID,
     ''Datum'' + cast(row_number() OVER (PARTITION BY 
     fldBeprobungID ORDER BY 
     fldBeprobungID) as nvarchar(max)) AS 
     rownumber,
     fldDatumMessung
     FROM #tblNachbeprobung) As u
     PIVOT ( 
     MAX(u.fldDatumMessung)
    FOR u.rownumber IN
     (' + @cols + ')) AS pv
     ORDER 
     BY fldBeprobungID';
    
     print @stmt;
     exec sp_executesql @stmt;
     drop 
     table #tblNachbeprobung;
    


    Einen schönen Tag
    noch,
    Christoph
    --
    Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Donnerstag, 29. Oktober 2015 08:20
  • Hallo zusammen,

    vielen Dank für die Antworten. Wieder was gelernt. ;) Mit ROW-NUMBER() hatte ich schon selbst probiert, aber leider nicht hingekriegt. Hat mir sehr geholfen. ;)


    Servus
    Winfried

    Gruppenrichtlinien
    HowTos zum WSUS Package Publisher
    WSUS Package Publisher
    HowTos zum Local Update Publisher
    NNTP-Bridge für MS-Foren

    Donnerstag, 29. Oktober 2015 09:22
  • Hallo zusammen,

    Anschlussfrage: Wie krieg ich die temporären Daten in eine temporär erweiterte Tabelle?

    ID           NU1                    NU2                    NU2                     NU3                   NU4

    32	2013-08-22	2013-09-10	2013-11-05	2014-11-19	NULL	
    33	2014-11-19	2013-11-05	2013-09-10	2013-08-22	NULL	
    34	2013-09-10	2013-11-05	NULL	NULL	NULL	NULL	NULL	
    

    Die Wunschtabelle krieg ich schon temporär um die nötigen Spalten erweitert, aber ich hab es noch nicht geschafft die temporären Daten in die temporäre Tabellen zu bringen.

    Auszug aus der Teilfunktion mit der Tabellenerweiterung:

    begin
             set @counter = @counter + 1;
             set @cols = ISNULL(@cols + ']' + ', [', '' ) + 'NU' + cast(@counter as nvarchar(max));
    		 Declare @Column varchar(10)
    		 Set @column = 'NU' + cast(@counter as nvarchar(max))
    		 Declare @SQL Varchar(100)
    		 Set @SQL = 'ALTER TABLE dbo.tblTempExcelExportGesamt ADD ' + @Column + ' date'
    		 Exec (@SQL)
    end;

    Kannmir nochmal jemand bitte helfen? Vielen Dank schon im Voraus.


    Servus
    Winfried

    Gruppenrichtlinien
    HowTos zum WSUS Package Publisher
    WSUS Package Publisher
    HowTos zum Local Update Publisher
    NNTP-Bridge für MS-Foren

    Donnerstag, 29. Oktober 2015 15:00
  • So, selbst gefunden, ein simples INTO [Name der Tabelle] reicht aus, die Tabelle wird mit allen benötigten Feldern angelegt und befüllt.

     --Pivot dynamisch
     set @stmt =
     N'SELECT * INTO tblTempNachbeprobung FROM (
     SELECT 
     fldBeprobungID,
     ''NU'' + cast(row_number() OVER (PARTITION BY 
     fldBeprobungID ORDER BY 
     fldBeprobungID) as nvarchar(max)) AS 
     rownumber,
     fldDatumMessung
     FROM tblNachbeprobung) As u
     PIVOT ( 
     MAX(u.fldDatumMessung)
    FOR u.rownumber IN
     (' + @cols + ')) AS pv
     ORDER 
     BY fldBeprobungID';
    
     print @stmt;
     exec sp_executesql @stmt;

    Das hilft mir weiter, Danke fürs anschauen. ;)


    Servus
    Winfried

    Gruppenrichtlinien
    HowTos zum WSUS Package Publisher
    WSUS Package Publisher
    HowTos zum Local Update Publisher
    NNTP-Bridge für MS-Foren

    Donnerstag, 29. Oktober 2015 15:25