Convert a column into a comma separated list in Excel

  • Copy the column in Excel
  • Open Word
  • “Paste special” as text only
  • Select the data in Word (the one that you need to convert to text separated with ,), press CtrlH (Find & replace)
  • In “Find what” box type ^p
  • In “Replace with” box type ,
  • Select “Replace all”
Đăng tải tại MS Excel | Bạn nghĩ gì về bài viết này?

Tính tổng (SUM) các ô chẵn, lẻ trong một hàng, cột, bảng trong Excel

Hình ảnh | Posted on by | Bạn nghĩ gì về bài viết này?

Wrong home page opens when I start Firefox – How to fix

Some third-party software providers might change Firefox’s shortcut so that the browser always opens their page. To prevent this from happening, make sure you decline any options to set your site preferences when installing or updating a third-party application.

If the wrong home page keeps opening even when you’ve taken steps to remove a toolbar that’s taken over Firefox and restored your home page, try checking the Firefox shortcut:

  1. Right-click on the Firefox icon on your desktop or task bar, then click on Properties.
  2. In the Target: field, you’ll see the web address that the software provider keeps telling Firefox to load when you open the browser (for example, “”). Delete that web address and click OK to save your changes:
    hijacked shortcut
Đăng tải tại Thủ thuật | Bạn nghĩ gì về bài viết này?

Mail merge using an Excel spreadsheet

Step 1: Prepare data in Excel for mail merge

The most important step in the mail merge process is to set up and prepare your data. You’ll use your Excel spreadsheet as the data source for the recipient list.

Here are some tips to prepare your data for a mail merge. Make sure:

  • Column names in your spreadsheet match the field names you want to insert in your mail merge. For example, to address readers by their first name in your document, you’ll need separate columns for first and last names.
  • All data to be merged is present in the first sheet of your spreadsheet.
  • Data entries with percentages, currencies, and postal codes are correctly formatted in the spreadsheet so that Word can properly read their values.
  • The Excel spreadsheet to be used in the mail merge is stored on your local machine.
  • Changes or additions to your spreadsheet are completed before it’s connected to your mail merge document in Word.


  • You can import information from your Excel spreadsheet by importing information from a comma-separated value (.csv) or a text (.txt) file and use the Text Import Wizard to build a new spreadsheet.
  • For more information, see Prepare your Excel data source for mail merge in Word.

Step 2: Start the mail merge

  1. In Word, choose File > New > Blank document.
  2. On the Mailings tab, in the Start Mail merge group, choose Start Mail Merge, and then choose the kind of merge you want to run.Screenshot of the Mailings tab in Word, showing the Start Mail Merge command and the list of available options for the type of merge you want to run.
  3. Choose Select Recipients > Use an Existing List.Screenshot of the Mailings tab in Word, showing the Select Recipients command with the Use an Existing List option selected.
  4. Browse to your Excel spreadsheet, and then choose Open.
  5. If Word prompts you, choose Sheet1$ > OK.

    Note: Now the Excel spreadsheet is connected to the mail merge document you’re creating in Word.

Edit your mailing list

You can limit who receives your mail.

  1. Choose Edit Recipient List.Screenshot of the Mailings tab in Word, showing the Edit Recipient List command as highlighted.
  2. In the Mail Merge Recipients dialog box, clear the check box next to the name of any person who you don’t want to receive your mailing.Select rows by checking the check box

    Note: You also can sort or filter the list to make it easier to find names and addresses. For more information about sorting and filtering items, see Sort and filter the data for a mail merge.

Step 3: Insert a merge field

You can insert one or more mail merge fields that pull the information from your spreadsheet into your document.

To insert an address block for an envelope, a label, an email message, or a letter

  1. On the Mailings tab, in the Write & Insert Fields group, choose Address Block.Insert Address Block merge field
  2. In the Insert Address Block dialog box, choose a format for the recipient’s name as it will appear on the envelope.Address block options
  3. Choose OK.
  4. Choose File > Save.

