IF OBJECT_ID('ORDERS') IS NOT NULL
DROP TABLE ORDERS
GO
CREATE TABLE ORDERS
(
ORDERID INT NOT NULL,
ORDERDATE DATE NOT NULL,
EMPID INT NOT NULL,
CUSID VARCHAR(5) NOT NULL,
QTY INT NOT NULL,
CONSTRAINT PK_ORDER PRIMARY KEY(ORDERID)
)
INSERT INTO ORDERS(ORDERID,ORDERDATE,EMPID,CUSID,QTY )
SELECT 30001,'20070802',3,'A',10
UNION ALL SELECT 10001,'20071224',2,'A',12
UNION ALL SELECT 10005,'20071224',1,'B',20
UNION ALL SELECT 40001,'20080109',2,'A',40
UNION ALL SELECT 10006,'20080118',1,'C',14
UNION ALL SELECT 20001,'20080212',2,'B',12
UNION ALL SELECT 40005,'20080212',3,'A',10
UNION ALL SELECT 20002,'20080216',1,'C',20
UNION ALL SELECT 20003,'20090418',2,'B',15
UNION ALL SELECT 30004,'20070418',3,'C',22
UNION ALL SELECT 30007,'20090907',3,'D',30
GO
--PIVOT
SELECT EMPID,[A],[B],[C],[D] INTO TEMPORDER
FROM
(
SELECT EMPID,QTY,CUSID FROM ORDERS
) AS O
PIVOT
(
SUM(QTY) FOR CUSID IN([A],[B],[C],[D])
) AS p
--UNPIVOT
SELECT EMPID,QTY,CUSID
FROM TEMPORDER
UNPIVOT
(
QTY FOR CUSID IN(A,B,C,D)
) AS O