Pivot

USE Sample


GO

-- Creating Test Table

CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)



GO

-- Inserting Data into Table

INSERT INTO Product(Cust, Product, QTY)

VALUES('KATE','VEG',2)

INSERT INTO Product(Cust, Product, QTY)

VALUES('KATE','SODA',6)

INSERT INTO Product(Cust, Product, QTY)

VALUES('KATE','MILK',1)

INSERT INTO Product(Cust, Product, QTY)

VALUES('KATE','BEER',12)

INSERT INTO Product(Cust, Product, QTY)

VALUES('FRED','MILK',3)

INSERT INTO Product(Cust, Product, QTY)

VALUES('FRED','BEER',24)

INSERT INTO Product(Cust, Product, QTY)

VALUES('KATE','VEG',3)



GO

-- Selecting and checking entires in table

SELECT *

FROM Product

GO





-- Pivot Table ordered by PRODUCT

SELECT PRODUCT, FRED, KATE

FROM (

SELECT CUST, PRODUCT, QTY

FROM Product) up

PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt

ORDER BY PRODUCT

GO





-- Pivot Table ordered by CUST

SELECT CUST, VEG, SODA, MILK, BEER, CHIPS

FROM (

SELECT CUST, PRODUCT, QTY

FROM Product) up

PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt

ORDER BY CUST

GO



-- Unpivot Table ordered by CUST

SELECT CUST, PRODUCT, QTY



FROM

(

SELECT CUST, VEG, SODA, MILK, BEER, CHIPS

FROM (

SELECT CUST, PRODUCT, QTY

FROM Product) up

PIVOT

( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p

UNPIVOT

(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)

) AS Unpvt

GO



-- Clean up database

DROP TABLE Product

GO

Pivot tables in SQL Server. (A simple sample.)

Pivot tables in SQL Server. (A simple sample.)



Create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)

insert into DailyIncome values ('SPIKE', 'FRI', 100)

insert into DailyIncome values ('SPIKE', 'MON', 300)

insert into DailyIncome values ('FREDS', 'SUN', 400)

insert into DailyIncome values ('SPIKE', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'TUE', 200)

insert into DailyIncome values ('JOHNS', 'WED', 900)

insert into DailyIncome values ('SPIKE', 'FRI', 100)

insert into DailyIncome values ('JOHNS', 'MON', 300)

insert into DailyIncome values ('SPIKE', 'SUN', 400)

insert into DailyIncome values ('JOHNS', 'FRI', 300)

insert into DailyIncome values ('FREDS', 'TUE', 500)

insert into DailyIncome values ('FREDS', 'TUE', 200)

insert into DailyIncome values ('SPIKE', 'MON', 900)

insert into DailyIncome values ('FREDS', 'FRI', 900)

insert into DailyIncome values ('FREDS', 'MON', 500)

insert into DailyIncome values ('JOHNS', 'SUN', 600)

insert into DailyIncome values ('SPIKE', 'FRI', 300)

insert into DailyIncome values ('SPIKE', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'FRI', 300)

insert into DailyIncome values ('JOHNS', 'THU', 800)

insert into DailyIncome values ('JOHNS', 'SAT', 800)

insert into DailyIncome values ('SPIKE', 'TUE', 100)

insert into DailyIncome values ('SPIKE', 'THU', 300)

insert into DailyIncome values ('FREDS', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'SAT', 100)

insert into DailyIncome values ('FREDS', 'SAT', 500)

insert into DailyIncome values ('FREDS', 'THU', 800)

insert into DailyIncome values ('JOHNS', 'TUE', 600)

To find the average for each vendor, run this query:

 

select * from DailyIncome

pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay

 

 

 

 

The short story on how it works using the last query:

 

select * from DailyIncome                                 -- Colums to pivot

pivot (

max (IncomeAmount)                                            -- Pivot on this column

for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) -- Make colum where IncomeDay is in one of these.

as MaxIncomePerDay                                            -- Pivot table alias

where VendorId in ('SPIKE')                               -- Select only for this vendor

 

 

 

 

 

Generate a Parameter list for all SP and Functions

Generate a parameter list for all SQL Server Stored procedures and functions


