جداول محوری پویا در SQL Server ( Pivot Table)

در این مقاله ، من قصد دارم توضیح دهم که چگونه می توان یک جدول محوری پویا در SQL Server ایجاد کرد. جداول محوری بخشی از اطلاعات خلاصه ای است که از یک مجموعه داده بزرگ اساسی تولید می شود. به طور کلی برای گزارش در ابعاد خاص از مجموعه داده های گسترده استفاده می شود. اساساً ، کاربر می تواند ردیف ها را به ستون تبدیل کند. این به کاربران امکان می دهد ستون ها را به راحتی از جدول SQL Server جابجا کرده و مطابق با نیازها گزارش ایجاد کنند.

برخی از جداول محوری نیز برای کمک به تجزیه و تحلیل داده ها ، عمدتا برای برش و قطعه بندی با داده ها و ایجاد تحلیلی ایجاد می شوند. اگر شکل زیر را مشاهده کنید ، ایده ایجاد یک جدول محوری از یک جدول خواهید داشت.

اگر شکل بالا را مشاهده کنید ، می بینید که دو جدول وجود دارد. جدول سمت چپ جدول واقعی است که شامل سوابق اصلی است. جدول سمت راست یک جدول محوری است که با تبدیل ردیف ها از جدول اصلی به ستون تولید می شود. اساساً ، یک جدول محوری شامل سه منطقه خاص است ، عمدتا - سطرها ، ستون ها و مقادیر. در تصویر بالا ، ردیف ها از ستون Student ، ستون ها از Subject گرفته شده و مقادیر با تجمیع ستون Marks ایجاد می شوند.

ایجاد یک نمونه داده

اکنون که در مورد چگونگی عملکرد یک جدول محوری ایده گرفته ایم ، می توانیم جلو برویم و برنامه های عملی خود را امتحان کنیم. برای ایجاد نمونه داده می توانید اسکریپت زیر را اجرا کنید و ما سعی خواهیم کرد تصویر بالا را در اینجا پیاده سازی کنیم.

CREATE TABLE Grades(

  [Student] VARCHAR(50),

  [Subject] VARCHAR(50),

  [Marks]   INT)

GO

INSERT INTO Grades VALUES

('Jacob','Mathematics',100),

('Jacob','Science',95),

('Jacob','Geography',90),

('Amilee','Mathematics',90),

('Amilee','Science',90),

('Amilee','Geography',100)

GO

بیایید سعی کنیم داده ها را از جدول جداگانه ای که به صورت زیر ایجاد کرده ایم ، انتخاب کنیم.

استفاده از اپراتور PIVOT :

اکنون که داده های خود را آماده کردیم ، می توانیم پیش برویم و جدول محوری را در SQL Server ایجاد کنیم. با در نظر گرفتن همان تصویر بالا ، ستون Student را به عنوان سطرها نگه می داریم و Subject را برای ستون ها می گیریم. همچنین ، نکته مهم دیگری که در اینجا باید به آن توجه شود این است که هنگام نوشتن پرس و جو برای جدول pivot در SQL Server ، ما نیاز به ارائه لیست متمایزی از مقادیر ستون داریم که می خواهیم در جدول pivot تجسم کنیم. برای این اسکریپت ، می توانیم ببینیم که سه موضوع مجزا در مجموعه داده اصلی موجود است ، بنابراین هنگام ایجاد جدول محوری باید این سه موضوع را در لیست ارائه دهیم.

SELECT * FROM

(

  SELECT

    [Student],

    [Subject],

    [Marks]

  FROM Grades

)

StudentResults

PIVOT

(

  SUM([Marks])

  FOR [Subject]

  IN (

    [Mathematics],

    [Science],

    [Geography]

  )

)

AS PivotTable

همانطور که در شکل بالا مشاهده می کنید ، جدول محوری ایجاد شده است و ما ردیف های Subjects را به ستون های مجزا تبدیل کرده ایم.