To insert a greeting line in an email message or a letter

  1. On the Mailings tab, in the Write & Insert Fields group, choose Greeting Line.Screenshot of the Mailings tab in Word, showing the Greeting Line command as highlighted.
  2. In the Insert Greeting Line dialog box, do the following:
    • Under Greeting line format, change the salutation if necessary by choosing the greeting (Dear is the default), the format for the recipient name, and the ending punctuation (a comma is the default).and
    • Under Greeting line for invalid recipient names, choose an option in the salutation list.
  3. Choose OK.
  4. Choose File > Save.

To insert data from your spreadsheet in an email message or a letter

  1. On the Mailings tab, in the Write & Insert Fields group, choose Insert Merge Field.
  2. In the Insert Merge Field dialog box, under Fields, choose a field name (column name in your spreadsheet), and then choose Insert.
  3. Repeat step 2 as needed, and choose Close when done.
  4. Choose File > Save.

For more information about adding fields from your spreadsheet to the merge document, see Insert mail merge fields. And if you’re interested in learning more about options for setting up email message, see Email merge in Word.

Step 4: Preview and finish the mail merge

After you insert the merge fields you want, preview the results to confirm that the content is okay. and then you’re ready to complete the merge process.

  1. On the Mailings tab, choose Preview Results.Screenshot of the Mailings tab in Word, showing the Preview Results group.
  2. Choose the Next Next record button for mail merge preview results or Previous Previous record button for mail merge preview results record button to move through records in your data source and view how the records will appear in the document.
  3. In the Finish group, choose Finish & Merge, and choose Print Documents or Send E-mail Messages.Screenshot of the Mailings tab in Word, showing the Finish & Merge command and its options.

Step 5: Save your mail merge

When you save the mail merge document, it stays connected to your data source. You can reuse the mail merge document for your next bulk mailing.

  • Open the mail merge document and choose Yes when Word prompts you to keep the connection.

You can see

Đăng tải tại MS Excel | Bạn nghĩ gì về bài viết này?

How to change sa password in SQL Server 2008 express?

If you want to change your ‘sa’ password with SQL Server Management Studio, here are the steps:

  1. Login using Windows Authentication and “.\SQLExpress” as Server Name
  2. Change server authentication mode – Right click on root, choose Properties, from Security tab select “SQL Server and Windows Authentication mode”, click OK Change server authentication mode
  3. Set sa password – Navigate to Security > Logins > sa, right click on it, choose Properties, from General tab set the Password (don’t close the window) Set sa password
  4. Grant permission – Go to Status tab, make sure the Grant and Enabled radiobuttons are chosen, click OK Grant permission
  5. Restart SQLEXPRESS service from your local services (Window+R > services.msc)
Đăng tải tại SQL server | Bạn nghĩ gì về bài viết này?

SFTP Configuration in Total Commander

This is a brief guide to setting up Total Commander to connect to the web space of domains at Platon Webhosting. The domain is used in the pictures as an example, while the procedure is applicable to any domain hosted on our servers.

Connecting to our servers is possible only via SFTP. We do not use FTP due to poor security.

For SFTP connection in Total Commander you will have to download a plugin available at our website. Unpack the package into a folder in the local disk.

The plugin has to be added to Total Commander. In the top TC taskbar, select Options under the Configuration entry.

In the Configuration menu, go to Plugins on the left and in File System Plugins (.WFX), click on Configure.

A new window with installed file system plugins will appear. Click on Add bellow the list.

Find the SFTP plugin in the file where you unpacked it earlier and click on Open.

The plugin will appear in the list. To return to Total Commander, click on the OK buttons.

In the list of disk units, select Network Neighborhood.

The new item Secure FTP connection will be featured in the panel.

For create a new connection press F7.

Entry the name of the connection (optional, it servers only for identification of the connection)

