Crosstabs på SQL server

ja... brug sql-server 2000 og dts

jeg forstår ikke hvad du konkret vil! Vil du skifte fra Access til SQl-server eller fra access til access --> du opretter jo ? i sql-serveren kategorien!

så vidt jeg ved virker crosstab queries på sql-server uden problemer... lidt syntax fra sql-server 7.0 BOL:


USE Northwind

GO

 

CREATE TABLE Pivot

( Year        SMALLINT,

  Quarter    TINYINT,

  Amount        DECIMAL(2,1) )

GO

INSERT INTO Pivot VALUES (1990, 1, 1.1)

INSERT INTO Pivot VALUES (1990, 2, 1.2)

INSERT INTO Pivot VALUES (1990, 3, 1.3)

INSERT INTO Pivot VALUES (1990, 4, 1.4)

INSERT INTO Pivot VALUES (1991, 1, 2.1)

INSERT INTO Pivot VALUES (1991, 2, 2.2)

INSERT INTO Pivot VALUES (1991, 3, 2.3)

INSERT INTO Pivot VALUES (1991, 4, 2.4)

GO

 

This is the SELECT statement to create the rotated results:

SELECT Year,

    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,

    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,

    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,

    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4

FROM Northwind.dbo.Pivot

GROUP BY Year

GO

 

hej Slash,

Jeg vil bare konvertere data således at jeg har min Access-front-end og en SQl-server backend.

Kan du forklare lidt nærmere hvordan jeg for DTS til at lave en crosstab. Jeg har lige kigget på det og er lidt lost ;-)

Dit eksempel med Create Table Pivot er ikke så attraktivt, da jeg ikke ønsker at oprette en ny tabel, men blot lave et view (eller lign) der returnere det ønskede resultat...

Sorry, det sidste var ikke helt korrekt.

Eksemplet duer ikke, fordi jeg ikke på forhånd ved hvormange kolonner der skal være (og jeg kender slet ikke værdierne)

/Thomas

okay.... her er hvad jeg just har gjort i sql-serveren for at lave et crosstab:

1) Kørt følgende i Query analyzer:

CREATE TABLE Pivot
( Year        SMALLINT,
  Quarter    TINYINT,
  Amount        DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

2) Oprettet en stored procedure med følgende:

SELECT Year,
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END)
    AS Q1,
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END)
    AS Q2,
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END)
    AS Q3,
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END)
    AS Q4
FROM northwind.dbo.Pivot
GROUP BY Year

3) Eksekveret min just oprettede stored procedure  i query analyzer og outputtet blev
Year  Q1                                      Q2                                      Q3                                      Q4                                     
------ ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
1990  1.1                                      1.2                                      1.3                                      1.4
1991  2.1                                      2.2                                      2.3                                      2.4

Så kan du jo bare klade din stored procedure fra f.eks. .asp, vb whatever!

Hej igen,

Ja, men det forudsætter, at jeg ved at der findes en Quarter med værdierne 1, 2, 3 og 4.

Dette er ikke kendte værdier, ligesom jeg ikke ved om der er 4 forskellige værdier eller 200!

Kan du se problemet?

hvis jeg skulle lave en sp, som skulle gøre det, så skal den køre et loop gennem det samme antal gange, som der er poster i \"SELECT DISTINCT Quarter FROM PIVOT\". Men hvordan gør jeg det?

/Thomas

2 sek

Jeg glemte at nævne, at det hele aller helst skulle kunne fungere på SQL server 7.0 (gerne endnu tidligere) men det er ikke et krav...(det er så fedt, når kunder ændrer kravspec midt i udviklingsforløbet ;-)

et umiddelbart forslag til loop kunne være dette: det er slet ikke testet, da det er skrevet direkte her på experten!

declare @next_id int

select @next_id = 0
While @next_id Is Not NULL
Begin
  Select @next_id = Min(quarter_id) from PIVOT where quarter_id > @next_id

      If @next_id Is Not Null
    Begin

    .....noget select.....
        end
end

det jeg skriver her virker 100% under sql 7.0! Måske skal du installere sql-server 7.0 olap services hvis der er noget der ikke funger!

hmm, det der \"....noget select....\"; kunne du uddybe det? Jeg er nemlig ikke helt med på, hvad det er du vil. Og Transact SQL er ikke min stærke side...

ja i noget select kunne du gøre det, at du selectede dine quarters én for en med de respektive værdier og lagde dem over i en temporær tabel, hvorfor du så kunne selecte med crosstab!

Det er lidt en kompleks problemstilling du har, og jeg kan ikke pt. overskue det helt - men prøv dig lidt frem.... har du ? kan du bare spørge løs på experten!

ok, jeg prøver mig lidt frem og vender tilbage med nogle points.

Du skal indtil videre have tak :-)

Slash,

Jeg har rent faktisk fået det næsten til at virke. Mit eneste problem er, at den SQLstreng, som jeg opbygger dynamisk ikke giver det ønskede resultat, når jeg åbner den fra Access? Hvorimod den fungere fint, hvis jeg køre den fra Query Analyzer\'en?

Alter PROCEDURE Crosstab 
AS

declare @next_id int
declare @TMPSTR varchar(4096)

select @TmpStr = \"SELECT Year,\"
select @next_id = 0

While @next_id Is Not NULL
Begin
    Select @next_id = Min(quarter) from PIVOT where quarter > @next_id
    If @next_id Is Not Null
    Begin
        select @TmpStr = @TmpStr + \"SUM(CASE Quarter WHEN \" + LTrim(str(@Next_Id)) + \" THEN Amount ELSE 0 END) AS \'\" + LTrim(Str(@Next_Id)) + \"\',\"
        End
end
select @TmpStr = Substring(@TmpStr, 1, Len(@TmpStr)-1) + \" FROM Northwind.dbo.Pivot GROUP BY Year\"

Exec(@TmpStr)
Go


Access fortæller bare at proceduren blev udført, men at der ikke blev returneret nogle poster...?

Et par gange har det dog lykkedes den at vise de 2 første kolonner i Crosstab\'en (år og quarter 1).

Har du et bud?

hmm..... lidt svært at sige hvad du skal prøve, idet access jo just ikke er sql-server! Har desv. ik\' access installeret, men kan man anvende print @variabelnavn i access ?? altså noget i retning af print @TmpStr (det er et totalt vildt gæt!!)

Hej Slash,

Jeg har selv prøvet med Print, men det \'skinner\' ikke igennem til Access. Access opfører sig i det hele taget lidt mærkeligt med de der Stored Procedures. Ovenstående kode giver f.eks. en fejl, når Access skal syntaks-checke, mens det går fint i Enterprise Manageren.

Microsoft i en nøddeskal :-)

Jeg giver dig 250 point, da der stadig manglede en del manuelt arbejde. Men du skal i hvert fald have mange tak :-)

mvh
Thomas

sorry, 247 blev det kun..håber, du overlever? ;-)

glad for at jeg kunne hjælpe dig på vej :-)