حال اجازه دهید سعی کنیم اسکریپت فوق را بشکنیم و نحوه عملکرد آن را بفهمیم. اگر اسکریپت را مشاهده کنید ، به وضوح می توانیم آن را به دو قسمت جداگانه تقسیم کنیم - قسمت اول که در آن داده ها را از جدول اصلی همانطور که هست انتخاب می کنیم و در قسمت دوم ، نحوه ایجاد جدول محوری را تعریف می کنیم. در اسکریپت ، ما همچنین برخی از کلمات کلیدی خاص مانند SUM ، FOR و IN را ذکر می کنیم ، که فقط برای اپراتور PIVOT استفاده می شود. بیایید سریع در مورد این کلمات کلیدی صحبت کنیم.

عملگر SUM

در اسکریپت ، من از عملگر SUM استفاده کرده ام که اساساً مقادیر ستون Marks را جمع می کند تا بتواند از آن در جدول محوری استفاده شود. استفاده از ستون تجمعی که می تواند برای بخش مقادیر نمایش دهد ، برای اپراتور محوری اجباری است.

کلمه کلیدی FOR

کلید واژه FOR کلمه کلیدی ویژه ای است که برای جدول محوری در اسکریپت های SQL Server استفاده می شود. این اپراتور به اپراتور محوری می گوید که ما باید روی کدام ستون تابع محوری را اعمال کنیم. اصولاً ستونی که قرار است از ردیف به ستون تبدیل شود.

کلمه کلیدی IN

کلید واژه IN ، همانطور که در بالا توضیح داده شد ، تمام مقادیر متمایز ستون محوری را که می خواهیم به لیست ستون جدول محوری اضافه کنیم ، لیست می کند. برای این مثال ، از آنجا که برای ستون Subject فقط سه مقدار مشخص داریم ، هر سه مورد موجود در لیست را برای کلمه کلیدی IN ارائه می دهیم.

تنها محدودیتی که در این فرآیند وجود دارد این است که برای ستون هایی که باید از جدول چرخش انتخاب کنیم باید مقادیر سخت کدگذاری شده را ارائه دهیم. به عنوان مثال ، اگر یک موضوع موضوع جدید در جدول وارد شود ، جدول محوری نمی تواند مقدار جدید را به عنوان یک ستون نمایش دهد زیرا در لیست برای اپراتور IN تعریف نشده است. بگذارید پیش برویم و چند رکورد را برای یک موضوع متفاوت - "تاریخچه" در جدول وارد کنیم.

INSERT INTO Grades VALUES

('Jacob','History',80),

('Amilee','History',90)

GO

بیایید همانطور که قبلاً برای نمایش جدول محوری پرس و جو را اجرا کنیم.

همانطور که مشاهده می کنید ، موضوع جدیدی که ما در جدول وارد کردیم در جدول PIVOT در دسترس نیست. به این دلیل که ستون جدید را در لیست IN اپراتور PIVOT ذکر نکردیم. این یکی از محدودیت های جدول PIVOT در SQL است. هر بار که می خواهیم ستونی جدید در PIVOT قرار دهیم ، باید کد اصلی را تغییر دهیم و اصلاح کنیم.

سناریوی دیگر این است که اگر الزامات تغییر کند و اکنون ، ما باید دانش آموزان را به جای دروس محوری کنیم ، حتی در چنین شرایطی ، باید کل درخواست را اصلاح کنیم. برای جلوگیری از این امر ، می توانیم چیزی پویا ایجاد کنیم که در آن بتوانیم ستونهایی را که به جدول PIVOT نیاز داریم پیکربندی کنیم. بگذارید جلوتر برویم و بفهمیم که چگونه یک روش ذخیره سازی پویا ایجاد کنیم که یک جدول PIVOT را در SQL بازگرداند.

ساخت روش ذخیره سازی پویا برای جداول PIVOT

بیایید کل اسکریپت PIVOT را در یک روش ذخیره شده محصور کنیم. این رویه ذخیره شده دارای گزینه های قابل تنظیم است که در آن می توانیم فقط با تغییر برخی از مقادیر پارامتر ، نیازهای خود را شخصی سازی کنیم. متن جدول PIVOT پویا در SQL در زیر است.