Fill in the following parameters, in the configuration box:

  • Connect to – name of the server you are connecting to (usually the name of your domain)
  • User name – login (the one that you use for login to
  • Password

You will find all necessary logon data in an e-mail and/or a text message from Platon Webhosting. Next, click on OK.

The new connection will appear in the respective Total Commander section as an individual entry. Open the entry to connect to the SFTP space.

If the connection was successful, you will see the contents of the disk space on the server. You will find individual webs hosted by Platon Webhosting in the file public_html.

Contents of individual webpages can be viewed in individual files.

Đăng tải tại Thủ thuật | Bạn nghĩ gì về bài viết này?

Crack the password on an Excel VBA Project

For an .xlsm you need to do it slightly different way. (you can also rename .xls to .xlsm)

  1. Change the extension of the .xlsm file to .zip.
  2. Open the .zip file (with WinZip or WinRar etc) and go to the xl folder.
  3. Extract the vbaProject.bin file and open it in a Hex Editor (with Notepad++ or npp.exe etc).
  4. Search for DPB and replace with DPx and save the file.
  5. Replace the old vbaProject.bin file with this new on in the zipped file.
  6. Change the file extension back to .xlsm.
  7. Open workbook skip through the warning messages.
  8. Open up Visual Basic inside Excel.
  9. Go to Tools > VBAProject Properties > Protection Tab.
  10. Put in a new password and save the .xlsm file.
  11. Close and re open and your new password will work.
Đăng tải tại MS Excel, Thủ thuật | Bạn nghĩ gì về bài viết này?

Starting Outlook displays error This information service is not installed on your computer


When you start Microsoft Office Outlook 2007, Microsoft Outlook 2010, or Microsoft Outlook 2013, you see the following error:

“This information service is not installed on your computer.”


You or your administrator installed Microsoft Office or Microsoft Outlook without the Microsoft Exchange Server provider.


To resolve this issue, you or your administrator should make sure that the Microsoft Exchange Server component is installed. To make sure that the correct component is installed, follow these steps:

  1. In Windows, search for and start Add or Remove Programs.
  2. Click Microsoft Office and then click Change.
  3. Use the Add or Remove Features option.
  4. Navigate to Microsoft Outlook, Outlook Messaging Components, Outlook Mapi Service Providers, and make sure that the Microsoft Exchange Server component is set to Run from my computer.
  5. Click Continue to complete setup.
Đăng tải tại Thủ thuật | Bạn nghĩ gì về bài viết này?

Windows 10: Ép ứng dụng luôn khởi chạy với quyền quản trị

Để thực hiện các thao tác này, trước hết, bạn mở danh sách All apps trong trình đơn Start.

Sau đó, tìm và nhấn phải chuột vào ứng dụng mà mình muốn chạy ở chế độ Administartor Mode (PC World Vietnam sẽ làm mẫu cho cửa sổ dòng lệnh Command Prompt), rồi chọn tiếp Open file location trong trình đơn ngữ cảnh.

Thủ thuật Windows 10
Thiết lập Administrator Mode cho cửa sổ dòng lệnh Command Prompt.

Lưu ý, các ứng dụng mặc định của Windows 10 như Cortana, Edge,…. sẽ không có tùy chọn này.

Sau đó, cửa sổ File Explorer sẽ hiển thị thư mục chứa shortcut của ứng dụng, tại đây, bạn nhấn phải chuột vào shortcut của ứng dụng này và chọn mục Properties trong trình đơn ngữ cảnh.

Thủ thuật Windows 10
Thư mục chứa shortcut của cửa sổ dòng lệnh Command Prompt.

Trong cửa sổ Properties, bạn nhấn mở thẻ Shortcut và nhấn vào nút Advanced…

Thủ thuật Windows 10
Giao diện cửa sổ Properties của Command Prompt.

Tiếp đến, tại cửa sổ Advanced Properties, bạn đánh dấu chọn vào Run as administrator và nhấn OK.

Thủ thuật Windows 10
đánh dấu chọn để Command Prompt luôn chạy ở chế độ Administrator Mode.

Từ bây giờ, ứng dụng này sẽ luôn được mở ở chế độ Administrator Mode mỗi khi bạn khởi chạy shortcut trên trình đơn Start.

Đăng tải tại Kiến thức - Công nghệ, Thủ thuật | Bạn nghĩ gì về bài viết này?

Tổng hợp 36 phím tắt thường dùng trong Excel

0. F4 Lặp lại thao tác đã làm trước đó
  1. Tổ hợp phím tắt cơ bản trong Excel:
1. Ctrl + A Chọn toàn bộ bảng tính trong
2. Ctrl + C Sao chép (Copy)
3. Ctrl + V Dán (Paste)
4. Ctrl + F Bật hộp thoại tìm kiếm
5. Ctrl + H Bật hộp thoại tìm kiếm và thay thế
6. Ctrl + N Tạo mới một bảng tính trắng
7. Ctrl + P Bật hộp thoại in ấn
8. Ctrl + S Lưu bảng tính
9. Ctrl + X Cắt một nội dung đã chọn
10. Ctrl + Z Phục hồi thao tác đã thực hiện trước đó
11 Ctrl + Y  Khôi phục lệnh vừa bỏ (ngược lại với Ctrl+Z)
12. Ctrl + * Chọn vùng dữ liệu liên quan đến ô (cell) hiện tại
13. Ctrl + F4 Đóng bảng tính
14. Alt + F4 Thoát Excel
  1. Tổ hợp phím tắt Di chuyển trong Excel:
15. Ctrl + Mũi tên Di chuyển đến vùng dữ liệu kế tiếp
16. Ctrl + Home Về ô đầu Worksheet (A1)
17. Ctrl + End Về ô có dữ liệu cuối cùng
18. Ctrl + Shift + Home Chọn từ ô hiện tại đến ô A1
19. Ctrl + Shift + End Chọn từ ô hiện tại đến ô có dữ liệu cuối cùng
  1. Tổ hợp phím tắt định dạng trong Excel:
20. Ctrl + B Định dạng In đậm
21. Ctrl + I Định dạng In nghiêng
22. Ctrl + U Định dạng gạch chân
23. Ctrl + 1 Hiển thị hộp thoại Format Cells (*)
  1. Tổ hợp phím tắt thao tác với Ô, Dòng, Cột trong Excel:
24. F2 Sửa nội dung trong ô
25. Ctrl + Spacebar Chèn cột
26. Shift + Spacebar Chèn dòng
27. Shift + F11 Chèn một trang bảng tính mới
28. Ctrl + 0 Ẩn các cột hiện tại.
29. Ctrl + Shift + 0 Hiện các cột bị ẩn trong vùng đang chọn
  1. Tổ hợp phím tắt thao tác với Sheet trong Excel:
30. Ctrl + Tab, Ctrl + F6 Chuyển đổi qua lại giữa các bảng tính đang mở
31 Ctrl + Page Up Chuyển sang sheet trước
32 Ctrl + Page Down Chuyển sang sheet kế tiếp
33. Ctrl + 0 Ẩn các cột hiện tại.
34. Ctrl + Shift + 0 Hiện các cột bị ẩn trong vùng đang chọn
35. Ctrl + F2 Xem trước khi in


Đăng tải tại MS Excel | Bạn nghĩ gì về bài viết này?

Visual Studio: automatically copying files into debug folder at compile time

Put these files into the solution (explorer) and then set the files properties:

  • Build action = Content


  • Copy to Output Directory = Copy if newer (for example)

enter image description here

Đăng tải tại 4-Coding | Bạn nghĩ gì về bài viết này?

How to automatically connect to a VPN in Windows 7 on start up

If you need to connect to a Virtual Private Network at windows startup and don’t want to have the user push connect or enter the password, do the following:
1) On the VPN Connect screen, check “Save this user name and password for the following users”
2) Select the “Anyone who uses this computer” radio button
3) Click “Properties” on the VPN Connect screen
4) On the “Options” tab, uncheck:
-“Display progress while connecting”
-“Prompt for name and password, certificate,etc.”
5) In Windows Explorer, browse to C:\Documents and Settings\All Users\Start Menu\Programs\Startup
6) Open a new Windows Explorer and browse to Control Panel\Network Connections
7) Drag the icon of your VPN connection to the Startup folder. This will create a shortcut

