הבלוג של גרי רשף

20/12/2011

פתרון לבעיית ה-Pivot הדינאמי

Filed under: Uncategorized — גרי רשף @ 21:05

הביטוי ניצחון פירוס מתאר מצב בו מחיר פתרון הבעייה הוא כזה שכבר עדיף היה להמשיך לחיות בשלום ובחירוק שיניים עם הבעייה, מאשר בחירוף נפש עם פתרונה; ופתרון מעין זה אני מציג כאן לבעיית ה-Pivot הדינאמי.
הקדמה קצרה- את הקריירה שלי במסדי נתונים התחלתי ב-Access, עוד בימי Windows 3.11 העליזים כשכל יישום נקנה בנפרד ולא כמו היום בחבילת Ofiice, והתפרס על פני יותר מעשרה דיסקטים "3.5..
בכל מה שקשור ל-SQL עבדתי כמקובל עם הממשק הגרפי הנוח שלו (הממשק הטקסטואלי בלתי שימושי לחלוטין), ונדהמתי לגלות שיש מפתחים שמסוגלים לכתוב משפטי SQL בעצמם.. חלפו שנים, התקדמתי ל-SQL Server, כיום גם אני כותב משפטי SQL עם יד אחת קשורה מאחורי הגב, אבל ליכולת אחת חשובה של אקסס טרם מצאתי תחליף- יכולות ה-Pivot שלה.
עד גרסת 2005 לא הייתה אופציה כזו ב-SQL Server למרות שבאקסס זה התבצע ללא בעייה עוד מגרסאותיו הראשונות למעלה מעשר שנים לפני כן, ואב"ג לימד אותנו איך לעשות זאת בעזרת SQL דינאמי.
אופציית ה-Pivot שהתווספה בגרסת 2005 הייתה נחותה לעומת זאת של אקסס וחייבה לציין במפורש את שמות העמודות, למשל- שליפה המציגה שורה לכל סכימה (dbo, sys וכו'), עמודה לכל סוג אובייקט (טבלה, View, טריגר, אינדקס..), ובכל תא – כמה כאלו יש (למשל- כמה טבלאות בסכימת dbo וכו'):

--If Object_ID('Tmp') Is Not Null Drop Table Tmp;
Select   *
--Into   Tmp
From     (Select  object_id,
                  type_desc,
                  Schema_Name(schema_id) [Schema]
         From     sys.objects) As p
Pivot    (Count (object_id)
For      type_desc In ([FOREIGN_KEY_CONSTRAINT],[INTERNAL_TABLE],[PRIMARY_KEY_CONSTRAINT],[SERVICE_QUEUE],[SQL_SCALAR_FUNCTION],[SQL_STORED_PROCEDURE],[SYSTEM_TABLE],[UNIQUE_CONSTRAINT],[USER_TABLE],[VIEW])) As pvt
Order By [Schema];
--Select * From Tmp;
--If Object_ID('Tmp') Is Not Null Drop Table Tmp;

clip_image002

בדוגמה הזו יש כמה קטעים מוערים (Commented)- בהמשך נשפוך את הפלט לטבלה ונשלוף ממנה, אבל את הרעיון הכללי אפשר להבין גם בלי זה.

ניתן לראות שלאחר האופרטור Pivot יש למנות את סוגי ה-type_desc במפורש, וכשהם אינם ידועים מראש- זו בעייה. בגרסה האקססית של ה-Pivot אין צורך בכך, ואני משער שהפער בין היכולות קשור לכך שאקסס הוא כלי לא תקני לחלוטין מבחינת חומרות ה-Ansi, ואילו SQL Server הולך בדרך הישר מבחינת ההתמודדות עם שליפה שהמבנה שלה אינו ידוע, ולכן אנחנו נדרשים להצהיר על כך במפורש.

מה ניתן לעשות אם איננו יודעים מראש אילו סוגי אובייקטים יש בסביבה בה השליפה הנ"ל תרוץ?

הפתרון הוא ליצור SQL דינאמי: נגדיר משתנה, נשרשר לתוכו את הערכים מעמודת type_desc, נוסיף לו את שאר חלקי השליפה שלפני ואחרי רשימת העמודות, ונפעיל בעזרת Exec:

Declare @SQL Varchar(Max);
Select  @SQL=IsNull(@SQL+',','')+'['+type_desc+']'
From    (Select Distinct type_desc From sys.objects) T
Order By type_desc;
Print @SQL;
If Object_ID('Tmp') Is Not Null Drop Table Tmp;
Set     @SQL='Select *'+Char(13)+
'Into Tmp'+Char(13)+
'From (Select object_id,'+Char(13)+
' type_desc,'+Char(13)+
' Schema_Name(schema_id) [Schema]'+Char(13)+
' From sys.objects) As p'+Char(13)+
'Pivot (Count (object_id) For type_desc In ('+@SQL+')) As pvt'+Char(13)+
'Order By [Schema];'
Print @SQL;
Exec(@SQL);
Select * From Tmp;
If Object_ID('Tmp') Is Not Null Drop Table Tmp;

(אינני מצרף צילום מסך אך הפלט אותו פלט כמו קודם)

מה הועילו חכמים בתקנתם? עד גרסת 2005 השתמשנו ב-SQL דינאמי מסוג אחד וכעת ב-SQL דינאמי אחר; נכון שהסינטקס כעת אולי מעט יותר נוח, אבל הבעייה אותה בעייה: אם העמודות ידועות מראש ניתן לכתוב SQL רגיל ואם לא- SQL דינאמי..