CREATE PROCEDURE dbo.DynamicPivotTableInSql

  @ColumnToPivot  NVARCHAR(255),

  @ListToPivot    NVARCHAR(255)

AS

BEGIN

  DECLARE @SqlStatement NVARCHAR(MAX)

  SET @SqlStatement = N'

    SELECT * FROM (

      SELECT

        [Student],

        [Subject],

        [Marks]

      FROM Grades

    ) StudentResults

    PIVOT (

      SUM([Marks])

      FOR ['+@ColumnToPivot+']

      IN (

        '+@ListToPivot+'

      )

    ) AS PivotTable

  ';

  EXEC(@SqlStatement)

END

همانطور که در اسکریپت بالا مشاهده می کنید ، من دو متغیر پارامتر دارم. جزئیات این دو پارامتر به شرح زیر است.

ColumnToPivot - این پارامتر نام ستونی را در جدول پایه ای که قرار است جدول محوری روی آن اعمال شود ، می پذیرد. برای سناریوی فعلی ، این ستون "موضوع" خواهد بود زیرا ما می خواهیم جدول پایه را چرخانده و تمام موضوعات را در ستون ها نمایش دهیم.    

ListToPivot - این پارامتر لیستی از مقادیری را که می خواهیم بصورت ستونی در جدول محوری در SQL تجسم کنیم ، می پذیرد.

 

اجرای رویه ذخیره شده پویا

اکنون که رویه ذخیره شده پویای ما آماده است ، اجازه دهید پیش برویم و آن را اجرا کنیم. اجازه دهید اولین سناریو را که در آن هر سه موضوع - ریاضیات ، علوم و جغرافیا را در جدول محوری در SQL تجسم کردیم ، تکرار کنیم. متن زیر را اجرا کنید.

EXEC dbo.DynamicPivotTableInSql

  N'Subject'

  ,N'[Mathematics],[Science],[Geography]'

 

 همانطور که مشاهده می کنید ، اکنون نام ستون "موضوع" را به عنوان اولین پارامتر و لیست ستون های محوری را به عنوان ستون دوم ارائه داده ایم.

فرض کنید ، اکنون ما می خواهیم علامت های ستون "History" را نیز در این جدول محوری قرار دهیم ، تنها کاری که باید انجام دهید این است که نام ستون را در پارامتر دوم اضافه کنید و روش ذخیره شده را اجرا کنید.

EXEC dbo.DynamicPivotTableInSql

  N'Subject'

  ,N'[Mathematics],[Science],[Geography],[History]'

 

به همین آسانی ، می توانید ستون هایی را که می خواهید اضافه کنید به لیست اضافه کنید و جدول محوری بر اساس آن نمایش داده می شود

بگذارید اکنون سناریوی دیگری را در نظر بگیریم که در آن شما باید نام دانش آموزان را در ستون ها و موضوعات موجود در ردیف ها را نشان دهید - فقط یک برعکس سناریوی کاری که ما در کل انجام داده ایم. همانطور که انتظار داشتید راه حل نیز ساده است. ما فقط مقادیر موجود در هر دو پارامتر را اصلاح خواهیم کرد به گونه ای که پارامتر اول ستون "Student" را نشان می دهد و پارامتر دوم شامل لیست دانش آموزان مورد نظر شما به همراه ستون ها خواهد بود. روش ذخیره شده به شرح زیر است.

EXEC dbo.DynamicPivotTableInSql

  N'Student'

  ,N'[Amilee],[Jacob]'

همانطور که در تصویر بالا مشاهده می کنید ، جدول محوری در SQL بدون نیاز به تغییر کد اصلی در پشت ، به صورت پویا اصلاح می شود.

 

نتیجه:

در این مقاله ، من توضیح داده ام که جدول محوری در SQL چیست و نحوه ایجاد آن. من همچنین یک سناریو ساده را نشان داده ام که در آن می توانید جداول محوری را پیاده سازی کنید. سرانجام ، من همچنین نشان دادم که چگونه می توان اسکریپت جدول محوری را پارامتر کرد ، به طوری که ساختار جدول مقادیر بدون تغییر کد زیر به راحتی قابل تغییر است.