PROD Script

SET IDENTITY_INSERT [dbo].[XYZ] ON

IF NOT EXISTS(SELECT 1 FROM [dbo].[XYZ] WHERE [Id] =28)

INSERT [dbo].[XYZ] ([Id], [first_Name],[last_Name] ) VALUES (28, 'CUSTOM','CUSTOM')

SET IDENTITY_INSERT [dbo].[XYZ] OFF

 

 

 

SET IDENTITY_INSERT [dbo].[ABC] ON

IF NOT EXISTS(SELECT 1 FROM [dbo].[ABCC] WHERE [Id] =274)

INSERT [dbo].[TableName] ([Id], [Id],[Vendor],[Name],[Short] ) VALUES (274,28, 'CUSTOM','CUSTOM','CUSTOM')

SET IDENTITY_INSERT [dbo].[ABC] OFF

 

 

UPDATE ZZZ_table

SET [Id]=274

WHERE code like 'CUST%'




-----------------------------------------------------------------------


**************
UPDATE SCRIPT:
**************




IF(@MachineName = 'LA-SONY-1') --Server Name





BEGIN


IF (SELECT COUNT(*) FROM [SSISConfigurations]

WHERE [ConfigurationFilter] = 'SSISPackageName'

AND [PackagePath] = '\Package.Variables[User::EmailSubject].Properties[Value]') = 0

BEGIN

INSERT [dbo].[SSISConfigurations]


([ConfigurationFilter],

[ConfiguredValue],

[PackagePath],

[ConfiguredValueType])

VALUES


(N'SSISPackageName',

N'DEV - New Data file has been loaded',

N'\Package.Variables[User::EmailSubject].Properties[Value]',

N'String')


END


ELSE


BEGIN


IF NOT EXISTS(SELECT ConfiguredValue from SSISConfigurations

WHERE [ConfigurationFilter] = 'SSISPackageName'

AND [PackagePath] = '\Package.Variables[User::EmailSubject].Properties[Value]'

AND ConfiguredValue = 'DEV - New Data file has been loaded')


BEGIN


UPDATE [dbo].[SSISConfigurations]

SET [ConfiguredValue] = N'DEV - New Data file has been loaded'

WHERE [ConfigurationFilter] = 'SSISPackageName'

AND [PackagePath] = '\Package.Variables[User::EmailSubject].Properties[Value]'

END


END




END

My PHOTO's @ NOVOTEL

 

 

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