MSBI

Here is my table named P. Table contains information about some random people who is having different type of telephonic medium to be contacted. Here is how my table P looks like: But I need the output in following way as shown in the snippet Here we can see that Row value of Table P is mirrored as column name and Corresponding values are loaded as its row. This type output is very to analyze than original table P. Isn't right? Well To achieve this format of output, we use, PIVOT command in SQL. Here is the script and out of it in a Snippet: SELECT NAME , HOME , OFFICE , MOBILE FROM ( SELECT * FROM P ) T PIVOT ( MAX ( PHONE_NUMBER ) FOR PHONE_TYPE IN ( HOME , OFFICE , MOBILE )) OUTPUT1 ORDER BY NAME Here is how it works. The first two lines of the query is used to select what we need in the output. Next PIVOT command is used to change rows to column names where we are going to use some MultiValue func...

  Here is a example to create a stored procedure for finding number is perfect square or not. CREATE PROCEDURE SQR ( @NUM int ) --syntax for creating procedure and assigning a variable to accept some number. as begin -- Stored procedure shud start with AS BEGIN and should end with END command. DECLARE @RESULT FLOAT SET @RESULT = 0 DECLARE @DIV INT SET @DIV = 0 WHILE ( @DIV != @NUM ) BEGIN SET @RESULT = SQRT ( @NUM ) IF ( @RESULT = @DIV ) BEGIN BREAK --- BREAK COMMAND IS USED TO BREAK THE WHILE LOOP ONCE CONDITION ABOVE BECOMES TRUE. END ELSE SET @DIV = @DIV + 1 END IF ( @RESULT = @DIV ) BEGIN PRINT ( 'THE NUMBER IS SQUARE' ) END ELSE PRINT ( 'THE NUMBER IS NOT SQUARE' ) END ------------------------------------------------------------- After running the above stored Procedure, We can check the output by using EXEC SQR 81  where we can change value of 81 as we wish to do.

When I wanted to know how to remove duplicates or in more sense, If i wanted to get Unique records, I went through a post which suggested to go for Rank function to find unique records.  Well some suggested to for Row_number function. Here is a example where I worked on both the types and let you the differences. CREATE TABLE Table1 ( ID INT , employee VARCHAR ( 100 ), sal INT , dept VARCHAR ( 100 )) GO INSERT INTO Table1 SELECT 1 , 'dhinakaran' , 100 , 'finance' UNION ALL SELECT 2 , 'bharath' , 100 , 'marketing' UNION ALL SELECT 3 , 'job' , 100 , 'accounts' UNION ALL SELECT 4 , 'job' , 100 , 'markting' UNION ALL SELECT 5 , 'job' , 200 , 'infrastruture' UNION ALL SELECT 6 , 'raghu' , 300 , 'markting' UNION ALL SELECT 7 , 'raghu' , 400 , 'finance' GO SELECT employee , sal , dept FROM ...

IF... ELSE statement can be used writing T-SQL. But when It comes to SSIS, we can assign the condition using DERIVED COLUMN TRANSFORMATION to achieve this. Here I'm having a condition, That if one of column in a table is having "US", Then I need to update other column as "YES" and if its not "US", then I need to update it as "NO". So for this situation, I need to have IF ELSE statement but I'm using DERIVED COLUMN Tx for this purpose. Condition I'm quoting is as follows: COUNTRY == "US" ? "YES" : "NO" . This means, When Country column is US, then YES, else NO.. In DERIVED COLUMN Tx, we can mention column name where we need to pass this YES and NO at the place derived column name slot and if we need to replace other column also, we have a option called derived column.