Pivot and Unpivot, Sql Server

Pivot and Unpivot

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);

INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);

select * from pvt    
    
--Unpivot the table.

create table #temptable (VendorID int not null,Employee varchar(10) not null, Orders int not null )

insert into #temptable
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;

select * from #temptable

--Pivot the table
select *
from 
(
  select VendorID, Employee ,Orders
  from #temptable
) src
pivot
(
  sum(Orders)
  for Employee in (Emp1, Emp2, Emp3, Emp4,Emp5)
) piv
ORDER BY piv.VendorID

/*
select eomonth(dateadd(mm,1,getdate()))

declare @var1 int
set @var1= 1

select format(@var1,'d3')

SELECT DATEFROMPARTS(2015, 12, 31)

relicate
replace
stuff
coalesce
isnull
choose
eomonth
format
DATEFROMPARTS
on
rollup
union
union all
except 
intersect 
in 
not in
exists
not exists
rollup
pivot
unpivot

Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

AVG() - Returns the average value
    COUNT() - Returns the number of rows
    FIRST() - Returns the first value
    LAST() - Returns the last value
    MAX() - Returns the largest value
    MIN() - Returns the smallest value
    SUM() - Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

UCASE() - Converts a field to upper case
    LCASE() - Converts a field to lower case
    MID() - Extract characters from a text field
    LEN() - Returns the length of a text field
    ROUND() - Rounds a numeric field to the number of decimals specified
    NOW() - Returns the current system date and time
    FORMAT() - Formats how a field is to be displayed


run \| edit \| history \| help	0