Crosstab query

Cant you just group on date and computer summing on labels?

Cross Tab er ikke understøttet på samme måde i SQL Server som i Access. IOW: Transform findes ikke i SQL Server, og det gør Pivot heller ikke.

I stedet er du nødt til selv at summere nogle ting sammen og foretage en forespørgsel på det.

Jeg har kode til Functions og Stored Procedures, som kan lave det samme som en Transform fra Access - og det fungerer udmærket.

Det giver mig ikke det samme resultat som en crosstab.

ex.
        PC001  PC012    PC309
1-1-01  1000    100      0
2-2-01  0      50      9987

Lad endelig høre Benny

Jeg forlader skuden her nu, så jeg vender ikke tilbage før i morgen tidlig.

Jeg finder noget til i morgen... Har du den Transform, der skal konverteres?

Bruger du SQL Server 2K? (vigtigt)

Why do you need a crosstab query to solve this ?

SELECT fldComputer, fldDate, Sum(fldNumLabels) AS TotalForPC
FROM tblPrintLog
GROUP BY fldComputer, fldDate

doesnt this give you what you want?

OK, that way!

Her følger så de elementer, der skal bruges til krydstabuleringen. Det består af to dele - en funktion og en stored procedure.

For at anvende det, kræves en SQL Server 2000, da tidligere versioner ikke undersøtter User Defined Functions

Følgende udføres i Query Analyzer mod den ønskede database:

create function fn_tblPrintLogSummation
-- Opretter en summering af data i tblPrintLog ud fra fldDate og fldComputer inden for datoafgrænsningerne
(
    @Param_FromDate    datetime,
    @Param_ToDate    datetime
)
returns table
as
    return
        (    -- top 100 percent er nødvendig for at kunne bruge order by.
            select        top 100 percent fldDate, fldComputer, Sum(fldNumLabels) as NoOfLabels
            from        tblPrintLog
            where        (fldDate between @Param_FromDate and @Param_ToDate)
            group by    fldDate, fldComputer
            order by        fldDate, fldComputer
        )

Derefter udføres i Query Analyzer mod samme database:

create procedure sp_tblPrintLogCrossTab
(
    @Param_FromDate    datetime,
    @Param_ToDate    datetime
)
as
    set nocount on
   
    -- Vi skal bruge en midlertidig tabel. Denne bliver oprettet i tempdb, og kan kun ses fra host maskinen
    create table #tempTable
    (
        fldDate            datetime
    )
   
    -- Indsæt alle de datoer, der er fundet i summeringen
    insert    #tempTable (fldDate)
    select    distinct fldDate
    from    fn_tblPrintLogSummation(@Param_FromDate, @Param_ToDate)
    order by    fldDate
   
    -- Vi skal bruge en cursor for at finde alle de computernavne, der er fundet i summeringen
    declare    @Comp    sysname
    declare    CompCursor cursor fast_forward for
        select    distinct fldComputer
        from    fn_tblPrintLogSummation(@Param_FromDate, @Param_ToDate)
        order by    fldComputer
   
    open    CompCursor
    fetch next from CompCursor into @Comp
   
    while @@fetch_status = 0 begin
        -- For hver computernavn, vi finder, opretter vi en kolonne med det navn. Derfor distinct i cursor-erklæringen.
        exec (\'alter table #tempTable add [\' + @Comp + \'] int not null default 0\')
   
        -- Så skal den midlertidige tabel opdateres med antal labels for den dag for den computer.
        exec (    \'update    #tempTable \' +
                \'set        [\' + @Comp +\'] = logsum.NoOfLabels \' +
                \'from    #tempTable T \' +
                        \'inner join fn_tblPrintLogSummation(\'\'\' + @Param_FromDate + \'\'\', \'\'\' + @Param_ToDate +\'\'\') as logsum \' +
                        \'on T.fldDate=logsum.fldDate \' +
                \'where    logsum.fldComputer=\'\'\' + @Comp +\'\'\'\' )
   
        -- Hent den næste
        fetch next from CompCursor into @Comp
    end

    -- Så er vi færdige med cursoren
    close    CompCursor
    deallocate    CompCursor
   
    -- Her tilføjes en total-kolonne per dato (kan udelades)
    alter table #tempTable
        add        TotalNoOfLabels int not null default 0
   
    -- Og opdatering af totalkolonnen
    exec (    \'update    #tempTable \' +
            \'set        [TotalNoOfLabels] = logsum.SumNoOfLabels \' +
            \'from    #tempTable T \' +
                    \'inner join    (    select        fldDate, Sum(NoOfLabels) as SumNoOfLabels \' +
                                    \'from        fn_tblPrintLogSummation(\'\'\' + @Param_FromDate + \'\'\', \'\'\' + @Param_ToDate +\'\'\') \' +
                                    \'group by    fldDate) as logsum \' +
                    \'on T.fldDate=logsum.fldDate\')
   

    -- Denne er væsentlig, hvis resultatet skal bruges fra VB.   
    set nocount off
   
    -- Dette returnerer krydstabuleringen
    select    *
    from    #tempTable
   
    set nocount on
   
    -- Så er vi færdige med den midlertidige tabel
    drop table #tempTable

Anvendelse:

exec sp_tblPrintLogCrossTab N\'12/1/2001\', N\'12/3/2001\'

giver krydstabuleringen for datointervallet 1/12-2001 til 3/12-2001.

Min query i Access ser ud som følgende
TRANSFORM Sum([tblPrintLog].[fldNumLabels]) AS SumOffldNumLabels
SELECT [tblPrintLog].[fldDate] AS Dato
FROM tblPrintLog
WHERE ((([tblPrintLog].[fldDate]) Is Not Null))
GROUP BY [tblPrintLog].[fldDate]
PIVOT [tblPrintLog].[fldComputer];

Ja jeg bruger SQL 2000 ( Access som frontend i dette tilfælde - ellers Navision )

Jeg vil se om jeg kan få det til at spille med din løsning Benny.

Jeg har nu afprøvet din løsning Benny og bortset fra enkelte detaljer, så er det OK.

Den dato der returneres indeholder også klokkeslæt, men det må vel kunne ændres til at være smalldatetime!
Næste opgave bliver så at få data over i Excel så jeg kan lave en fin graf over dem.
Jeg må kunne flytte den midlertidige tabel over i min database og så bruge den som recordsource for mit Excel ark ( jeg ved godt at jeg kan lave crostabben i Excel, men jeg skal bruge de samme data til flere ting, så for at være sikker på at de er konsistente vil jeg helst lave så meget så tæt på de orginale data som muligt. )

Hvilket værktøj skal du bruge for at få det i Excel? Hvis du bruger VB eller Excel VBA, så kan du åbne den stored procedure som et forward only recordset (serverside) eller clientside (ADODB.Recordset)

Jeg plejer bare at lave en database query direkte fra Excel!  Det plejer at virke ganske udmærket bortset fra at data skal manuelt opdateres !

Kan du tage fat i stored procedures der? (Jeg har ikke prøvet)

Det kan jeg ikke nej - kun views eller tables ;-(