Change columns to rows and vice versa using PIVOT-UNPIVOT

PIVOT

See table below:
TbSummary


Using pivot, we can change rows to columns, so data can be displayed as below:


PIVOT Syntax:
SELECT [new column 1], [new column 2], [new column 3],…
FROM
(
SELECT * FROM [Source Table]
) AS [alias for source table]
PIVOT
(
[AGGREGATE FUNCTION]([value column]) FOR [header column]in ([new column 1], [new column 2], [new column 3],…)
)
AS [alias for pivot table]

Example of query:
SELECT yr, [1], [2], [3]
FROM
(SELECT * FROM TbSummary) AS a
PIVOT
(
SUM(score) FOR batch in ([1], [2], [3])
)
AS b



UNPIVOT

See the tables below:
TbData


In contrary with PIVOT, UNPIVOT is used to change columns to rows. So here, we’re going to display the data as below:


UNPIVOT Syntax:
SELECT [new column 1], [new column 2]
FROM
(
SELECT
[column1],
[column2],
...
FROM [Source Table]
) [alias for source table]
UNPIVOT
(
[new column 2] FOR [new column 1] IN ([column1],[column2],...)
) AS [alias for unpivot table]

Example of the query:
SELECT details, value
FROM
(
SELECT
CAST (id AS varchar) AS id,
CAST ([name] AS varchar) AS [name],
CAST(gender AS varchar) AS gender,
CONVERT(varchar,dob,106) AS dob,
CAST (phonenum AS varchar) AS phonenum
FROM TbData
) a
UNPIVOT
(
value FOR details IN (id, [name], gender, dob, phonenum)
) AS b

Dong Yi

Switch User Option Disappear from Windows?

As we know “Switch User” option in Windows allows user to log off from current user and log on to a new user.
How if the option disappear from your Windows?

No worry, here's how to make it appears:
1. Go to [Start] > [Control Panel] > [User Accounts]
2. Select "Change the way users log on or off"
3. And you might receive a prompt like this: "Fast User Switching cannot be used because Offline Files is currently enabled. To make changes to Offline Files, click OK."
4. If that so, just click OK, so that "The Offline Files Settings" dialog box appears, select "clear the Enable Offline Files" check box, and then click OK
5. Back to step 2 to select the "Use Fast User Switching" check box, and then click Apply Options
6. Now the "Switch User" option is available on your Windows

Delete Account Information in Web Browser

Did you experience to log-in in a web application while accidentally type password in "account/user name" text box,
and then a pop up box showing you account with password appears as one of the option for subsequent log in?
And then you still unable to remove the pop up after you try to delete cookies and browsing history?

If that so, you should try this one:
1. Open the web browser (e.g. Mozilla, IE)
2. Press [ctrl]+[shift]+[delete]
3. A dialog box will be prompted
4. Select "Form & Search History" check box
5. Click [Clear Now] button

Note: all log-in account will be deleted, if you're logged in any account (e.g. email or any web application), you'll need to relog-in

XXB

USING ROW_NUMBER FUNCTION IN SQL QUERY

ROW_NUMBER is used to query the row number. It's very useful when need to get the sequence number of an ordered query.
Here's the example of query to use it.

Table 'TClass'




Give row number from the most to least Num_Attendee:

Query:
SELECT ROW_NUMBER() OVER (ORDER BY Num_Attendee desc) AS Row_Num, * FROM TClass

Result:




Give row number for each Class from the most to least Num_Attendee in a class:

Query:
SELECT ROW_NUMBER() OVER (PARTITION BY CLASS ORDER BY Num_Attendee desc) AS Row_Num, * FROM TClass

Result:




Get the most Num_attendee of each class:

Query:
SELECT Class, Num_attendee FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CLASS ORDER BY Num_Attendee desc) AS Row_Num, * FROM TClass
) x
WHERE x.Row_Num=1

Result:

How to Remove All Hyperlinks in Word or Excel

Here's the step by step how to remove all hyperlinks in your excel or Word file:

1. Open the word/ excel file
2. Hit [Alt]+[F11] to open the Visual Basic Editor
3. Go to menu [Insert]>[Module]
4. Copy and paste the code below:

Ms. Word:
Sub RemoveHyperlinks()
Dim oField As Field
For Each oField In ActiveDocument.Fields
If oField.Type = wdFieldHyperlink Then
oField.Unlink
End If
Next
Set oField = Nothing
End Sub

Ms. Excel:
Sub RemoveHyperlinks()
'Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete
End Sub

5. And then click [File]>[Close]
6. On Word/Excel file go to [View]>[Macros]>[View Macros]
7. Run “RemoveAllHyperlinks”
8. Now all hyperlinks are removed successfully

Note: I'm using Office 2007