SQL Rename column on table

Using sp_RENAME in SQL we can rename table name, and column name on the fly:

     USE AdventureWorks
     GO
     sp_RENAME
     'Person.Address.AddressLine1', 'Addresslin01', 'COLUMN'
     GO

The above statement using a sample table to rename a column from 'AddressLine1' to 'Addresslin01'

SQL COALESCE: Whichever not null.

Sometimes we need to retrieve information from multiple columns from a table whichever is not null. In such situation COALESCE can be used in SQL query

I picked a sample table ProductListPriceHistory. It shows each product's ID and its list price. Some of them have both StartDate and EndDate. Some of them have either one only.  

Assume those date indicated as the availability of each product, I want to retrieve the information based on the following conditions:
1.) if both StartDate and EndDate are not NULL, retrieve the first column (StartDate)
2.) if either StartDate or EndDate is NULL, retrieve whichever column is NOT NULL

Therefor I wrote a query like this:

     USE AdventureWorks2008
     GO

     SELECT ProductID,COALESCE(StartDate,EndDate)TodayPrice, ListPrice
     FROM
     Production.ProductListPriceHistory

After execute the query, it retrieve the value from StartDate and EndDate whichever is not null and input it into TodayPrice column.

SQL: Combine CASE WHEN with AND

CASE WHEN expression can combine with AND, OR..
In the following example, lets assume a condition that we need to apply a integer numeric value in three specific colors of the product AND their list price are more than 5 dollars. Using CASE WHEN on color and AND list price >5 which results only products are black, silver, red and higher than 5 dollars list price its color data value will convert as 1. 

USE AdventureWorks
GO
SELECT
Name,
ListPrice,
CASE WHEN ( Color IN ('BLACK', 'SILVER', 'RED')
        AND (ListPrice > 5))
    THEN 1 ELSE 0 END as colorcode
    FROM Production.Product

SQL: Convert data in multiple conditions using CASE WHEN..THEN

From this example above, the original table has a column 'Color' which store the color specification of the product line.

Let's assume now when developing a ETL, this column need to be convert to 'colorcode' to display the color description as number.
We could use CASE WHEN.. THEN in SQL to convert the table for reporting.
In this situation, using CASE WHEN..THEN is better than just IF..THEN because CASE WHEN could easily setup multiple conditions.
In this example we have to place a color code for black, silver, and red in our product table:

 
USE AdventureWorks2008

GO

SELECT Name,
CASE WHEN ([COLOR]='BLACK') THEN 1
WHEN ([COLOR]='SILVER') THEN 2
WHEN ([COLOR]='RED') THEN 3
ELSE 0 END AS COLORCODE

FROM

Production.Product

After we run the query, notice that the column 'colorcode' has converted color name into integer numbers:

 

SQL: Converting Data Type

Sometime data type needs to be changed for querying or BI reporting use. In situations like this the CONVERT clause comes in handy.

For example, I need to grep the data of Date for reporting. However the data type of date in the data warehouse are Date time which has date and time stamp.

I don't need the time stamp part of it. I want to turn my date key from datetime to just date, so I place a CONVERT clause within my SELECT statement. 



SELECT
CONVERT (DATE, FullDateAlternateKey) AS FullDate
FROM AdventureWorksDW.dbo.DimTime

After the query is executed, notice the difference of data type between the original table and the query output: 

MSDN has a full list of data type which CONVERT can handle. Please check out this link:

http://msdn.microsoft.com/en-us/library/ms187928.aspx