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

20/12/2011

תגיות: ,
2 תגובות

הביטוי ניצחון פירוס מתאר מצב בו מחיר פתרון הבעייה הוא כזה שכבר עדיף היה להמשיך לחיות בשלום ובחירוק שיניים עם הבעייה, מאשר בחירוף נפש עם פתרונה; ופתרון מעין זה אני מציג כאן לבעיית ה-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.

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

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

2 תגובות

  1. שאול25/07/2012 ב 04:15

    הוספתי לקוד שכתבת
    select @sql=replace(@sql,',[],',")
    אחרי השורה שמשרשרת את הערכים כאשר הרצתי אותו בשינויים המחויבים להתאמה לטבלה אחרת.
    כי כאשר יש ערך NULL זה יצר [] ואז התקבלה הודעת שגיאה בחלק של טבלת הציר

    הגב
  2. גרי רשף25/07/2012 ב 08:59

    שאול- קשה לי להיזכר איך הצלחתי בסוף להפעיל את הסמטוחה הזו לפני יותר מחצי שנה..
    ניסיתי עם ובלי התוספת שלך וזה לא עבד (בדקתי ב-2005 וב-2008).
    אצלך זה עובד?..

    הגב