Now at Windows startup, the VPN will launch and connect silently.

Đăng tải tại Thủ thuật | Thẻ | Bạn nghĩ gì về bài viết này?

How to Find Duplicates in Excel

1. Select the range A1:C10.

Find Duplicates in Excel

2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Duplicate Values…

Click Highlight Cells Rules, Duplicate Values

3. Select a formatting style and click OK.

Select a Formatting Style

Result. Excel highlights the duplicate names.


Note: select Unique from the first drop-down list to highlight the unique names.

As you can see, Excel highlights duplicates (Juliet, Delta), triplicates (Sierra), quadruplicates (if we have any), etc. Execute the following steps to highlight triplicates only.

4. First, clear the previous conditional formatting rule.

5. Select the range A1:C10.

6. On the Home tab, click Conditional Formatting, New Rule…

New Rule

7. Select “Use a formula to determine which cells to format”.

8. Enter the formula =COUNTIF($A$1:$C$10,A1)=3

9. Select a formatting style and click OK.

New Formatting Rule

Result. Excel highlights the triplicate names.


Explanation: =COUNTIF($A$1:$C$10,A1) counts the number of names in the range A1:C10 that are equal to the name in cell A1. If COUNTIF($A$1:$C$10,A1) = 3, Excel formats the cell. Because we selected the range A1:C10 before we clicked on Conditional Formatting, Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =COUNTIF($A$1:$C$10,A2)=3, cell A3 =COUNTIF($A$1:$C$10,A3)=3, etc. Notice how we created an absolute reference ($A$1:$C$10) to fix this reference.