USE AdventureWorks;
GO
SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema],
SO.name AS [ObjectName],
SO.Type_Desc AS [ObjectType (UDF/SP)],
P.parameter_id AS [ParameterID],
P.name AS [ParameterName],
TYPE_NAME(P.user_type_id) AS [ParameterDataType],
P.max_length AS [ParameterMaxBytes],
P.is_output AS [IsOutPutParameter]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P
ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE TYPE IN ('P','FN'))
ORDER BY [Schema], SO.name, P.parameter_id
GO




To know the column name with in the Database

SELECT table_name,column_name
from information_schema.columns
where column_name like '%Satish%'


Excel formual:
=A1 & "AS" & B1 & ","



 

Create dynamic connection string with variables SSIS :

Create dynamic connection string with variables SSIS :

Create Parameter on package level with string datatype with following Name, set default value with respect to your machine configuration I set according to mine

VServerName = ”SATISH”

VSQLDbName = ”TESTDB”
VSQLUserName= ”SAT123”

VSQLPassword = ”123”

(1) If integrated security with database is False or you connect with Windows authentication following is the expression you have to set  expression at connection string property.

"Data Source=" + @[User::VServerName]  + ";Initial Catalog=" + @[User:: VSQLDbName]   + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;"

(2) If you want to connect with database with  SQL Server authentication you have to use following expression string at  expression of connection string at connection in SSIS.

"Data Source="+ @[User::VServerName] +";User ID="+ @[User::VSQLUserName]  +"Password= "+ @[User::VSQLPassword] +" ; Initial Catalog=" + @[User::VSQLDbName] + ";Provider=SQLNCLI10.1;Persist Security Info=True;"

Setting SSIS Package Configuration with SQL Server Configuration :

SSIS is completely replacement of DTS and it becomes first class SQL Server components along with database engine, analysis services, and reporting services. It is a good practice if you keep configuration value not in SSIS but outside SSIS packages.

 

Those configuration values could be saved on XML configuration file, environment variable, windows registry, parent package variable or SQL Server. The use of XML configuration file is not uncommon now but it is less secure compared to save it into windows registry or environment variable.

 

But it is rigid implementation if the option is to use windows registry or environment variable. The balance between security and flexibility is to save it into SQL Server. This posting will guide you how to implement package configuration into SQL Server.

 

1. Open your visual studio. Choose File menu > New > Project.
2. In New Project dialog box, look at Project Types left pane, and choose item : Business Intelligence Projects.
3. In Templates list box, choose Integration Services Project.
4. In Name text box, fill out your SSIS project name and click OK button.
5. Now you focus on Package.dtsx or if it is not opened yet, you double click on it at Solution Explorer window pane.
6. Create package level variable, so go to SSIS menu > Variables.
7. In Variables window pane, click New toolbar to create new SSIS variable. You can edit the variable name for example I name it myVar. Then change the variable data type to String. The variable value I would bind it with SSIS configuration with SQL Server type repository.
8. Click blank area in the SSIS package or Control Flow designer window.
9. Go to SSIS menu > Package Configurations…
10. In Package Configuration Organizer window, tick check box “Enable package configurations”
11. Click Add button
12. In Package Configuration Wizard, click Next button
13. You will be presented with Select Configuration Type section, choose SQL Server in Configuration Type combo box.
14. Make sure you choose “Specify Configuration settings directly”, click New button beside Connection combo box.
15. In Configure OLE DB Connection Manager window, choose New button.
16. Type your database server name, database name, and click “Test Connection” button to test whether you can connect to database server. If test is successful, click OK button to close Connection Manager window and go back to Configure OLE DB Connection Manager. And click OK to go back to Package Configuration Wizard window.
17. Click New button beside Configuration Table combo box. And click OK button. The wizard will create SSIS configuration table.
18. Type your Configuration Filter, for example Configuration.VariableA and click Next button.
19. In “Select Properties to Export” section, from Objects list box, go to the variable that is created on step 7, expand its properties and tick Value property.
20. Click Next button to go to “Completing the Wizard” section, name your configuration name, for example MyConfiguration and click Finish button to go back to Package Configurations Organizer window.
21. click Close button.
22. by default myVar variable value is still empty, you need to fill it from “SSIS Configuration” table, so open SSMS (SQL Server Management Studio) and open the table.
23. After you open “SSIS Configuration” table, go to Configuration.VariableA and type any value in ConfiguredValue column then commit the change.
24. go back to BIDS, still you find the SSIS variable value is empty, you need to refresh it by close the package designer window and reopen it.
25. Now you will find the variable value is filled with value from SQL Server configuration repository.