PIVOT و UNPIVOT در SQL Server – قسمت اول

درباره نویسنده

مهدی شیشه بری شیشه بری

مهدی شیشه بری

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

19 دیدگاه

  1. مرتضی

     با سلام واحترام

    مرسی خیلی خوب مطلب آموزشی.
    پاسخ
  3. داود وحدانی

    داود وحدانی

       با سلام 

    و تشکر مطلب عالی بود ممنون منتظر قسمته ای بعدی این مطلب هستیم
    پاسخ
  4. حسن ضرابی

    حسن ضرابی

      با سلام و خسته نباشید

    از مقاله خیلی خوبتان بسیار ممنونم خیلی خوب و عالی بود

    پاسخ
  5. علی اکبر شعبانی

    علی اکبر شعبانی

       خیلی خوب بود مقالتون ممنونم

    پاسخ

  6. Homa hedarpour

        خیلی خیلی منمونم ،بسیا رسا بود.

    پاسخ

  7. mobtaker

        سلام استاد ، وقت بخیر

    ضمن تشکر از مقاله های مفیدتان .

    درنظر بگیرید به غیر از فیلد amount یک فیلد دیگر به عنوان Percent (درصد) نیز در جدول قرار دارد که قرار است آنرا هم بعد از چرخش در گزارش نمایش دهیم . برای این مهم چه راه کاری میشود ارایه داد.
    پاسخ

  8. محسن

        خیلی خوب بود ، تشکر

    پاسخ

  9. ab

        سلام

    استاد ببخشید در مثال شما تعداد داده ها(quarter) مشخص بود حال اگر تعداد داده ها مشخص نباشید چگونه باید کد مربوطه را بنویسیم
    پاسخ
    1. مهدی شیشه بری شیشه بری

      مهدی شیشه بری شیشه بری

        سلام
      لطفا قسمت دوم مقاله را مطالعه کنید. پاسخ تان را آن جا پیدا خواهید کرد.
      شاد باشید.

      پاسخ

  10. ab

       سلام خواندم اما متاسفانه جوابم را بدست نیاوردم

    فرض کنید
    فرض کنید جدول زیر را داریم 
    CREATE TABLE [dbo].[test۳](
        [ID] [int] IDENTITY(۱,۱) NOT NULL,
        [Date] [date] NULL,
        [Name] [nvarchar](۵۰) NULL,
        [Price] [int] NULL,
     CONSTRAINT [PK_test۳] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    و داده های زیر را داخل ان وارد کرده ایم 

    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۱’,’a’,۱۰۰)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۲’,’a’,۱۰۱)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۳’,’a’,۱۰۲)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۱’,’b’,۱۰۳)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۲’,’b’,۱۰۴)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۳’,’b’,۱۰۵)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۱’,’c’,۱۰۶)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۲’,’c’,۱۰۷)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۱’,’d’,۱۰۹)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۲’,’d’,۱۱۰)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۳’,’d’,۱۱۱)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۱’,’e’,۱۱۲)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۲’,’e’,۱۱۳)
    insert into [dbo].[test۳] values(‘۲۰۱۶-۰۵-۰۳’,’e’,۱۱۴)
    بدین صورت که به تعداد روزها سطر ایجاد شود و به تعداد داده های ستون name برای خروچی ستون ایجاد شود و قیمتهای مربوط زیر هر نام براساس ان تاریخ نمایش داده شود 
    درضمن تعداد داده های ستون name متغییر است وتعداد ثابتی ندارد 
    چگونه من باید کوئری بنویسم تا به جواب مورد نظرم برسم 
    چون من نتونستم با قسمت دوم اموزش خوب شما به جواب برسم
    اگر درست نمایش داده شود چنین چیزی می خوام

    Date    a   b   c   d   e
     
    ۲۰۱۶-۰۵-۰۱  ۱۰۰ ۱۰۳ ۱۰۶ ۱۰۹ ۱۱۲
     
    ۲۰۱۶-۰۵-۰۲  ۱۰۱ ۱۰۴ ۱۰۷ ۱۱۰ ۱۱۳
     
    ۲۰۱۶-۰۵-۰۳  ۱۰۲ ۱۰۵ ۱۰۸ ۱۱۱ ۱۱۴

    باتشکر 

    پاسخ
    1. مهدی شیشه بری شیشه بری

      مهدی شیشه بری شیشه بری

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

      DECLARE @Name VARCHAR(MAX)
      SET @Name=”
      SELECT @Name=@Name+Name+’,’ FROM test3
          GROUP BY Name
      SET @Name=LEFT(@Name,LEN(@Name)-1)

      EXEC(‘SELECT * FROM
       (SELECT Date,Name,Price FROM test3)t
       PIVOT
       (SUM(Price) FOR Name
       IN (‘+@Name+’))pTable’)

      پاسخ

      1. ab

            باسلام و سپاس از جواب خوبتان

        دو سوال 
        1) من کد شما را چگونه می توانم تبدیل به یک ویو یا فانکشن کنم
        2)کدشمارا تبدیل به ویو کرده ام و از طریق کد زیر در یک دیتا گرید نمایش میدهم ولی فقط ستون name را نمایش می دهد چگونه می توانم همه ستونها را دردیتاگرید نمایش بدهم
        private void Form1_Load(object sender, EventArgs e)
               {
                   DataClasses1DataContext dc = new DataClasses1DataContext();
                   var staff = dc.aaaa();
                   radGridView1.DataSource = staff;
               }

  11. جواد پهلوان

    سلام بسیار ممنون برای مقاله بسیار خوبتون
    من از قابلیت pivot در پروسیجر استفاده کردم و وقتی میخواهم توی پروژه ام اضافه کنم میگه نوعش رو نمیشناسه
    از linq استفاده میکنم ممنون میشم اگه راهنمایی کنید
    تشکر

    پاسخ
  12. هادی سعیدی

    هادی سعیدی

    باسلام
    بسیار عالی.سپاسگزار

    پاسخ

  13. prvsh

    سلام، ممنون از آموزش خوبتون
    مشکلی که من دارم اینه که به جای ستون amount در جدول مثال، ستونی دارم که نوع داده ای اون nvarchar هستش و به همین دلیل نمیتونم از توابع جمعی استفاده کنم. میشه لطفا راهنماییم کنین که چجوری میتونم این کار رو انجام بدم؟
    باتشکر

    پاسخ
    1. آرزو محمدزاده

      آرزو محمدزاده

      با سلام
      اگر جنس دیتای nvarchar شما عددی باشد
      می توانید آن را CAST یا CONVERT کنید به INT یا BIGINT بعد از توابع تجمیعی استفاده کنید.

      تشکر از همراهی شما

      پاسخ

  14. مصیب

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

    پاسخ

  15. میلاد حاتمی

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

    پاسخ

  16. Rp

    درود و سپاس از مقاله خوبتان
    من سعی کردم و پاسخ تمرین را بصورت زیر نوشتم :
    SELECT * FROM
    (SELECT CAST(YEAR AS CHAR(4)) YEAR,quarter,amount FROM sale)s
    PIVOT
    (SUM(s.amount) FOR quarter
    IN ([spring],[summer],[autumn],[winter])
    )pTable
    UNION
    SELECT ‘Totalsum’,* FROM
    (SELECT quarter,amount FROM sale)s
    PIVOT
    (SUM(s.amount) FOR quarter
    IN ([spring],[summer],[autumn],[winter])
    )pTable

    پاسخ

ارسال یک نظر

نشانی ایمیل شما منتشر نخواهد شد.

تمامی حقوق مادی و معنوی این وب سایت متعلق به نیک آموز می باشد.
این سایت توسط تیم آموزش برنامه نویسی نیک آموز مدیریت می شود.