Pivot and Unpivot Table

PIVOT AND UNPIVOT TABLE

SAMPLE SELECT SQL- USING PIVOT AND UNPIVOT TABLE

PIVOT AND UNPIVOT TABLE
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements. For a complete description of the syntax for PIVOT, see FROM (Transact-SQL).

SAMPLE PIVOT TABLE:

create table #TabPivot
(
    Id int,
    Nome varchar(50),
    Categoria varchar(50)
)

insert into #TabPivot
select 1, 'João', 'A' union all
select 2, 'João', 'B' union all
select 3, 'João', 'D' union all
select 4, 'Maria', 'A' union all
select 5, 'Maria', 'C' union all
select 6, 'Maria', 'D'

-- USING PIVOT
select nome,
coalesce(A, '') CatA,
coalesce(B, '') CatB,
coalesce(C, '') CatC,
coalesce(C, '') CatD
from
(
select nome, Categoria, 'X' flag
from #TabPivot
) d
pivot
(
max(flag)
for Categoria in (A, B, C, D)
) piv

Sample 01 - PIVOT TABLE

Name	CatA	CatB	CatC	CatD
João	X	X
Maria	X		X	X

Sample 02 - UNPIVOT TABLE:

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);

/* Unpivot the table. */
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;

VendorID	Employee	Orders
1	Emp1	4
1	Emp2	3
1	Emp3	5
1	Emp4	4
1	Emp5	4
2	Emp1	4
2	Emp2	1
2	Emp3	5
2	Emp4	5
2	Emp5	5
3	Emp1	4
3	Emp2	3
3	Emp3	5
3	Emp4	4
3	Emp5	4
4	Emp1	4
4	Emp2	2
4	Emp3	5
4	Emp4	5
4	Emp5	4
5	Emp1	5
5	Emp2	1
5	Emp3	5
5	Emp4	5
5	Emp5	5