Note: you can use any formula you like. For example, use this formula =COUNTIF($A$1:$C$10,A1)>3 to highlight the names that occur more than 3 times.

Đăng tải tại MS Excel | Bạn nghĩ gì về bài viết này?

How to enable ping response in windows 7

Control Panel –> System and security –> Windows Firewall –> Advanced settings –> Inbound rules –> New rule –> custom rule

in Protocol and ports: Protocol: ICMPv4
on the same panel go to customize, choose “Specific ICMP types”, check the box “echo request”

The rest is trivial; go to next… next… and save it.

Đăng tải tại Thủ thuật | Bạn nghĩ gì về bài viết này?

Configure a Windows Firewall for Database Engine Access

The principal steps to allow access are:

  1. Configure the Database Engine to use a specific TCP/IP port. The default instance of the Database Engine uses port 1433, but that can be changed. The port used by the Database Engine is listed in the SQL Server error log. Instances of SQL Server Express, SQL Server Compact, and named instances of the Database Engine use dynamic ports. To configure these instances to use a specific port, see Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager).
  2. Configure the firewall to allow access to that port for authorized users or computers.

Applies to Windows Vista, Windows 7, and Windows Server 2008

The following procedures configure the Windows Firewall by using the Windows Firewall with Advanced Security Microsoft Management Console (MMC) snap-in. The Windows Firewall with Advanced Security only configures the current profile. For more information about the Windows Firewall with Advanced Security, see Configure the Windows Firewall to Allow SQL Server Access

Method 1: To open a port in the Windows firewall for TCP access

  1. On the Start menu, click Run, type WF.msc, and then click OK.
  2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
  3. In the Rule Type dialog box, select Port, and then click Next.
  4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.
  5. In the Action dialog box, select Allow the connection, and then click Next.
  6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
  7. In the Name dialog box, type a name and description for this rule, and then click Finish.

Method 2: To open access to SQL Server when using dynamic ports

  1. On the Start menu, click Run, type WF.msc, and then click OK.
  2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
  3. In the Rule Type dialog box, select Program, and then click Next.
  4. In the Program dialog box, select This program path. Click Browse, and navigate to the instance of SQL Server that you want to access through the firewall, and then click Open. By default, SQL Server is at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe. Click Next.
  5. In the Action dialog box, select Allow the connection, and then click Next.
  6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
  7. In the Name dialog box, type a name and description for this rule, and then click Finish.
