go backarticles

Articles of SQLschool.gr Team

T-SQL PIVOT

Fivi Panopoulou - Sotiris Karras

Με την βοήθεια του τελεστή pivot μπορούμε να ομαδοποιήσουμε δεδομένα και να κάνουμε aggregations, παρουσιάζοντας το αποτέλεσμα που προκύπτει για κάθε ομάδα σε διαφορετική στήλη.Ο τελεστής αυτός χρησιμοποιείται, ως επί το πλείστον, για να παράγουμε reports στην επιθυμητή μορφή. Ας δούμε την χρήση του με ένα παράδειγμα, χρησιμοποιώντας την AdventureWorks.

Έστω λοιπόν ότι θέλουμε να δούμε την αξία των παραγγελιών ανά χρονιά και ανά ημέρα της εβδομάδας και να πάρουμε μια εικόνα για το πώς πήγαιναν οι πωλήσεις ανάλογα με την μέρα.Η πληροφορία που χρειαζόμαστε βρίσκεται στον πίνακα SalesOrderHeader του Sales schema και με την χρήση του group by θα μπορούσαμε να πάρουμε το επιθυμητό αποτέλεσμα. Αυτό θα έχει μια μορφή σαν την παρακάτω:

Με τον τρόπο αυτό όμως, ενώ έχουμε τα δεδομένα που χρειαζόμαστε είναι δύσκολο να δούμε την πληροφορία που πραγματικά θέλαμε με μια ματιά. Θα ήταν σίγουρα πιο ευανάγνωστο το αποτέλεσμά μας αν σε κάθε γραμμή είχαμε τη χρονιά, ενώ τα σύνολα για κάθε ημέρα ήταν σε στήλες. Αυτό μπορούμε να κάνουμε με τη χρήση του pivot. Το query που θα μας δώσει το αποτέλεσμα σε αυτή τη μορφή είναι το παρακάτω:

SELECT OrderYear, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday 
FROM
(
    SELECT  YEAR(OrderDate) as OrderYear, 
            DATENAME(WEEKDAY,OrderDate) as OrderDay, 
            SubTotal
    FROM Sales.SalesOrderHeader
) AS d
PIVOT
(
    AVG(SubTotal)
    FOR OrderDay IN 
        (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday) 
) AS p
ORDER BY p.OrderYear;
GO

Το αποτέλεσμα είναι το παρακάτω:

Παρατηρώντας το παραπάνω query πρέπει να σταθούμε στα εξής βασικά κομμάτια του:

  1. Στο FROM clause έχουμε πρώτα ένα select statement που μας δίνει τα δεδομένα τα οποία θα χρησιμοποιήσουμε για να κάνουμε pivot και ένα alias για το αποτέλεσμα αυτού
  2. Το επόμενο τμήμα του FROM clause είναι αυτό του PIVOT.  Αυτό με την σειρά του περιλαμβάνει τα παρακάτω:
    • Μια aggregate function πάνω σε κάποια από τις στήλες του πρώτου πίνακα. Αυτή καθορίζει τον υπολογισμό που θα γίνει σε κάθε pivoted στήλη (στο παράδειγμα: AVG(SubTotal)).
    • Έπειτα, δηλώνουμε την στήλη του πρώτου πίνακα η οποία περιλαμβάνει τις τιμές που θέλουμε να γίνουν στήλες και ποιες είναι αυτές οι τιμές. Στο παράδειγμά μας είναι οι μέρες τις εβδομάδας.

Η στήλη OrderYear δεν χρησιμοποιήθηκε ούτε για να καθορίσει στήλες αλλά ούτε και για aggregation και έτσι αυτή καθορίζει τις γραμμές του αποτελέσματος. Είναι δυνατό να έχουμε και άλλες unpivoted στήλες. Στην περίπτωση αυτή, ο συνδιασμός τους θα καθόριζε τις γραμμές. Για παράδειγμα, αν θέλαμε το προηγούμενο αποτέλεσμα να το δούμε ανα χρόνο και μήνα, και όχι μόνο ανά χρόνο, θα χρησιμοποιούσαμε το παρακάτω query:

SELECT OrderYear, OrderMonth, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday 
FROM
(
    SELECT  YEAR(OrderDate) as OrderYear,
            MONTH(OrderDate) as OrderMonth, 
            DATENAME(WEEKDAY,OrderDate) as OrderDay, 
            SubTotal
    FROM Sales.SalesOrderHeader
) AS d
PIVOT
(
    AVG(SubTotal)
    FOR OrderDay IN 
        (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday) 
) AS p
ORDER BY p.OrderYear,p.OrderMonth;
GO

Το αποτέλεσμα θα είχε την παρακάτω μορφή:


Fivi Panopoulou

Fivi Panopoulou

System Engineer • Speaker

Το 2007 ξεκίνησα τις σπουδές μου στη σχολή Ηλεκτρολόγων Μηχανικών και Μηχανικών Υπολογιστών στο Εθνικό Μετσόβιο Πολυτεχνείο.Κατά την διάρκεια των σπουδών μου εκεί αγάπησα τον προγραμματισμό και τα συστήματα πληροφορικής, καθώς επίσης απέκτησα το ιδιαίτερο ενδιαφέρον μου για τις βάσεις δεδομένων. Κατά την διάρκεια της διπλωματικής μου ασχολήθηκα με ζητήματα ανωνυμοποίησης δεδομένων και την ανάπτυξη σχετικού εργαλείου. Τα τελευταία χρόνια των σπουδών μου, μου δόθηκε η ευκαιρία να ασχοληθώ περισσότερο και να διευρύνω τους ορίζοντές μου ως Microsoft Student Partner και μέσω της κοινότητας Student Guru. Στα πλαίσια των κοινοτήτων αυτών, ξεκίνησα να ασχολούμαι με παρουσιάσεις αλλά και να γνωρίζω τον SQL Server. Από την πρώτη στιγμή που ασχολήθηκα μαζί του, συνειδητοποίησα πόσο ήθελα να εμβαθύνω τις γνώσεις μου σχετικά με αυτόν και τα συστήματα διαχείρισης βάσεων δεδομένων γενικότερα, πράγμα που προσπαθώ να κάνω έκτοτε. Πριν χρόνια είχα την τύχη να συμμετέχω στο πρόγραμμα mentoring, μέσω του οποίου γνώρισα τον κ. Χατζηπαυλή. Από τότε συμμετέχω στην ομάδα του SQLschool.gr.


Sotiris Karras

Sotiris Karras

System Engineer • Speaker

Είμαι απόφοιτος της σχολής Ηλεκτρολόγων Μηχανικών και Μηχανικών Υπολογιστών του Εθνικού Μετσόβιου Πολυτεχνείου και στα ενδιαφέροντά μου συμπεριλαμβάνεται o τομέας του Knowledge and Data Engineering. Πιο συγκεκριμένα, έχω ασχοληθεί ακαδημαϊκά και ερευνητικά με τον τομέα του data privacy και data anonymity, ενώ πάθος μου είναι ό,τι έχει να κάνει με relational databases και data management. Στο παρελθόν, έχω συνεργαστεί με την Microsoft Hellas ως Microsoft Student Partner για ακαδημαϊκές δραστηριότητες και ήμουν μέρος του MVP mentoring προγράμματός της, στο οποίο είχα την τύχη να γνωρίσω τον κ. Χατζηπαυλή.


Relative Articles

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.