Pivoting Data using T-SQL and SSIS Pivot Transformation

Pivot Data Using T-SQL

Microsoft has introduced the Pivot and Unpivot relational operators in T-SQL with the release of MS SQL Server 2005. The Pivot operator is used to transform and rotate data from a single column in to multiple columns (row to columns) in SQL.

The following are the queries used to create a sample table, insert some sample records and at last the Pivot query to transform the data from row to columns.

-- Create a sample table
CREATE TABLE [Pivot_Test](
    [Customer] [nchar](10) NOT NULL,
    [Product] [nchar](10) NOT NULL,
    [Quantity] [smallint] NOT NULL
)
-- Insert some sample records
INSERT INTO [Pivot_Test]
    ([Customer]
    ,[Product]
    ,[Quantity])
VALUES
    ('Cust A', 'Pepsi', 1),
    ('Cust B', 'Coke', 1),
    ('Cust C', 'Cake', 2),
    ('Cust A', 'Burger', 2),
    ('Cust B', 'Burger', 1),
    ('Cust C', 'Burger', 2),
    ('Cust C', 'Coke', 1)

The records have been loaded into the table. The screenshot is as below,

Before creating the query, user needs to know about the columns that have to be pivoted.

-- SQL query to pivot the data in the sample table    
SELECT  [Customer],
        [Pepsi],
        [Coke],
        [Cake],
        [Burger]
FROM    (SELECT [Customer],
                [Product],
                [Quantity]
        FROM    [R_n_D].[dbo].[Pivot_Test]) Src
PIVOT    (SUM([Quantity]) FOR [Product] IN([Pepsi],[Coke],[Cake],[Burger])) Pvt
ORDER BY [Customer]

Output for the above query,

Pivot Data Using SSIS Pivot Transformation

The above said Pivot action can be done also by using SSIS Pivot Transformation. One has to pay little bit of attention in configuring the Pivot Transformation.  The followings are the step by step to perform Pivot against data using the Transformation.

Step 1:
Create a simple package having OLE DB data source, Pivot Transformation and Excel Destination. As per the SSIS best practice use SQL query with the necessary fields in it. Don’t forget to have Order by clause in the SQL query. When I came across some of the blogs, they have advised to use the built-in sort function to those necessary field in the OLEDB data source by using the advance editor option (Input and Output Properties > OLE DB Source Output: IsSorted – True and mark 1,2.. as per the order of sort to the fields in the Output Column). Always prefer to use Order by clause while using Pivot transformation.

 

Step 2:
Open the Pivot Transformation and go to Input Columns tab; since we select the necessary fields in OLE DB datasource, select all the columns in the Input column tab. Go to Input and Output Properties tab, here we are going to configure how each column is being used in the Pivot operation.

Go to Pivot Default Input > Input Columns and select the relevant Input Column and set the PivotUsage property which is found under the custom header.

The following are the values that can be set in the PivotUsage property,

• 0 – column that is just passed through without any changes
• 1 – column that is a set key.  All input rows with the same set key are combined into one output row.
• 2 – column to pivot.
• 3 – values from these columns are placed in pivot columns.

Set 1 – Customer,

Set 2 – Products,

Set 3 – Quantity.

Step 3:
Initially there won’t be any output columns under the Pivot Default Output > Output Columns. Manually we have to create the output columns. Create an output column for Customer; change the source column property to match the LineageID from the input Customer column.

For the Product values, but here we need to enter the value of the product in the PivotKeyValue property. The product LineageID has to be updated in the Source column property. Repeat the same for the other products.

Step 4:
Create Excel destination and run the package.

The data is pivoted now

Basics: Changing the name of an existing Universe

The following is the procedure to change the name of an existing universe,

1. It is advisable to take a copy of the original version of the Universe which name needs to be changed.

2. Open the original version;

a. Rename the connection name.

b. Rename the Universe name by using the save as option.

 c. Export the Universe.

3. Open the reports in the Infoview which are all dependent on the original version of the Universe and map the objects in the report to the duplicate/ copied version.

a. Click Edit Query button,

b. Click Query Properties button,

 c. Select the Universe,

 d. Map the objects.

4. Check the reports against the duplicate/ copied version of Universe by refreshing it.

5. Delete the original version of the report.

The report is now using the Universe which has been renamed.

Note:
If the above said procedure is not followed properly and somebody changes the Universe name directly, then the report will search for the Universe at time of the refreshing phase. The whole report has to be created from the scratch.

Good things to lead a good life

Health

1. Drink plenty of water.
2. Eat breakfast like a king, lunch like a prince and dinner like a beggar.
3. Eat more foods that grow on trees and plants, and eat less food that is manufactures in plants (factory).
4. Live with 3 E’s – Energy, Enthusiasm and Empathy
5. Make time for prayers.
6. Play more games.
7. Read more books.
8. Sit in silence for at least 10 minutes each day.
9. Sleep for 7 hours.
10. Take a 10 -30 minutes’ walk every day – and while you walk, SMILE!!

Personality

1. Don’t compare your life to others’. You have no idea what their journey is all about.
2. Don’t have negative thoughts or things you cannot control. Instead invest your energy in the positive present moment.
3. Don’t overdo; Keep your limits.
4. Don’t take yourself seriously; no one else does.
5. Don’t waste your precious energy on gossip.
6. Dream more while you are awake.
7. Envy is waste of time. You already have all you need.
8. Forget issues of the past. Don’t remind your partner with his/her mistakes of the past. That will ruin your present happiness.
9. Life is too short to waste time hating anyone. Don’t hate others.
10. Make peace with your past so it won’t spoil the present.
11. No one is in charge of your happiness except you.
12. Realize that life is a school and you are here to learn. Problems are simply part of the curriculum that appear and fade away like algebra class but the lessons you learn will last a lifetime.
13. Smile and Laugh more often.
14. You don’t have to win every argument. Agree to disagree.

Community

1. Call your family often.
2. Each day give something good to others.
3. Forgive everyone for everything.
4. Spend time with people over the age of 70 and under the age of 6.
5. Try to make at least three people smile each day.
6. What other people think of you is none of your business.
7. Your job won’t take care of you when you are sick. Your family and friends will. Stay in touch.

Life

1. Do the right things.
2. Get rid of anything that isn’t useful, beautiful or joyful.
3. God heals everything.
4. However god or bad a situation is, it will change.
5. No matter how you feel; Get up, Dress up and Show up.
6. The best is yet to come.
7. When you awake alive in the morning, thank GOD for it.
8. Your inner most is always happy; so be Happy!