מאמר זה ייתן סקירה מלאה של השימוש באופרטורים PIVOT ו-UNPIVOT ב-SQL Server. האופרטורים PIVOT ו-UNPIVOT דומים לאופרטורים היחסים המאפשרים הפיכת הביטוי בעל ערך הטבלה לטבלה אחרת . שני המפעילים מייצרים דיווח רב מימדי שעוזר לשלב ולהשוות כמות גדולה של נתונים במהירות.
אנחנו יכולים להשתמש ב מפעיל PIVOT כאשר אנו צריכים לשנות ביטויים בעלי ערך טבלה. זה מפצל את ערכים ייחודיים מעמודה אחת לעמודות רבות בתוצאה הסופית. זה גם אגרגטים ערכי העמודות הנותרים הנדרשים בתוצאה הסופית. מפעיל UNPIVOT ממירה נתונים מעמודות של ביטוי בעל ערך טבלה לערכי עמודות, שהם היפוך של PIVOT.
הבה נבין זאת בעזרת התרשים הפשוט המופיע להלן:
בצד שמאל של דמות זו, אנו יכולים לראות את מערך הנתונים המקורי , שיש לו שלוש עמודות: שנה, אזור, ו מכירות . לאחר מכן, נוכל לראות את טבלת PIVOT בצד ימין, הבנויה על ידי סיבוב של אזור (שורות) לצפון ולדרום (עמודות) . לאחר המרת שורות לעמודות, נוכל לעשות an מצטבר של ערכי עמודת מכירות עבור כל מפגש בין העמודות והשורות של טבלת PIVOT.
תחילה ניצור טבלה בשם pivot_demo להדגים את מפעילי PIVOT ו-UNPIVOT. ההצהרה הבאה יוצרת טבלה חדשה במסד הנתונים שצוין:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
לאחר מכן, הוסף כמה נתונים לטבלה זו כמפורט להלן:
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
אנו יכולים לאמת את הנתונים באמצעות הצהרת SELECT. נקבל את הפלט שלהלן:
מפעיל PIVOT
אופרטור זה משמש לסיבוב ביטויים בעלי ערך טבלה. זה הוצג לראשונה בגרסת SQL Server 2005. זה ממיר נתונים משורות לעמודות. הוא מפצל את הערכים הייחודיים מעמודה אחת לעמודות רבות ולאחר מכן אוסף את ערכי העמודות הנותרים הנדרשים בתוצאה הסופית.
עלינו לבצע את השלבים הבאים כדי ליצור טבלת PIVOT:
- בחר את מערך הנתונים הבסיסי לציר.
- צור תוצאות זמניות בעזרת טבלה נגזרת או CTE (ביטוי טבלה נפוץ).
- השתמש באופרטור PIVOT.
תחביר
התחביר הבא ממחיש את השימוש ב-PIVOT ב-SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
אם נשבור את הסקריפט הזה, נוכל לראות שיש לו שני חלקים נפרדים. החלק הראשון בוחר נתונים מהטבלה הראשית, והחלק השני קובע כיצד תיבנה טבלת PIVOT. החלק השני מכיל גם כמה מילות מפתח מיוחדות כמו SUM, FOR ו-IN. הבה נראה את המשמעות של מילות מפתח אלו באופרטור PIVOT.
סְכוּם
המפעיל הזה רגיל לצבור את הערכים מהעמודה שצוינה לשימוש בטבלת PIVOT. עלינו להשתמש בו עם האופרטור PIVOT כדי לקבל את תצוגות העמודות המצטברות עבור סעיפי הערכים.
FOR מילת מפתח
מערך java ממוין
מילת מפתח זו משמשת להצהרת טבלת PIVOT ל להורות למפעיל PIVOT באיזו עמודה יש להחיל את פונקציית PIVOT. בעיקרון, זה מציין את שמות העמודות שיהפכו משורות לעמודות.
מילת מפתח IN
מילת המפתח הזו מפרט את כל הערכים הייחודיים מעמודת PIVOT שתוצג כעמודות של טבלת PIVOT.
דוגמא
הבה נבין זאת בעזרת דוגמאות שונות.
1. ההצהרה הבאה בוחרת תחילה את העמודה שנה, צפון ודרום כנתוני הבסיס לציר. לאחר מכן, צור תוצאה זמנית באמצעות הטבלה הנגזרת ולבסוף החל את האופרטור PIVOT כדי ליצור את הפלט הסופי. פלט זה מוזמן גם בשנה העולה.
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
ביצוע הצהרה זו יפיק את הפלט שלהלן. כאן, אנו יכולים לראות את סכום מחושב של מכירות אזור צפון ודרום בהתאם לערכי השנה .
2. זוהי דוגמה נוספת שבה אנו הולכים לחשב את סכום המכירות עבור כל שנה המתאים לערכי האזור:
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
ביצוע הצהרה זו יהיה לייצר שגיאה מכיוון שאיננו יכולים לציין את הערך המספרי כשם עמודה ישירות.
עם זאת, SQL Server מאפשר לנו להימנע מבעיה זו על ידי שימוש בסוגריים לפני כל ערך שלם. ההצהרה המעודכנת מוצגת בקטע הקוד הבא:
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
הצהרה זו בוצעה בהצלחה והציגה את סכום המכירות המחושב עבור כל שנה התואם לערכי האזור:
3. הדוגמה הקודמת של קבלת טבלת PIVOT מועילה כאשר אנו מודעים לכל ערכי עמודות PIVOT האפשריים. אבל נניח שבשנה הקרובה, מספר העמודות גדל. בהתחשב בדוגמה הקודמת, יש לנו 2010, 2011 ו-2012 שנים כעמודות PIVOT. עם זאת, אין ערובה שהעמודות הללו לא ישתנו בעתיד. מה קורה אם יש לנו נתונים מ-2013 או 2014, או אולי אפילו יותר? במקרים כאלה, נצטרך להשתמש טבלת PIVOT דינמית שאילתות לתיקון בעיה זו.
שאילתת טבלת PIVOT הדינמית מקפלת את כל סקריפט PIVOT בנוהל מאוחסן. הליך זה יספק אפשרויות מתכווננות, שיאפשרו לנו לשנות את הדרישות שלנו על ידי שינוי מספר ערכים עם פרמטרים.
קוד ה-SQL הבא מסביר את פעולתה של טבלת PIVOT הדינמית. בסקריפט זה, תחילה שלפנו את כל הערכים הנבדלים מהעמודה PIVOT ולאחר מכן כתבנו משפט SQL לביצוע עם שאילתת PIVOT בזמן הריצה. בוא נראה את הפלט לאחר ביצוע הסקריפט הזה:
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
בסקריפט זה, יצרנו שני משתנים עם פרמטרים. תיאורו ניתן להלן:
@PivotColumn : משתנה זה ייקח את שם העמודה מהטבלה המקורית עליה נוצרת טבלת PIVOT. לדוגמה , כאן, העמודה 'אזור' מציגה את כל האזורים הזמינים בעמודות.
@PivotList : משתנה זה ייקח את רשימת העמודות שברצוננו להציג כעמודת פלט בטבלת PIVOT.
ביצוע פרוצדורה מאוחסנת דינמית
לאחר היצירה המוצלחת של הפרוצדורה הדינמית המאוחסנת, אנו מוכנים לבצע אותו. ההצהרה הבאה משמשת לקריאה לפרוצדורה המאוחסנת הדינמית כדי להציג את טבלת PIVOT בזמן ריצה:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
כאן ציינו כעת את שם העמודה ' אזור ' בתור הפרמטר הראשון ורשימת העמודות PIVOT כפרמטר השני. הפעלת הסקריפט תציג את הפלט הבא:
כעת, נוכל להוסיף עמודות נוספות בעתיד בזמן ריצה כדי להציג את טבלת PIVOT, מה שלא אפשרי עם שתי הדוגמאות הראשונות.
מפעיל UNPIVOT
זוהי השיטה ההפוכה של אופרטור PIVOT ב-SQL Server. זה מאיש את המפעיל הזה מבצע את פעולה הפוכה של PIVOT על ידי המרת נתונים מעמודות לשורות. אופרטור UNPIVOT מסובב גם את טבלת PIVOT לטבלה הרגילה. זה הוצג לראשונה בגרסת SQL Server 2005.
תחביר
התחביר הבא ממחיש את UNPIVOT ב-SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
דוגמא
הבה נבין כיצד לבטל את פעולת PIVOT עם דוגמאות. קודם כל ניצור א שולחן מקורי ושולחן PIVOT ולאחר מכן החל אופרטור UNPIVOT על טבלה זו.
c מערך מחרוזות
קטע הקוד הבא מכריז תחילה על משתנה טבלה זמני @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
לאחר מכן, נוסיף ערכים לטבלה זו כדלקמן:
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
כעת, אנו יכולים לבצע פעולת UNPIVOT באמצעות המשפט הבא:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
ביצוע קטע הקוד יחזיר את הפלט הבא:
קטע הקוד שלהלן הוא דוגמה נוספת לביצוע תחילה פעולת PIVOT ולאחר מכן פעולת UNPIVOT על אותה טבלה בתוך שאילתה בודדת:
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
ביצוע קטע הקוד יציג את אותו פלט:
הערה: תהליך UNPIVOT הוא פעולה הפוכה של הליך PIVOT, אך הוא אינו היפוך מדויק. מכיוון ששורות מוזגו כאשר PIVOT מחשב את המצטבר ומשלב שורות רבות לשורה אחת בתוצאה, לכן פעולת UNPIVOT לא יכולה להפוך את הטבלה לדומה למקור. עם זאת, אם האופרטור PIVOT אינו ממזג שורות רבות לשורה אחת, אז האופרטור UNPIVOT יכול לקבל את הטבלה המקורית מפלט PIVOT.
סיכום
מאמר זה ייתן סקירה מלאה של אופרטורים PIVOT ו-UNPIVOT ב-SQL Server וימיר ביטוי טבלה לאחר. לעולם אסור לשכוח ש-UNPIVOT היא הפעולה ההפוכה של PIVOT, אבל זה לא ההיפך המדויק של תוצאת PIVOT.