Đăng tải tại SQL server, Thủ thuật | Bạn nghĩ gì về bài viết này?

How to DataTable export to Excel in


static public void ExportDataTable(DataTable dt, string filename)
   string attachment = "attachment; filename=" + filename + "";
   HttpContext.Current.Response.AddHeader("content-disposition", attachment);
   HttpContext.Current.Response.ContentType = "application/"; //office 2003
   string sTab = "";
   foreach (DataColumn dc in dt.Columns)
      HttpContext.Current.Response.Write(sTab + dc.ColumnName);
      sTab = "\t";
   int i;
   foreach (DataRow dr in dt.Rows)
      sTab = "";
      for (i = 0; i < dt.Columns.Count; i++)
         HttpContext.Current.Response.Write(sTab + dr[i].ToString());
         sTab = "\t";

Public Shared Sub ExportDataTable(dt As DataTable, filename As String)
	Dim attachment As String = "attachment; filename=" & filename & ""
	HttpContext.Current.Response.AddHeader("content-disposition", attachment)
	HttpContext.Current.Response.ContentType = "application/"
	'office 2003
	Dim sTab As String = ""
	For Each dc As DataColumn In dt.Columns
		HttpContext.Current.Response.Write(sTab + dc.ColumnName)
		sTab = vbTab
	Dim i As Integer
	For Each dr As DataRow In dt.Rows
		sTab = ""
		For i = 0 To dt.Columns.Count - 1
			HttpContext.Current.Response.Write(sTab & dr(i).ToString())
			sTab = vbTab
End Sub

Đoạn code sau dùng cho Encode : Unicode

   Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
   Response.ContentEncoding = System.Text.Encoding.Unicode;
   Response.ContentType = "application/ms-excel";
Đăng tải tại 4-Coding | Thẻ , , , , , , , , , , , , , , , , , , , ,

How To Set a Date Format In GridView Using ASP.NET (Using HtmlEncode Property)

The problem is that if this field is enabled, you can not pass format information to the boundfield control. That is, if you try the following code, you will not get the desired result.

<columns> <asp headertext="CreationDate" dataformatstring="{0:M-dd-yyyy}" datafield="CreationDate" :BoundField /> </columns> </asp>

You have two choices to make this work as you would expect. The first choice is to simply set HtmlEncode to false as follows:

<asp id="GridView1" runat="server" :GridView> <columns> <asp headertext="CreationDate" dataformatstring="{0:M-dd-yyyy}" datafield="CreationDate" :BoundField HtmlEncode="false" /> </columns>

The second choice is to make the column a template and simply set the format string directly in the Label or Text Fields as follows.

<asp id="GridView3" runat="server" :GridView> <columns> <asp headertext="CreationDate" :TemplateField> <edititemtemplate> <asp id="Label1" runat="server" Label.Text='<%# Eval("CreationDate", "{0:M-dd-yyyy}") %>'> </asp> </edititemtemplate> <itemtemplate> <asp id="Label1" runat="server" Label.Text='<%# Bind("CreationDate", "{0:M-dd-yyyy}") %>'>; </asp> </itemtemplate> </asp> </columns> </asp>

Below is a screen shot of what the three scenarios discussed above look like.

Output of all three scenarios

Đăng tải tại 4-Coding | Thẻ , , , , , , , , , , , , , , , , , , , ,

Thêm giá trị Null vào cột kiểu dữ liệu DateTime trong SQL Server sử dụng

1. Khai báo thư viện
using System.Data.SqlTypes;
2. Khai báo biến
System.Data.SqlTypes.SqlDateTime _date;
3. Gán giá trị Null
_date = SqlDateTime.Null;
4. Insert dùng parameter command
cmd.Parameters.AddWithValue(“@date”, _date);

Đăng tải tại 4-Coding | Thẻ , , , , , , , , , , , , , , , , , , , , , , , , , | Bạn nghĩ gì về bài viết này?