אין ברירה אלא לחזור ל-OpenRowset – כלי מוכר בבלוג הזה שבעזרתו אפשר לעשות כל מה שאסור, ובין היתר להריץ פרוצדורות ולקבל שליפה שמתנהגת כ-Select רגיל.

למי שזו לו פעם ראשונה- יש לאפשר באופן חד פעמי שימוש בפקודה הנ"ל על ידי:

SP_Configure 'Ad Hoc Distributed Queries',1;
Go

Reconfigure With Override;
Go

כעת כך יש "לעטוף" את הקוד הנ"ל בפקודת Exec שתהפוך את כולו לפרוצדורה אחת, ולצורך כך (חוץ מאשר לפתוח ב-Exec ופותח שמאלי וגרש, ולסיים בגרש וסוגר ימני) – נצטרך להכפיל את כל הגרשים שבקוד הקיים, ונקבל את הדבר המשונה הבא שלמרבה הפלא עובד (שוב- אותו פלט כמו קודם):

Exec('
Declare @SQL Varchar(Max);
Select  @SQL=IsNull(@SQL+'','','''')+''[''+type_desc+'']''
From    (Select Distinct type_desc From sys.objects) T
Order By type_desc;
Print   @SQL;

If Object_ID(''Tmp'') Is Not Null Drop Table Tmp;
Set     @SQL=''Select *''+Char(13)+
''Into Tmp''+Char(13)+
''From (Select object_id,''+Char(13)+
'' type_desc,''+Char(13)+
'' Schema_Name(schema_id) [Schema]''+Char(13)+
'' From sys.objects) As p''+Char(13)+
''Pivot (Count (object_id) For type_desc IN (''+@SQL+'')) As pvt''+Char(13)+
''Order By [Schema];''
Print @SQL;
Exec(@SQL);
Select * From Tmp;
If Object_ID(''Tmp'') Is Not Null Drop Table Tmp;
');

ולבסוף ניצור פקודת OpenRowset, ומכיוון שגם היא "עוטפת" את הקוד הנ"ל בגרשים – נצטרך שוב להכפיל את כל הגרשים שבנ"ל, ונקבל בסה"כ את הדבר הבא:

Select * From OpenRowset('MSDASQL','DRIVER={SQL Server}; SERVER=localhost;trusted_connection=yes','
Exec(''
Declare  @SQL Varchar(Max);
Select   @SQL=IsNull(@SQL+'''','''','''''''')+''''[''''+type_desc+'''']''''
From     (Select Distinct type_desc From sys.objects) T
Order By type_desc;
Print    @SQL;
If Object_ID(''''Tmp'''') Is Not Null Drop Table Tmp;
Set      @SQL=''''Select *''''+Char(13)+
''''Into Tmp''''+Char(13)+
''''From (Select object_id,''''+Char(13)+
'''' type_desc,''''+Char(13)+
'''' Schema_Name(schema_id) [Schema]''''+Char(13)+
'''' From sys.objects) As p''''+Char(13)+
''''Pivot (Count (object_id) For type_desc IN (''''+@SQL+'''')) As pvt''''+Char(13)+
''''Order By [Schema];''''
Print    @SQL;
Exec(@SQL);
Select * From Tmp;
If Object_ID(''''Tmp'''') Is Not Null Drop Table Tmp;
'');
');

clip_image004

פקודת Select? כן.

עובד? כן.

יש לציין את ערכי העמודות במפורש? לא.

אלגנטי? ממש לא..

יציב? גם לא: אצלי השליפה נכשלת מסיבות לא ברורות, למשל אם אני מבטל את שורת הרווח חסרת המשמעות שלאחר ה-Print הראשון, או את הסמיקולון שבסוף..

כנראה שזו דוגמה לניצחון פירוס (אם לקשר לרישא של הפוסט).

הערת השלמה לגבי היציבות- הצלחתי להפעיל את זה מול SQL 2008 ו- SQL 2005 אך לא מול SQL 2012.

התלבטתי אם לפרסם את הפוסט לאור זאת, והחלטתי שכן תוך שאני מציין בבירור שזו אינה מציאה גדולה..

מודעות פרסומת

4 תגובות »

  1. […] שסוף טוב הכל טוב- לאחר שבפוסט קודם מצאתי פתרון רעוע שהיווה יותר אתגר טכני מאשר פתרון […]

    פינגבאק של פתרון לבעיית ה-Pivot הדינאמי (2) - גרי רשף — 01/01/2012 @ 21:35

  2. […] שסוף טוב הכל טוב- לאחר שבפוסט קודם מצאתי פתרון רעוע שהיווה יותר אתגר טכני מאשר פתרון […]

    פינגבאק של פתרון לבעיית ה-Pivot הדינאמי (2) « הבלוג של גרי רשף — 01/01/2012 @ 21:30

  3. היי גרי.
    האם ישנן דרכים אחרות שאינן PIVOT לבעיות מעין אלו?

    תגובה של איתי — 22/12/2011 @ 12:40

    • אפשר לעשות זאת על ידי (Select Count(Case .. When … Then 1 End כשלכל עמודה יש Count מותנה משלה.
      מכאן אתה מבין שצריך גם כאן לדעת מראש אילו עמודות יהיו, ואם לא יודעים- יש ליצור גם כאן SQL דינאמי.

      על ידי כל מיני פירוטכניקות- ניתן למצוא עוד דרכים, אך גם הן יהיו כרוכות ב-SQL דינאמי.

      תגובה של גרי רשף — 22/12/2011 @ 18:24


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: