استفاده ازعملگرPIVOTبرای تبدیل مقادیر تک ستونی به چندستونی با کمک توابع تجمع
برخی موارد نیاز داریم که یک مقدار چند ستونی را به چند ستونی تبدیل کنیم. این مورد در ساخت گزارشات کاربرد زیادی دارد. به عنوان مثال فرض کنید یک کوئری آماده دارید که توسط آن لیستی از تمام شیفتهای افراد به همراه نام قسمتهای مربوطه در یک سازمان ارائه میشود. حال نیاز دارید بفهمید که هر کدام از قسمتهای سازمان در کدام شیفت، چند نفر کارمند دارد. عملا شما به یک جدول متقاطع نیاز دارید که ستونهای آن شامل نام قسمتهای مختلف سازمان و سطرهای آن نام شیفتهای مختلف میباشد.
ابتدا شما را با گرامر عبارت مورد نظر آشنا میکنم :
FROM table_source
-- جدول یا کوئری منبع
PIVOT (
aggregate_function ( value_column )
-- تابع تجمعی مورد نظر به همراه ستون مورد نظر
FOR pivot_column
-- ستونی که برای ساختن نام ستونها استفاده میشود
IN ( <column_list>)
-- مقادیری که از ستون بالا برای عملیات انتخاب میشود
) table_alias
-- نام مستعار جدول خروجی
گاهی اوقات پس از اینکه روی نام پایگاه داده کلیک راست را میزنیم و گزینه New Database Diagram را انتخاب میکنیم با خطای زیر مواجه میشویم.
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
دلیل آن هم به خوبی درپیغام خطا نمایش داده شده است و آن هم این است که برای پایگاه داده مورد نظر شما یک owner به صورت مستقیم تعریف نشده است.
برای رفع این مشکل میتوانید از اسکریپت زیر استفاده کرده و عمل مورد نیاز برای رفع مشکل را انجام دهید.
ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login
درست مانند :
ALTER AUTHORIZATION ON DATABASE::RamezaniDB TO sa
Catalog view ها اطلاعاتی را در خود نگهداری میکنند که موتور پایگاه داده SQL Server از آنها استفاده مینماید. استفاده از این view ها به کسانی توصیه میشود که به اطلاعاتی در رابطه با ساختار پایگاه داده خود نیاز دارند. این view ها عمومیترین واسط موجود برای به دست آوردن متادیتاهایی در رابطه با پایگاه داده میباشند. شما میتوانید توسط کوئریهای ساده به اطلاعات ارزشمندی به دست آورید. به عنوان مثال میتوانید ارتباط بین آبجکتهای موجود در پایگاه داده را با یک کوئری به دست آورید. یا مثلا میتوانید لیست نام تمام جداول موجود یا کلیدهای شناسه موجود در پایگاه داده را به سادگی به دست آورید. USE MyTable SELECT name FROM sys.objects WHERE type = 'P'
مثلا تکه کد زیر لیست تمام stored procedure های تعریف شده را به شما در خروجی خواهد داد :
به کمک کوئری زیر میتوانید نتایج یک فیلد در پایگاه داده را به صورت comma separated دریافت کنید. گفتم شاید این مورد به درد دوستان هم بخورد. لطفا به کد زیر دقت نمایید :
DECLARE @NameList nvarchar(Max)
SELECT @NameList = COALESCE(@NameList + ', ', '') +
ColumnName
FROM TableName
WHERE ID < 10
SELECT @NameList
در خیلی مواقع ملاحظه میشود که برای نمایش تعدادی از رکوردهای یک جدول در پایگاه داده کل مقادیر موجود درآن توسط یک دستور select به دست میآید و صفحهبندی خروجی به کنترلهای موجود سپرده میشود. اگر پایگاه داده ما دارای تعداد زیادی رکورد باشد آن موقع است که ما دچار مشکل میشویم. فرض کنید به طور همزمان ۵ نفر (که تعداد زیادی نیستند) از برنامه ما که شامل ۱۰۰۰۰۰ سطر داده میباشد استفاده کنند و در هر صفحه ما ۱۰ رکورد نمایش داده شود و صفحهبندی ما از نوع معقولی نباشد در این صورت به جای اینکه ما با ۵×۱۰ رکورد داده را بارگزاری کنیم، ۵×۱۰۰۰۰۰ رکورد یعنی ۵۰۰۰۰۰ رکورد را برای به دست آوردن ۵۰ رکورد بارگزاری میکنیم. در زیر روشی شرح داده میشود که سربار اضافه از روی برنامه و سرورهای مربوطه حذف شود. به stored procedure و توضیحات مربوط به آن توجه فرمایید :
CREATE PROCEDURE sp_PagedItems
(
@Page int,
@RecsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
شاید بعضی اوقات نیاز پیدا کرده باشید که داده هایی را در جداول SQL Server وارد کنید. این مطلب را که قبلا در جایی خوانده بودم را برای شما مینویسم تا شاید به درد بعضی از دوستان بخورد.
- در این مرحله اسکریپت زیر را اجرا کنید تاداده ها از روی فایل مورد نظر روی جدول بارگزاری شود. توجه داشته باشید که اگر خطایی در حین انجام این کار رخ دهد فقط همان سطر وارد نمیشود و کار ادامه میابد.
همانطور که میدانید CSV فایلیست که در آن مطالب به کمک کاما از هم جدا شدهاند و یک فرمت استاندارد و شناخته شده میباشد که خیلی از نرمافزارهای بزرگ مانند excel هم آن را تولید و هم از آن داده دریافت میکنند.
۱-ابتدا توسط اسکریپت زیر یک جدول تستی بسازید :
Create TestTable
USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
۲-یک فایل با فرمت CSV در درایو C و در آدرس C:\csvtest.txt بسازید که دارای محتوای زیر باشد :
1,Masoud,Ramezani,19830101
2,Name1,Family1,19790122
3, Name2,Family2,20071101
4, Name3,Family3,20040202
BULK INSERT CSVTest
FROM 'c:\csvTest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
۴-توسط اسکریپت زیر محتوای جدول را چک کنید.
SELECT *
FROM CSVTest
GO
۵- در انتها هم میتوانید این جدول تستی را از پایگاه داده حذف نمایید.
Drop Table CSVTest
GO
SQL Server Compact یک پایگاه داده رابطهای فشرده است که توسط شرکت مایکروسافت جهت استفاده به عنوان پایگاه داده در دستگاههای موبایل و کامپیوترهای رومیزی عرضه شده است.
قبل از اینکه این محصول برای سکوهای رومیزی عرضه شود با عنوان SQL Server برای Windows CE شناخته میشد. آخرین نسخه منتشر شده این محصول SQL Server Compact 3.5 SP1 است که علاوه بر Windows Mobile 2003, 5.0, 6.0, 6.5 از .Net Framework 3.5 هم پشتیبانی مینماید.
نکاتی در رابطه با SSCE :
۱- روی نسخههای ۳۲ و ۶۴ بیتی پشتیبانی میشود.
۲- برای دانلود و توزیع مجدد رایگان است و میتوانید آن را از سایت مایکروسافت دانلود نمایید.
۳- یک API مشترک را با دیگر نسخههای SQL Server به اشتراک میگذارد.
۴- شامل ADO.NET Provider برای دسترسی به دادهها با استفاده از API مربوط میباشد.
۵- دارای قابلیتهای همزمانسازی توکار میباشد.
خصوصیت SQL Server Compact Edition SQL Server Express Edition خصوصیات قرارگیری و نصب اندازه نصب ۱.۷ مگابایت حجم دانلود ۱.۸ مگابایت حجم روی دیسک سخت ۵۳.۸ مگابایت حجم دانلود ۱۹۷ مگابایت حجم روی دیسک سخت همخوانی با ClickOnce دارد دارد نصب خصوصی- ادغام شده با نرمافزار دارد ندارد گزینه نصب غیر مدیریتی دارد ندارد قابلیتاجرا روی ویندوز موبایل دارد ندارد نصب مرکزی با استفاده از یک MSI دارد دارد راهاندازی در هنگام کار با نرمافزار دارد ندارد پشتیبانی از پردازنده ۶۴ بیتی نسخه ۳.۱ ندارد اما نسخههای بعدی دارد دارد قابلیت اجرا به عنوان سرویس ندارد دارد خصوصیات فایل داده قالب فایل فایل واحد چندین فایل انباره فایل داده در یک محل اشتراکی در شبکه دارد ندارد پشتیبانی از پسوندهای فایلی دیگر دارد ندارد حداکثر اندازه پایگاه داده ۴ گیگابایت ۴ گیگابایت ذخیره XML دارد (ذخیره در nText) دارد قالب فایلی بدون رمز و سند امن دارد ندارد خصوصیات برنامهنویسی خصوصیات رایج T-SQL دارد دارد T-SQL رویهای ندارد دارد دسترسی دادهای از راه دور (RDA) دارد ندارد ADO.NET Sync Framework دارد ندارد (برای نسخههای آینده) مشترک برای merge replication دارد دارد تراکنشهای ساده دارد دارد تراکنشهای توزیع شده ندارد دارد XML - XQuery و QPath به صورت بومی ندارد دارد Sp ها، view ها و تریگرها ندارد دارد امنیت بر مبنای نقش ندارد دارد تعداد ارتباطات همزمان ۲۵۶ نامحدود
صفحه قبل 1 صفحه بعد