MSSQL Server: spørring med virtuelle kolonner?

[ventle]

Jeg skal lage en spørring som henter ut resultater fra tester som blir gjort på ett produkt. Testresultatene ligger lagret i en database som er omtrent sånn ut (forenklet modell laget i Access):

 

 

- Hver Sekvens er en samling av alle stegene i en test

- Hvert steg er en deltest som inngår i en test

- En rapport inneholder resultatene fra kjøringen av en sekvens

- Hver enhet (produkt) kan testes mange ganger, men en rapport kan bare inneholde testresultater fra en enhet

- Steg_Verdi inneholder resultatet fra ett steg i en rapport

 

Jeg vil få ut dataene i en spørring som inneholder alle stegverdiene i alle rapporter, gruppert på hver rapport. En linje/rad i spørringen skal da tilsvare hver rapport, og hver kolonne i spørringen skal inneholde ett steg i den aktuelle rapporten, slik at det blir seende omtrent sånn ut:

 

 

Enhet_Serienummer er listet opp av praktiske/lesbarhetsårsaker, en rapport kan uansett bare tilsvare en enhet med ett serienummer, så i praktiske sammenhenger kan man se på Enhet_Serienummer i eksempelet som Rapport_ID.

 

Kolonnene i spørringsresultatet må jo bygges av innholdet i tabellen Steg, og inneholde alle Steg med samme Sekvens_ID som rapporten (man kan kanskje lage en virtuell kobling mellom Sekvens_ID i Rapport-tabellen og Steg-tabellen for enkelhets skyld?), og verdiene fra Steg_Verdi som matcher både Rapport_ID og Steg_ID nedover (tungvint forklart, håper dere forstår hva jeg mener).

 

Spørsmålet er da hvordan jeg bygger denne spørringen, med riktig gruppering og kobling mellom tabeller? Jeg har prøvd følgende:

SELECT Rapport.Rapport_ID,
Rapport.Enhet_ID,
Rapport.Sekvens_ID,
Enhet.Enhet_ID,
Enhet.Enhet_Serienummer,
Sekvens.Sekvens_ID,
Steg.Steg_ID,
Steg.Sekvens_ID,
Steg.Steg_Navn,
Steg_Verdi.Rapport_ID,
Steg_Verdi.Steg_ID,
Steg_Verdi.Verdi
FROM Rapport JOIN Enhet ON Rapport.Enhet_ID = Enhet.Enhet_ID
JOIN Sekvens ON Rapport.Sekvens_ID = Sekvens.Sekvens_ID
JOIN Steg ON Sekvens.Sekvens_ID = Steg.Sekvens_ID
JOIN Steg_Verdi ON Rapport.Rapport_ID = Steg_Verdi.Rapport_ID AND Steg.Steg_ID = Steg_Verdi.Steg_ID
GROUP BY Rapport.Rapport_ID

 

dette gir feilmeldingen:

Column 'Rapport.Enhet_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Jeg har veldig lite peiling på hvordan man skal bruke JOIN når man har flere enn to tabeller å ta fra, og tipper det er her feilen ligger. Noen innspill?

[Wattengård]

Hvorfor bruker du group by?

Mener du kanskje order by?

 

-c-

[ventle]

Nei, jeg skal gruppere resultatene per rapport_id, da er det GROUP BY som gjelder. ORDER BY endrer bare rekkefølge på resultatene.

[Wattengård]

Jeg ser nå hva du er ute etter og hvis jeg ikke har gått glipp av noe grunnleggende så får du ikke til dette med en enkel spørring.

 

Nå skriver du ingenting om hvilket språk du benytter i bakgrunnen, men for å få ut en slik rapport må du nok etterbehandle data etter spørringen.

 

Alternativet er å joine på Steg_Verdi tabellen flere ganger. Da må du derimot vite hvor mage steg det maks kan være, samt at du må bygge spørringen dynamisk etter sekvensen.

For eksempel hvis en sekvens sier at du skal gjøre steg 1,2,5,7 så kan spørringen bli noe slikt som

select r.rapport_id, e.enhet_id, sv1.verdi, sv2.verdi, sv3.verdi, sv4.verdi
FROM Rapport r JOIN Enhet e ON r.Enhet_ID = e.Enhet_ID
JOIN Steg_Verdi sv1 ON r.Rapport_ID = sv1.Rapport_ID AND Steg_ID = 1
join Steg_Verdi sv2 ON r.Rapport_ID = sv2.Rapport_ID AND Steg_ID = 2
join Steg_Verdi sv3 ON r.Rapport_ID = sv3.Rapport_ID AND Steg_ID = 5
join Steg_Verdi sv4 ON r.Rapport_ID = sv4.Rapport_ID AND Steg_ID = 7

 

Group By funker ikke helt på den måten du har tenkt her

 

-C-

[kaffenils]

Det der ute etter er PIVOT. Nå støtter ikke SQL Server dynamiske kolonner i PIVOT, men med litt dynamisk SQL får du til det meste.

 

Sjekk denne linken: http://www.sqlprof.com/blogs/sqldev/archiv...-2005-2008.aspx

[ventle]

