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

01/01/2012

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

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

כנראה שסוף טוב הכל טוב- לאחר שבפוסט קודם מצאתי פתרון רעוע שהיווה יותר אתגר טכני מאשר פתרון אמין- הפעם מדובר בפתרון יציב ואמין (עד כמה שהספקתי לבדוק אותו): במקום להתבסס על פקודת ה-Pivot החדשה יחסית, נשתמש דווקא בשיטה הישנה יותר- בניית SQL דינאמי.
עד 2005 הפתרון היה נראה בערך כך:

Declare @SQL NVarchar(Max);
Select  @SQL=Isnull(@SQL+','+Char(13)+Char(9)+Char(9),'')+'Count(Case When type_desc='''+type_desc+''' Then 1 End) As ['+type_desc+']'
From    sys.objects
Group By type_desc;
Select  @SQL='Select Schema_id,'+Char(13)+
             Char(9)+Char(9)+@SQL+Char(13)+
             'From sys.objects'+Char(13)+
             'Group By Schema_id;';
Print @SQL;
Exec(@SQL);

clip_image002

למען האמת- כשפגשתי לראשונה את הפתרון בשיטת ה-SQL הדינאמי בימי גרסת 2000 העליזים – זה התחיל עם Cursor שעבר על כל ערכי type_desc ובנה כך את ה-SQL, ואינני זוכר אם שיטת השרשור הזו בעזרת Select לתוך משתנה פעלה אז, אבל בשורה התחתונה- קיבלנו SQL דינאמי.

את כל זה נעטוף כעת ב-SP_ExecuteSQL תוך שאנחנו מכפילים את כל ה'תגים' שבתוך הפקודה (ומשמיטים את פקודת ה-Print):

Exec SP_ExecuteSQL N'
Declare @SQL Varchar(Max);
Select  @SQL=Isnull(@SQL+'',''+Char(13)+Char(9)+Char(9),'''')+''Count(Case When type_desc=''''''+type_desc+'''''' Then 1 End) As [''+type_desc+'']''
From    sys.objects
Group By type_desc;
Select  @SQL=''Select Schema_id,''+Char(13)+
             Char(9)+Char(9)+@SQL+Char(13)+
             ''From sys.objects''+Char(13)+
             ''Group By Schema_id;'';
Exec(@SQL);
';

clip_image004

ולסיום נעטוף את כל זה ב-OpenRowSet תוך שמכפילים שוב את כל 'התגים':

Select * From OpenRowset('MSDASQL','DRIVER={SQL Server}; SERVER=localhost;trusted_connection=yes','Set FmtOnly Off 
Exec SP_ExecuteSQL N''
Declare @SQL Varchar(Max);
Select  @SQL=Isnull(@SQL+'''',''''+Char(13)+Char(9)+Char(9),'''''''')+''''Count(Case When type_desc=''''''''''''+type_desc+'''''''''''' Then 1 End) As [''''+type_desc+'''']''''
From    sys.objects
Group By type_desc;
Select  @SQL=''''Select Schema_id,''''+Char(13)+
             Char(9)+Char(9)+@SQL+Char(13)+
             ''''From sys.objects''''+Char(13)+
             ''''Group By Schema_id;'''';
Exec(@SQL);
'';
');

clip_image006

קצת מורכב, אבל גם דינאמי וגם מאפשר לבצע פילטור, סינון, מיון, Join וכו'.

הקוד הנ"ל נבדק בגרסאות 2005 ו-2008 ועבד ללא דופי. ב-2012 הוא נפל והופיעה הודעת שגיאה כדלקמן:

Msg 11513, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

The metadata could not be determined because statement 'Exec SP_ExecuteSQL @SQL' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

With Result Sets הוא אופרטור חדש בגרסת 2012 שנועד להגדיר במפורש ולעצב את סט התוצאות שפרוצדורה מחזירה.

השתמשתי בו כך (תוך שאני מגביל מראש את ערכי העמודות):

Select * From OPENROWSET ('SQLNCLI','Server=(Local);Trusted_Connection=Yes','Set FmtOnly Off
Exec SP_ExecuteSQL N''
Declare @SQL NVarchar(Max);
Select @SQL=Isnull(@SQL+'''',''''+Char(13)+Char(9)+Char(9),'''''''')+''''Count(Case When type_desc=''''''''''''+type_desc+'''''''''''' Then 1 End) As [''''+type_desc+'''']''''
From   sys.objects
Where  type_desc In (''''System_Table'''',''''View'''')
Group By type_desc;
Select @SQL=''''Select Schema_id,''''+Char(13)+
            Char(9)+Char(9)+@SQL+Char(13)+
            ''''From sys.objects''''+Char(13)+
            ''''Group By Schema_id;'''';
Exec   SP_ExecuteSQL @SQL
With Result Sets(([Schema_id] Int Not Null,
                  [System_Table] Int Not Null,
                  [View] Int Not Null));
'';
');

clip_image008

כמובן שכאן הדרא קושיא לדוכתא וכעת עלינו להגדיר מראש ובמפורש את ה-Result Sets.

אני חושד שמיקרוסופט החליטו לסגור את הפרצות של OpenRowset שאיפשרו לנו להכשיר שרצים ולי לכתוב פוסטים..

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

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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