Temporary Tables, CTE, Table Variable, Pivot Table

Published: 27 January 2019
on channel: LearN Share

228

LOCAL TEMPORATY TABLE, GLOBAL TEMPORATY TABLE, DYNAMIC TABLE, DERIVED TABLE, COMMON TABLE EXPRESSION (CTE), PIVOT TABLE

You can use the following scripts .

USE AdventureWorks2012
GO
-------------LOCAL TEMPORATY TABLE---------------
Create table #LocalTempTable (Co1 char(5));

insert into #LocalTempTable (Co1)
values ('A'),('B');

-------------GLOBAL TEMPORATY TABLE---------------
Create table ##GlobalTempTable (Co2 char(5));
insert into ##GlobalTempTable (Co2)
values ('C'),('D')

-------------Dynamic Table---------------

select *, col1 from
(values
('a', 'b'),
('c','d'),
('e','f')) as TableName (col1, col2)

-------------Derived Table---------------
--Get the third sales person based on sales amount for each territoryID
select * from
(select p.FirstName, p.LastName, s.SalesYTD,
DENSE_RANK () over (partition by s.territoryid order by p.firstname, p.lastname) as DenseRank
from sales.salesperson s
join person.Person p on s.BusinessEntityID = p.BusinessEntityID) Dt1
where DenseRank = 3

-------------Common Table Expresion (CTE)---------------
--Get the third sales person based on sales amount for each territoryID

with CTETable
as
(select p.FirstName, p.LastName, s.SalesYTD,
DENSE_RANK () over (partition by s.territoryid order by p.firstname, p.lastname) as DenseRank
from sales.salesperson s
join person.Person p on s.BusinessEntityID = p.BusinessEntityID)

select * from CTETable
where DenseRank = 3

-----------------------pivot--------------------------
--Find total purchase amount by each employee from each vendor for each year, MONTH AND DAY

select P2.VendorID, P2.PurchaseYear,P2.purchaseMonth, P2.PurchaseDay, P2.[250],P2.[251], P2.[252], P2.[253],P2.[259]
from
(
select EmployeeID, VendorID, TotalDue, YEAR(OrderDate) as 'PurchaseYear', SUBSTRING (FORMAT( OrderDate,'MMMM'),1,3) as 'purchaseMonth', day(orderdate) PurchaseDay
from Purchasing.PurchaseOrderHeader) P1
pivot (sum(Totaldue) for employeeid in ([250],[251],[252],[253],[259])) P2
order by P2.VendorID, P2.PurchaseYear, P2.purchaseMonth, P2.PurchaseDay

On this page of the site you can watch the video online Temporary Tables, CTE, Table Variable, Pivot Table with a duration of hours minute second in good quality, which was uploaded by the user LearN Share 27 January 2019, share the link with friends and acquaintances, this video has already been watched 228 times on youtube and it was liked by 1 viewers. Enjoy your viewing!

247