Takk for svar, Pivot med dynamisk sql-kode så veldig lovende ut og jeg kom nesten helt i mål med dette. Jeg bruker denne spørringen:

 

SELECT * FROM (SELECT Rapport.Rapport_ID,
Enhet.Enhet_Serienummer,
Sekvens.Sekvens_ID,
Steg.Steg_ID AS StegID,
Steg.Steg_Navn,
Steg_Verdi.Verdi AS StegVerdi
FROM Enhet JOIN Rapport ON Enhet.Enhet_ID = Rapport.Enhet_ID
JOIN Sekvens ON Rapport.Sekvens_ID = Sekvens.Sekvens_ID
JOIN Steg ON Sekvens.Sekvens_ID = Steg.Sekvens_ID
JOIN Steg_Verdi ON Steg.Steg_ID = Steg_Verdi.Steg_ID
WHERE Steg_Verdi.Rapport_ID = Rapport.Rapport_ID
ORDER BY Rapport.Rapport_ID ASC) src
PIVOT (AVG(StegVerdi) FOR StegID
IN ([0],[1],[2]....[siste StegID])) AS pvt

 

Som gir resultat lignende dette:

 

 

rader har nå blitt til kolonner, men hva må jeg gjøre for å få gruppert på Enhet_Serienummer?

[kaffenils]

PIVOT spørringen vil automatisk gruppere på alle kolonner som ikke er pivotert. Slik du har skrevet spørringen så vil den gruppere på følgende kolonner:

 

Sekvens.Sekvens_ID,
Steg.Steg_ID AS StegID,
Steg.Steg_Navn,
Steg_Verdi.Verdi AS StegVerdi

 

Feilen her er at du har med kolonner fra tabell Steg, men radene i denne tabellen er også en del av pivot-kolonnene, dermed får du resultatet du har postet. En annen ting er at spørringen du har postet ikke samsvarer med resultatet du har postet. Det er ikke lett å hjelpe deg når du gjør slikt.

[ventle]

Suksess jeg fjernet Steg_Navn fra SELECT-lista, og knips knips så ble det gruppering så det suser. Takk for hjelpen

[ventle]

For interresserte ble hele spørringen til slutt seende sånn ut:

 

DECLARE @listCol VARCHAR(4000)
DECLARE @query VARCHAR(6000)

SELECT  @listCol = STUFF(( SELECT
                               '],[' + cast(Steg.Steg_ID AS varchar(5))

                       FROM Steg WHERE Steg.Sekvens_ID LIKE 26

                       ORDER BY Steg.Steg_ID

                       FOR XML PATH('')

                                   ), 1, 2, '') + ']'

SET @query = 'SELECT * FROM (SELECT TOP 100000
	Enhet.Enhet_Serienummer,
	Steg.Steg_ID AS StegID,
	Steg_Verdi.Verdi AS StegVerdi
FROM Enhet JOIN Rapport ON Rapport.Enhet_ID = Enhet.Enhet_ID
	JOIN Sekvens ON Rapport.Sekvens_ID = Sekvens.Sekvens_ID
	JOIN Steg ON Sekvens.Sekvens_ID = Steg.Sekvens_ID
	JOIN Steg_Verdi ON Steg.Steg_ID = Steg_Verdi.Steg_ID
WHERE Sekvens.Sekvens_ID = 26 AND Steg_Verdi.Rapport_ID = Rapport.Rapport_ID
ORDER BY Rapport.Rapport_ID ASC) src
PIVOT (AVG(StegVerdi) FOR StegID
IN ('+@listCol+')) AS pvt'

EXECUTE (@query)

 

forutsetninger:

- jeg ønsker kun å hente ut verdier fra testen med Sekvens_ID 26

- Steg_ID'ene i sekvensen overstiger ikke 5 siffer (kan selvfølgelig utvides ved å sette høyere tall i varchar() i cast-funksjonen)

- jeg vil bare få tilbake de første 100000 resultatene (sortert etter Rapport_ID i stigende rekkefølge). Kan selvfølgelig utvides med å sette ett høyere tall etter SELECT TOP, men om jeg fjernet TOP-begrensningen, fikk jeg en feilmelding:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

- jeg vet ikke hvorfor jeg må bruke en aggregatfunksjon (AVG(StegVerdi)) i stedet for å bare legge inn selve verdien som argument til PIVOT, men det gir nå en gang samme resultat (med mindre samme Steg_ID dukker opp flere ganger i samme Rapport_ID), så da bryr jeg meg ikke mer om det...

[quantum]

Suksess jeg fjernet Steg_Navn fra SELECT-lista, og knips knips så ble det gruppering så det suser. Takk for hjelpen

Ved gruppering må alle feltene du velger enten være med i grupperingen, eller brukes i en agregatfunksjon. (MySQL synes det er smart å se bort fra dette kravet og levere tilfeldige verdier i disse feltene, noe som åpner for en del artige sideeffekter. Mange tråder i denne gruppa handler om akkurat dette.)

[kaffenils]

Og MySQL prøver til og med å forsvare praksisen

http://dev.mysql.com/tech-resources/articl...p-by-myths.html