SQLShack (Norsk)

I de forrige få artiklene i denne serien, Vi har lagt grunnlaget for hvordan du oppretter en rapport. Vi tar et skritt videre og ser hvordan du bruker SQL Server PIVOT-tabelloperatøren. Vi starter fra det enkle spørsmålet og sakte fremover mot dynamisk SQL og PIVOT. La oss begynne.

Datamodellen og den generelle ideen

Datamodellen vi skal bruke er den samme som vi har brukt i hele denne serien.

Vår oppgave i dag er å lage en rapport (ved hjelp av SQL Server PIVOT-operatør) der hver by vil være i en egen rad , og hvor vi teller antall samtaleutfall relatert til hver by. Derfor skal alle utfall (alle verdier fra ordboken) være kolonner i rapporten.

Siden «et bilde er verdt tusen ord», vil vi også beskrive vårt endelige mål med bildet.

For å oppnå dette må vi bruke data fra 4 tabeller call, call_outcome, kunde og by. Derfor er det ville være bra at du ser nærmere på modellen nedenfor og analyserer hvordan disse tabellene er relatert.

Vi begynner med å undersøke dataene i tabellene, og med hvert spørsmål beveger vi oss et skritt nærmere ønsket resultat (spørring). Tilnærmingen som brukes her er den samme som den som ble brukt i Lær SQL: Lag en rapport manuelt ved hjelp av SQL-spørringsartikkel.

Rapportkategorier og data

det første settet med spørsmål vi skal begynne med, er det som undersøker data som er til stede i alle de fire tabellene vi må bruke for å lage rapporten. Vi gjør det slik at vi kan bekrefte at den endelige rapporten returnerte det den skulle. Etter det oppretter vi spørsmål wh som returnerer rapporteringskategorier og rapportdata, samt SQL Server PIVOT-tabellspørsmål.

Disse spørsmålene er så enkle som de kunne være, så det er ikke noe spesielt å kommentere angående syntaksen. Når det gjelder dataene som returneres, bør vi være oppmerksom på følgende:

• Bytabellen inneholder 6 byer, og derfor bør vi ha 6 rader i sluttrapporten
• Det er 3 mulige utfall i call_outcome-tabellen, så vi bør ha 3 kolonner for utfall (totalt 4 kolonner – > en er for bynavn)
• Tabellanropet har 10 rader (bare 8 på bildet over), så i finaletabellen skal summen av alle utfall være 10 (hver samtale kan ha nøyaktig 1 utfall)

Neste ting å gjøre er å utarbeide rapporteringskategorier. Vi ønsker å ha en kombinasjon av alle byer og alle mulige utfall. For å oppnå det bruker vi CROSS JOIN (kartesisk produkt).

Å ha alle rapporteringskategoriene skal garantere at vi får en rad i rapporten, uansett om det paret har data eller ikke. Og det er det vi ønsker – å se 0 i den rapporteringskategorien, og ikke gå glipp av den kategorien helt.

Den neste tingen å gjøre er å bli med i alle fire tabellene som inneholder dataene vi trenger.

Selve spørringen er ikke kompleks. I resultatet bør vi legge merke til at vi har 10 rader, samme nummer som antall samtaler vi har i databasen. Jeg har også brukt DATEDIFF-funksjonen til å returnere varigheten av hver samtale på få sekunder. Jeg kunne rett og slett sette call.id her, men jeg ønsket å minne oss selv om DATEDIFF-funksjonen vi har nevnt i denne artikkelen. Vi kan bruke denne varigheten hvis vi trenger SUM / AVG sekunder brukt per rapporteringskategori (by & samtaleutfall).

Rapport uten SQL Server PIVOT-TABELL

Nå er vi klare til å bli med i kategorier og data. Vi bruker begge tidligere nevnte spørsmål som underforespørsler og blir med dem ved hjelp av VENSTRE JOIN (for å ha alle kategoriene til stede i den endelige utgangen).

Vi har alle 18 kombinasjoner (6 forskjellige byer * 3 forskjellige samtaleutfall), og vi har også alle de 10 samtalene som er til stede her (rader med samtalelengden < > NULL).

La oss nå opprette SQL Server PIVOT-spørringen.

SQL Server PIVOT TABLE (statisk)

Så langt har vi klart å hente dataene vi trenger, og vi har dem som liste. Vi kunne eksportere data til Excel og gjøre transformasjoner der. Vi vil faktisk gjøre dette i den kommende artikkelen.I dag vil vi likevel løse dette ved hjelp av SQL Server PIVOT-operatør. I denne delen vil vi dekke den «statiske» SQL Server PIVOT. La oss ta en titt på spørringen som brukes og resultatet først.

Vi kan legge merke til at resultatet er akkurat det vi ønsket. Vi har hver by i en rad og alle tre kategoriene for samtaleutfall i separate kolonner. Hvis det ikke er data for et bestemt by-utfallspar, er den cellen skal inneholde verdien 0.

La oss kommentere SQL Server PIVOT-spørringen nå. Dette er noen få ting jeg synes er viktig å nevne her:

• I første del av spørringen – report_data, vi har kopiert inn spørringen fra forrige avsnitt. Den eneste endringen var at vi ikke hadde ORDER BY og GROUP BY i denne delen av spørringen fordi de ikke kan brukes her (de bør gå etter det endelige spørreresultatet genereres)
• PIVOT-delen av spørringen består av to deler. I den første definerer vi hvilken samlet funksjon vi vil bruke. I vårt tilfelle er dette – COUNT ( call_duration). I FOR en del av spørsmålet ery, vi definerer kolonner. Vi lister opp alle verdiene vi vil ha som kolonner. Dette er hardkodende. Hvis vi legger til ny verdi i tabellen, påvirker det ikke spørringen (og det burde det)