Dynamic SQL CrossTab Stored Procedure

First, here’s the code for the procedure:

create procedure CrossTab (@Select varchar(1000),

    @PivotCol varchar(100),

    @Summaries varchar(100),

    @GroupBy varchar(100),

    @OtherCols varchar(100) = Null)


set nocount on

set ansi_warnings off


declare @Vals varchar(8000);

set @Vals = ;


set @OtherCols= isNull(‘, ‘ + @OtherCols,)


create table #temp  (Pivot varchar(100))


insert into #temp

exec (‘select distinct convert(varchar(100),’ + @PivotCol + ‘) as Pivot FROM (‘ + @Select + ‘) A’)


select @Vals = @Vals + ‘, ‘ +

    replace(replace(@Summaries,‘(‘,‘(CASE WHEN ‘ + @PivotCol + ‘=”’ +

            Pivot +  ”’ THEN ‘),‘)[‘‘ END) as [‘ + Pivot )

from #Temp
order by Pivot


drop table #Temp


exec ‘select ‘ + @GroupBy + @OtherCols + @Vals +

       ‘ from (‘ + @Select + ‘) A GROUP BY ‘ + @GroupBy)


set nocount off

set ansi_warnings on

And here’s a description of the parameters and how to use it:

  • Select – This is the SQL statement or table you are querying. It can be any valid SELECT statement, with JOINS, WHERE clauses, TOP, etc — anything that you can cut and paste into query analyzer and execute.  (And, in fact, you should always do this when testing things out).
  • PivotCol – This is the column (or a valid expression) that you wish to pivot on.
  • Summaries – The summaries you wish to perform. Note that this is plural; you can summarize multiple columns if you wish.  See below for more information.
  • GroupBy – This is the list of the non-pivot columns you wish to group by and return, separated by commas.
  • OtherCols (optional) – Any other columns to return that you are not grouping by, also separated by commas. Make sure that this list does not contain references to columns that are not being summarized or grouped.

Here’s the format you should use when specifying the Summaries parameter:

SUM(Field ELSE DefaultValue)[Prefix], ….

First, list the aggregate function you wish to use, and within that function put the column you wish to summarize, and add an ELSE clause with what that column’s default value is. For example, put 0 if you wish to display all 0’s for empty columns, or NULL if you wish to display nulls.

Next, immediately following the aggregate function (with no spaces) put the prefix for the column name in brackets. This is what will be appended to the beginning of the column name, followed by the value from the pivot column. Leave as [] to just use the pivot column’s value as the field name.

Finally, you may have multiple summaries just seperate them by comma’s. Remember if you have many pivot values, you will probably end up with too many columns and/or a T-SQL statement > 8000 characters so you may get an error.


If the pivot column is “Employee”, with values of 001, 002, 003 and 004:

SUM(Hours ELSE 0)[]

returns column headings of 001, 002, 003, 004, and returns a 0 in all “empty” fields.

SUM(Hours ELSE 0)[Hours], MAX(Date ELSE Null)[MaxDate]

returns column headings of Hours001, MaxDate001, Hours002, MaxDate002, Hours003, MaxDate003, ..etc …

SUM(Amount ELSE Null)[]

returns column headings of 001,002,003,004 and returns a NULL in all “empty” fields.  Note that you can use SUM(Amount)[] as a shortcut, since ELSE NULL is redundant and is the default behavior.

SUM(Amount ELSE 0)[Amount], COUNT(Amount)[Qty]

returns column headings of Amount001, Qty001, Amount002, Qty002, … etc …

Here are some samples using either Northwind or Pubs:

exec CrossTab

‘SELECT LastName, OrderDate FROM northwind..Employees Employees INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ‘,





exec CrossTab

‘select titles.*, stores.stor_name, sales.qty, sales.stor_id from pubs..titles titles

inner join pubs..sales sales on


inner join pubs..stores stores on sales.stor_id = stores.stor_id ‘,


‘SUM(qty ELSE 0)[Qty], MAX(stor_name ELSE ””)[MaxStoreName], COUNT(1 ELSE 0)[Count]’,


‘Count(*) as TotalCount’

Đăng tải tại SQL server | Thẻ , , , , , , , , , , , , , , , , , , , , , , , | Bạn nghĩ gì về bài viết này?

Using PIVOT and UNPIVOT – SQL Server 2005 or later

The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT…CASE statements. For a complete description of the syntax for PIVOT, see FROM (Transact-SQL).

The following is annotated syntax for PIVOT.

SELECT <non-pivoted column>,

[first pivoted column] AS <column name>,

[second pivoted column] AS <column name>,

[last pivoted column] AS <column name>


(<SELECT query that produces the data>)

AS <alias for the source query>



<aggregation function>(<column being aggregated>)


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

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

… [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

The following code example produces a two-column table that has four rows.

USE AdventureWorks2008R2 ;
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;

Here is the result set.

DaysToManufacture          AverageCost

0                          5.0885

1                          223.88

2                          359.1082

4                          949.4105

No products are defined with three DaysToManufacture.

The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. A column is provided for three [3]days, even though the results are NULL.

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

Here is the result set.

Cost_Sorted_By_Production_Days    0         1         2           3       4

AverageCost                       5.0885    223.88    359.1082    NULL    949.4105

A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data. For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks2008R2 sample database to determine the number of purchase orders placed by certain employees. The following query provides this report, ordered by vendor.

USE AdventureWorks2008R2;
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

Here is a partial result set.

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5

1492        2           5           4           4           4

1494        2           5           4           5           4

1496        2           4           4           5           5

1498        2           5           4           4           4

1500        3           4           4           5           4

The results returned by this subselect statement are pivoted on the EmployeeID column.

SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;

This means that the unique values returned by the EmployeeID column themselves become fields in the final result set. Therefore, there is a column for eachEmployeeID number specified in the pivot clause: in this case employees 164, 198, 223, 231, and 233. The PurchaseOrderID column serves as the value column, against which the columns returned in the final output, which are called the grouping columns, are grouped. In this case, the grouping columns are aggregated by the COUNT function. Notice that a warning message appears that indicates that any null values appearing in the PurchaseOrderID column were not considered when computing the COUNT for each employee.

Important noteImportant
When aggregate functions are used with PIVOT, the presence of any null values in the value column are not considered when computing an aggregation.

UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. Suppose the table produced in the previous example is stored in the database as pvt, and you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, and Emp5 into row values that correspond to a particular vendor. This means that you must identify two additional columns. The column that will contain the column values that you are rotating (Emp1, Emp2,…) will be calledEmployee, and the column that will hold the values that currently reside under the columns being rotated will be called Orders. These columns correspond to thepivot_column and value_column, respectively, in the Transact-SQL definition. Here is the query.

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
--Unpivot the table.
SELECT VendorID, Employee, Orders
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;

Here is a partial result set.

VendorID   Employee   Orders

———- ———- ——

1          Emp1       4

1          Emp2       3

1          Emp3       5

1          Emp4       4

1          Emp5       4

2          Emp1       4

2          Emp2       1

2          Emp3       5

2          Emp4       5

2          Emp5       5

Notice that UNPIVOT is not the exact reverse of PIVOT. PIVOT performs an aggregation and, therefore, merges possible multiple rows into a single row in the output. UNPIVOT does not reproduce the original table-valued expression result because rows have been merged. Besides, null values in the input of UNPIVOT disappear in the output, whereas there may have been original null values in the input before the PIVOT operation.

The Sales.vSalesPersonSalesByFiscalYears view in the AdventureWorks2008R2 sample database uses PIVOT to return the total sales for each salesperson, for each fiscal year. To script the view in SQL Server Management Studio, in Object Explorer, locate the view under the Views folder for the AdventureWorks2008R2 database. Right-click the view name, and then select Script View as.

Đăng tải tại SQL server | Thẻ , , , , , , , , , , , , , , , , , , , , , ,