Pivot Queries in Oracle and SQL Server – Table Script

Use the following script to create and populate the table for the Pivot Queries in Oracle and SQL Server article.

If you’re using Oracle, uncomment the COMMIT on the last line.

CREATE TABLE TestPiv (
  ProductType VARCHAR(20),
  ProductColor VARCHAR(10),
  Region VARCHAR(20),
  Units DECIMAL(7),
  TotalSales DECIMAL(10,2));

INSERT INTO TestPiv VALUES ('Widget', 'Blue' , 'North',  42, 503.19);
INSERT INTO TestPiv VALUES ('Widget', 'Blue' , 'South',  26, 273.88);
INSERT INTO TestPiv VALUES ('Widget', 'Blue' , 'East' ,  33, 402.23);
INSERT INTO TestPiv VALUES ('Widget', 'Blue' , 'West' ,  41, 499.65);
INSERT INTO TestPiv VALUES ('Widget', 'Red'  , 'North',  67, 708.42);
INSERT INTO TestPiv VALUES ('Widget', 'Red'  , 'South',  12, 115.61);
INSERT INTO TestPiv VALUES ('Widget', 'Red'  , 'East' ,  37, 430.54);
INSERT INTO TestPiv VALUES ('Widget', 'Red'  , 'West' ,   9, 107.66);

INSERT INTO TestPiv VALUES ('Block' , 'Blue' , 'North', 122, 886.44);
INSERT INTO TestPiv VALUES ('Block' , 'Blue' , 'South', 107, 749.14);
INSERT INTO TestPiv VALUES ('Block' , 'Blue' , 'East' , 119, 853.97);
INSERT INTO TestPiv VALUES ('Block' , 'Blue' , 'West' , 113, 798.65);
INSERT INTO TestPiv VALUES ('Block' , 'Red'  , 'North', 103, 802.42);
INSERT INTO TestPiv VALUES ('Block' , 'Red'  , 'South', 126, 859.24);
INSERT INTO TestPiv VALUES ('Block' , 'Red'  , 'East' , 115, 837.61);
INSERT INTO TestPiv VALUES ('Block' , 'Red'  , 'West' , 136, 824.56);

INSERT INTO TestPiv VALUES ('Glove' , 'Blue' , 'North',  87, 285.46);
INSERT INTO TestPiv VALUES ('Glove' , 'Blue' , 'South',  96, 316.21);
INSERT INTO TestPiv VALUES ('Glove' , 'Blue' , 'East' ,  48, 152.72);
INSERT INTO TestPiv VALUES ('Glove' , 'Blue' , 'West' ,  72, 219.47);
INSERT INTO TestPiv VALUES ('Glove' , 'Red'  , 'North',  77, 185.48);
INSERT INTO TestPiv VALUES ('Glove' , 'Red'  , 'South',  86, 216.23);
INSERT INTO TestPiv VALUES ('Glove' , 'Red'  , 'East' ,  38, 100.01);
INSERT INTO TestPiv VALUES ('Glove' , 'Red'  , 'West' ,  62, 119.71);

-- Uncomment this line if using Oracle
-- COMMIT;

Leave a Reply

Your email address will not be published. Required fields are marked *