Dette spørsmålet gjør jobben sin, og den vil fungere perfekt hvis vi aldri endrer (legg til ny , slette, oppdatere navnene) samtaleutfall i den relaterte tabellen. Det kan vise seg å være problemet i tilfeller vi trenger å gjøre endringer i ordboken. Vi ønsker ikke å tenke på om det er et spørsmål som ikke fungerer som forventet. For å løse dette må vi bruke et nytt konsept – dynamisk SQL.

Dynamisk SQL Server PIVOT-TABELL

Før vi går til koden, la oss kort forklare hva dynamisk SQL egentlig er er (vi vil gi en mye mer detaljert gjennomgang av den i kommende artikler). Den enkleste forklaringen er at i SQL kan du «bygge» spørsmålene dine som strenger, og deretter sende den strengen som en parameter for den lagrede prosedyren for SQL Server. Denne prosedyren skal utføre SQL-setningen (e) som er lagret i den strengen (selvfølgelig , hvis syntaksen er OK).

Selv om dette kan høres ut som noe som ikke er så vanlig, har det ganske mange steder det gjør livet ditt mye enklere. Opprette et SQL Server PIVOT-tabellspørsmål med en ukjent antall kolonner er nøyaktig et slikt tilfelle.

Ideen med denne tilnærmingen er som følger:

• Vi vil erklære en variabel der alle kolonnenavn (@column) skal lagres. ), og variabelen hvor den komplette spørringen (@query) skal lagres
• Ved å bruke SELECT-spørringen, finner vi alle verdiene som er lagret i kolonnen result_text. Følgende kode – @column + = QUOTENAME (TRIM (co.outcome_text)) + ,, skal legge til kolonnenavn i listen over alle forrige kolonnenavn som returneres av spørringen. Resultatet er at vi har alle kolonnenavn lagret i e variabel @column
• I variabelen @query lagrer vi hele spørringen fra forrige seksjon, bortsett fra den delen der kolonnene er definert. Vi får denne delen fra variabelen @column
• Det siste vi trenger å gjøre er å sende hele spørringen som parameter til SQL Server-systemprosedyren sp_executesql

Det endelige resultatet er det samme som i forrige avsnitt, men denne gangen er vi sikre på at spørringen vår vil fungere selv om vi gjør endringer i resultatet. utfall_tekstverdier. Du kan også enkelt endre dette spørsmålet og beregne andre verdier, for eksempel SUM / AVG-varighet per by-utfallspar.

Konklusjon

SQL Server PIVOT-operatøren gir deg en helt nytt syn på hva du kan oppnå direkte på databaselaget. Når det kombineres med dynamisk SQL, går dette enda lenger. Jeg oppfordrer deg sterkt til å leke med det – det er ingen bedre måte å lære enn å prøve det selv. I neste artikkel skal vi vise hvordan vi kan bruke SQL-spørreutdata og lage tabeller og grafer i Excel (dette skal ikke bare fungere i SQL Server, men generelt).

Innholdsfortegnelse

Lær SQL: OPPRET DATABASE & OPPRETT TABELLoperasjoner

Lær SQL: INSERT IN TABLE

Lær SQL: Primær nøkkel

Lær SQL: Fremmed nøkkel

Lær SQL: SELECT-setning

Lær SQL: INNER JOIN vs LEFT JOIN

Lær SQL: SQL-skript

Lær SQL: Typer relasjoner

Lær SQL: Bli med flere tabeller

Lær SQL: Aggregerte funksjoner

Lær SQL: Hvordan skrive en kompleks SELECT-spørring?

Lær SQL: INFORMASJON_SCHEMA-databasen

Lær SQL: SQL-datatyper

Lær SQL: Sett teori

Lær SQL: Brukerdefinerte funksjoner

Lær SQL: Brukerdefinerte lagrede prosedyrer

Lær SQL: SQL-visninger

Lær SQL: SQL-utløsere

Lær SQL: Øv SQL-spørsmål

Lær SQL: SQL-spørringseksempler

Lær SQL: Opprett en rapport manuelt ved hjelp av SQL-spørsmål

Lær SQL: SQL Server dato- og tidsfunksjoner

Lær SQL: Opprett SQL Server-rapporter ved hjelp av dato- og tidsfunksjoner

Lær SQL: SQL Server-pivottabeller

Lær SQL: SQL Server-eksport til Excel

Lær SQL: Introduksjon til SQL Server-sløyfer

Lær SQL: SQL Server-markører

Lær SQL: Best fremgangsmåter for SQL for sletting og oppdatering av data

Lær SQL: Navngivningskonvensjoner

Lær SQL: SQL-relaterte jobber

Lær SQL: Ikke-Equi-sammenkoblinger i SQL Server

Lær SQL: SQL Injection

• Forfatter
• Nylige innlegg

Emil er databaseprofesjonell med 10+ års erfaring i alt relatert til databaser. Gjennom årene jobbet han i IT- og finansbransjen og jobber nå som frilanser.
Hans tidligere og nåværende engasjement varierer fra databasedesign og koding til undervisning, rådgivning og skriving om databaser. Ikke å glemme, BI, å lage algoritmer, sjakk, filateli, 2 hunder, 2 katter, 1 kone, 1 baby …
Du finner ham på LinkedIn
Vis alle innlegg av Emil Drkusic

Siste innlegg av Emil Drkusic (se alle)

• Lær SQL: SQL Injection – 2. november 2020
• Lær SQL: Non-Equi-sammenkoblinger i SQL Server – 29. september 2020
• Lær SQL: SQL-relaterte jobber – 1. september 2020