pivot Table در Sql Server قسمت 1

Pivot table ها در واقع همانند سایر  Table های دیگر می باشند فقط با این تفاوت که در نمایش ردیف و ستون های آنها مقداری تفاوت وجود دارد .

دستور pivot جزو دستوراتی از Sql server می باشد که ممکن است در بیشتر پروژه هایمان برای نمایش گزارشات مختلف   به استفاده از آن نیاز داشته باشیم . کاربرد دستور pivot تقریبا تبدیل نمایش خروجی یک کوئری از حالت ردیفی به ستونی می باشد . به صورت خیلی خلاصه بخواهیم نحوه کارکرد این دستور را بیان کنیم ، کار آن یک چرخش 90 درجه جداول  می باشد ، یعنی جای ستون و ردیف ها را با هم جابه جا کنیم .

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

Pivot-in-sql server
ساختار دستور Pivot به صورت زیر می باشد :

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,
    ...
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>

PIVOT

(<aggregation function>(<column being aggregated>)

FOR [<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],...)

) AS <alias for the pivot table>

<optional ORDER BY clause>;

بعد از نوشتن Pivot و داخل پرانتز مقابل آن خواهیم داشت :

1- یک aggregation function .

2- نوشتن کلمه کلیدی For و  تعیین نام فیلدی که قصد تبدیل آن به چنید ستون را خواهیم داشت .

همانطور که درsyntax این دستور ملاحضه می کنید ،   دستور pivot حتما باید داری یک aggregation function باشد .

فیلد مقابل کلمه For با توجه به مقادیری که بر میگرداند هر کدام به ستون هایی مجزا تبدیل می شوند . مثلا اگر در مقابل for اسم ستونی را داریم که محتوای آن فیلد دارای سه مقدار A,B,C می باشد ، در نتیجه خروجی دستور شاهد سه ستون با عنوان های A,B,C خواهیم بود .

ساختار دستور آن به صورت خلاصه تر به صورت زیر می باشد :

Select Field1,Field2,...,Field n
From(
Table_Source
)
Pivot ( Aggregate Function(Value_Column)
For Pivot Column In(مقادیر مورد نظر)
) Table Alias

بررسی یک مثال عملی :

جدولی به اسم Invoice داریم که شامل سه فیلد به عنوان های زیر می باشد :

InvoiceNumber , InvoiceDate , InvoiceAmmount

ابتدا با استفاده از کوئری زیر جدول مورد نظر را ایجاد میکنیم :

create table Invoice
(
InvoiceNumber   int primary key identity,
InvoiceDate        date,
InvoiceAmount    int
)

و چندین رکورد را در آن درج میکنیم . قصد داریم میزان فروش محصولات را در هرماه از سال نمایش دهیم . دستور مورد نظر را به صورت زیر می نویسیم :

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot

که خروجی آن به صورت زیر می باشد :

pivot-in-sqlserver

و اما توضیح کدهای بالا :

در داخل دومین دستور Select که به صورت subquery نوشته شده است ، سه فیلد انتخاب کرده ایم که شامل :

select year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],        InvoiceAmount as Amount  FROM Invoice

 تا اینجا اگر این دستور را اجرا کنیم ، در خروجی شاهد سه ستون سال ، ماه و مقدار خواهیم بود .

فیلد سال که ثابت است و می خواهیم یکی از ستون های جدول باشد ، از Amount در قسمت Aggregate function از آن استفاده می کنیم و از month در قسمت for در دخل دستور pivot استفاده می کنیم .

مقدار ماه که دستور آن را به صورت زیر نوشته ایم :

left(datename(month,invoicedate),3)as [month]

یکی از مقادیر زیر را بر می گرداند :

jan, feb, mar, apr,may, jun, jul, aug, sep, oct, nov, dec

  در قسمت For باید ستونی را که می خواهیم مقدار آن را به چندین ستون تقسیم کنیم را می نویسیم ،که در اینجا ماه فیلد ماه را نوشته ایم  و بعد از آن کلمه کلیدی In  را نوشته و داخل پرانتز مقادیری را که ستون مقابل For بر میگرداند را می نویسیم ، که در این مثال ایم 12 ماه میلادی را نوشته ایم چون فیلد month برای هر ردیف یکی از این 12 ماه را بر میگرداند .

اگر در مقابل دستور For و داخل In  اسم هایی را بنویسیم که توسط فیلد مقابل For برگردانده نمی شود ، مقدار NULL را برای آن ستون خواهیم داشت .مثلا فرض کنید در مثال فوق علاوه بر اسم ماه ها ، دو فیلد اضافی دیگر هم با عنوان های esfand,bahman بنویسیم . چون این دو مقدار هیچ وقت از فیلد ماه با استفاده از دستوری که نوشتیم برگردانده نمی شوند ، پس در خروجی برای این دو فیلد شاهد مقدار NULL خواهیم بود .

 


توسط : عثمان رحیمی  2 ماه قبل ، یکشنبه 17 اسفند 1393 ساعت 00:35  0  4462

نظر شما برای ما مهم است و به ما در بهبود سایت کمک میکند.


ارسال نظر
  • نام (اختیاری ) :
  • پست الکترونیک :
  • توضیحات :

مطالب مرتبط