خانه SQL Server PIVOT و UNPIVOT در SQL Server – قسمت دوم SQL Server نوشته شده توسط: مهدی شیشه بری ۰۲ تیر ۱۳۹۵ زمان مطالعه: 6 دقیقه ۵ (۱) مقدمه PIVOT و UNPIVOT در SQL Server در قسمت اول، با ساختار PIVOT و نحوه چگونگی استفاده از آن آشنا شدید. این بار به سراغ دیتابیس معروف Northwind می رویم و می خواهیم بدانیم در جدول Customers به ازای کشورهای uk، spain و usa چه تعداد کارمند داریم. بنابراین در ابتدای کار می بایست تکلیف سه مورد زیر را مشخص کنیم: Aggregate Column: فیلد CustomerID PIVOT Column: فیلد Country لیستی که قرار است گزارش براساس آن تهیه شود که در مثال فرضی ما، مقادیر فیلد Country و شامل uk، spain و usa خواهد بود. اسکریپت زیر را اجرا می کنیم: SELECT * FROM (SELECT Country,CustomerID FROM Customers)C PIVOT (COUNT(CustomerID) FOR Country IN ([uk],[usa],[spain]))pTable خوب، تا این جای کار همه چیز مطابق با خواسته ما بود اما آیا شما می دانید که در جدول Customers چه کشورهایی وجود دارد؟ اگر تعداد این کشورها زیاد باشد، آیا منطقی است که پس از شناسایی آن ها، لیست عریض و طویلی از عنوان کشورها را در جلو IN و در ساختار PIVOT، ردیف کنیم؟ آیا این امکان وجود ندارد که در آینده عناوین کشورهای جدیدی به جدول مان اضافه شوند؟ و … پاسخ مناسب به حل مشکلات مطرح شده، استفاده از Dynamic T-SQL خواهد بود. Dynamic T-SQL در واقع اسکریپت هایی است که به صورت Dynamic ایجاد می کنیم و در همان لحظه، آن ها را اجرا می کنیم. با استفاده از Dynamic T-SQL می توان شرایطی پویا و متنوع در زمان اجرای یک کوئری ایجاد کرد. در اسکریپت زیر، متغیرهای مورد نیاز را تعریف و مقداردهی کرده و سپس با الحاق مناسبی با عبارات T-SQL، از طریق EXEC آن ها را اجرا می کنیم. اکنون برای آن که بتوانیم یک Dynamic PIVOT داشته باشیم، دقیقا از این تکنیک استفاده می کنیم. فرض می کنیم که می خواهیم بدانیم از هر کشور چه تعداد مشتری داریم. پس می بایست لیست کشورهای موجود را از جدول Customers استخراج کنیم. DECLARE @country VARCHAR(MAX) SET @country='' SELECT @country=@country+Country+',' FROM Customers GROUP BY Country SET @country=LEFT(@country,LEN(@country)-1) ابتدا متغیرcountry@ را تعریف می کنیم. در خط دوم اسکریپت بالا، ابتدا مقدار country@ را برابر Blank قرار می دهیم. توجه داشته باشید که اگر این کار را انجام ندهید با مشکل روبرو خواهید شد زیرا در ابتدا، مقدار country@ در هنگام تعریف، برابر با NULL خواهد بود و همواره جمع یک رشته با NULL برابر با NULL خواهد شد! در عبارت SELECT، تمامی کشورها را از طریق جدول Customers در متغیرcountry@ به همراه جداکننده ویرگول، ذخیره می کنیم. توجه داشته باشید که استفاده از GROUP BY به منظور جلوگیری از درج تکراری عناوین کشورها است. در خط آخر هم با توجه به این که در انتهای رشته ی country@ یک علامت ویرگول اضافی داریم، آن را حذف می کنیم. لیست تمامی کشورها در متغیر country@ ذخیره شده و می بایست آن را به عنوان لیست مورد جستجو در جلو عبارت IN در ساختار PIVOT قرار دهیم. EXEC('SELECT * FROM (SELECT Country,customerID FROM Customers)C PIVOT (count(customerID) FOR Country IN ('+@country+'))pTable') و اما حالا شما به عنوان تمرین، تلاش کنید کوئری ای بنویسید که خروجی زیر را نمایش دهد. این خروجی قرار است گزارشی باشد که عناوین همه customerIDها در Header قرار گرفته و متناسب با هر کدام، CompanyNameشان نمایش داده شود. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش برچسب ها # PIVOT# SQL Server# UNPIVOT# آموزش SQL Server# آموزش کامل pivot table معرفی نویسنده معرفی محصول مهدی شیشه بری دوره آموزش کوئری نویسی در SQL Server 1.000.000 تومان مقالات مرتبط ۰۶ مهر برنامه نویسی تفاوت بین برنامه نویس ، کدنویس ، توسعه دهنده و مهندس نرم افزار تیم فنی نیک آموز ۰۴ مهر SQL Server دستور Case در SQL Server – قسمت اول محمد سلیم آبادی ۰۴ مهر برنامه نویسی راهنمای مسیر شغلی برنامه نویسی (چگونه برنامه نویس شویم؟) تیم فنی نیک آموز ۰۲ مهر برنامه نویسی الگوریتم برنامه نویسی چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ محمدحسین فخرآوری ۰۶ / ۰۸ / ۹۸ - ۰۲:۳۵ با سلام قسمت UNPIVOT اگه ممکن لینک کنید من پیداش نکردم در سایت. پاسخ به دیدگاه آرزو محمدزاده ۱۰ / ۰۶ / ۰۰ - ۰۸:۲۳ https://nikamooz.com/pivot-unpivot-sql-server-part1/ پاسخ به دیدگاه محمدحسین فخرآوری ۰۶ / ۰۸ / ۹۸ - ۰۲:۳۵ با سلام قسمت UNPIVOT اگه ممکن لینک کنید من پیداش نکردم در سایت. پاسخ به دیدگاه آرزو محمدزاده ۱۰ / ۰۶ / ۰۰ - ۰۸:۲۳ https://nikamooz.com/pivot-unpivot-sql-server-part1/ پاسخ به دیدگاه مریم.خانی ۱۵ / ۰۵ / ۹۶ - ۰۷:۳۳ بسیار عالی بود. مفهوم کاملا واضح بیان شده و شیوه نوشتاری هم عالی بود کمال تشکر را دارم پاسخ به دیدگاه مریم.خانی ۱۵ / ۰۵ / ۹۶ - ۰۷:۳۳ بسیار عالی بود. مفهوم کاملا واضح بیان شده و شیوه نوشتاری هم عالی بود کمال تشکر را دارم پاسخ به دیدگاه شاهرخ ۲۶ / ۰۴ / ۹۶ - ۰۹:۲۲ واقعا عالی بود ممنون از شما پاسخ به دیدگاه شاهرخ ۲۶ / ۰۴ / ۹۶ - ۰۹:۲۲ واقعا عالی بود ممنون از شما پاسخ به دیدگاه جواد پهلوان ۱۳ / ۰۶ / ۹۵ - ۰۷:۴۱ سلام خیلی خیلی ممنون بابت مقاله بسیار خوبتون ببخشید یک سوال داشتم من از این قابلیت در پروسیجر استفاده میکنم و وقتی میخوام پروسیجر رو به پروژه ام اضافه کنم(از linq استفاده میکنم) میگه نوعش رو نمی شناسه از temp table هم استفاده کردم که وقتی کوئری اجرا میشه بریزه توی temp table اما بازهم میگه نمیشناسه خیلی ممنون میشم اگه راهنمایی کنید تشکر پاسخ به دیدگاه جواد پهلوان ۱۳ / ۰۶ / ۹۵ - ۰۷:۴۱ سلام خیلی خیلی ممنون بابت مقاله بسیار خوبتون ببخشید یک سوال داشتم من از این قابلیت در پروسیجر استفاده میکنم و وقتی میخوام پروسیجر رو به پروژه ام اضافه کنم(از linq استفاده میکنم) میگه نوعش رو نمی شناسه از temp table هم استفاده کردم که وقتی کوئری اجرا میشه بریزه توی temp table اما بازهم میگه نمیشناسه خیلی ممنون میشم اگه راهنمایی کنید تشکر پاسخ به دیدگاه ha_zarabi_vb6@outlook.com ۱۰ / ۰۴ / ۹۵ - ۰۷:۳۰ با سلام و خسته نباشید خدمت شما جناب مهندس عزیز خیلی عالی توضیح دادید و من خیلی استفاده کردم اگر ممکن هست در مورد pivot که بحث خیلی مهمی هست بیشتر مقاله در سایت خوب نیک آموز بگذارید و حتی با مثالهای بیشتر ممنونم از شما. پاسخ به دیدگاه مهدی شیشه بری ۱۹ / ۰۴ / ۹۵ - ۱۰:۵۷ سلامخوشحالم که مقاله برای شما مفید بوده.سعی می کنم در آینده مقالات بیشتری در این زمینه ارائه کنم. پاسخ به دیدگاه ha_zarabi_vb6@outlook.com ۱۰ / ۰۴ / ۹۵ - ۰۷:۳۰ با سلام و خسته نباشید خدمت شما جناب مهندس عزیز خیلی عالی توضیح دادید و من خیلی استفاده کردم اگر ممکن هست در مورد pivot که بحث خیلی مهمی هست بیشتر مقاله در سایت خوب نیک آموز بگذارید و حتی با مثالهای بیشتر ممنونم از شما. پاسخ به دیدگاه مهدی شیشه بری ۱۹ / ۰۴ / ۹۵ - ۱۰:۵۷ سلامخوشحالم که مقاله برای شما مفید بوده.سعی می کنم در آینده مقالات بیشتری در این زمینه ارائه کنم. پاسخ به دیدگاه 1 2