Creating a pivot table

Transact-SQL
1

Here is my basic query.
SELECT
e.name , p.category, sum(p.price*od.quantity) as Sales

FROM orders o
INNER JOIN orderdetails od ON o.ID = od.ORDERID
INNER JOIN products p ON p.id = od.PRODUCTID
INNER JOIN employees e on e.id=o.id
GROUP BY p.category, e.name
ORDER BY p.category, e.name

I am trying to do a pivot on it, to have the category's (there are three different ones) across the top with the e.name and a total of sales for that category on each line. I have been trying a few different ways, but none of them have worked.

2

Please post create table scripts, test data (in the form of an insert statement & what the desired output is, based on the test data.

3

Here are the scripts that I have created to make the tables and add the data.

CREATE TABLE EMPLOYEES
( ID INT NOT NULL,
SSN DECIMAL(9,0) NOT NULL,
BIRTHDATE DATE NOT NULL,
DEPENDANTS TINYINT NOT NULL,
MARRIED BIT NOT NULL,
FULL_PART_TIME BIT NOT NULL,
HIRE_DATE DATE NOT NULL,
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP TINYINT NOT NULL,
ZIP4 TINYINT,
COMPLETED_TRAINING BIT DEFAULT 0,
Name VARCHAR(30) NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT AK_SSN UNIQUE(SSN));

CREATE TABLE SUPPLIERS
( ID INT NOT NULL,
NAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP TINYINT NOT NULL,
ZIP4 TINYINT,
EMAIL VARCHAR(50) NOT NULL,
PHONENUMBER CHAR(12) NOT NULL,
CONTACT VARCHAR(30),
TERMS VARCHAR(MAX),
NOTES VARCHAR(MAX),
PRIMARY KEY (ID));

CREATE TABLE PRODUCTS
( ID INT NOT NULL,
CATEGORY VARCHAR(12) NOT NULL,
REORDER TINYINT NOT NULL,
SIZE VARCHAR(12) ,
DISCRIPTION varchar(100) NOT NULL,
PRICE SMALLMONEY NOT NULL,
QNTY TINYINT,
FROMSUPPLIER INT REFERENCES SUPPLIERS(ID) NOT NULL,
FROMBACKUP INT REFERENCES SUPPLIERS(ID),
PRIMARY KEY (ID));

CREATE TABLE CUSTOMERS
( ID INT NOT NULL,
fNAME VARCHAR(30) NOT NULL,
lNAME VARCHAR(30) NOT NULL,
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP TINYINT NOT NULL,
ZIP4 TINYINT,
EMAIL VARCHAR(50) NOT NULL,
NOTES VARCHAR(MAX),
PHONENUMBER VARCHAR(12) NOT NULL,
PRIMARY KEY (ID));

CREATE TABLE ORDERS
( ID INT NOT NULL,
BYCUSTOMER INT NOT NULL,
ENTEREDBYEMPLOYEE INT NOT NULL,
PLACEDON DATE NOT NULL,
PRIMARY KEY (ID));

CREATE TABLE ORDERDETAILS
(ORDERID INT NOT NULL REFERENCES ORDERS(ID) on delete cascade,
PRODUCTID INT NOT NULL REFERENCES PRODUCTS(ID) on delete cascade,
QUANTITY INT NOT NULL,
);

to add test data

INSERT INTO CUSTOMERS
VALUES(1,'Kim','Gross','332 W Dewey Ave','Council','ID',83612,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(2,'Elaine','Gross','501 Illinois Ave','Nampa','ID',83612,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(3,'Joe','blow','332 W Dewey Ave','Nampa','ID',83612,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(4,'Fred','Savage','501 Illinois Ave','Caldwell','ID',83612,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(5,'John','Jingle','332 W Dewey Ave','Boise','ID',83612,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(6,'Jane','Doe','501 Illinois Ave','Indian Valley','ID',83602,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(7,'Tim','Tiny','332 W Dewey Ave','Denver','ID',83632,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(8,'Bud','Wiser','501 Illinois Ave','Bear','ID',83642,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(9,'Gene','Ie','332 W Dewey Ave','Nampa','ID',83692,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(10,'Patty','Peppermint','501 Illinois Ave','Seattle','ID',83682,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(11,'Tiegen','Love','332 W Dewey Ave','Portland','ID',83672,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(12,'OJ','Simpson','501 Illinois Ave','Baler','ID',83662,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(13,'Hello','There','332 W Dewey Ave','Baker','ID',83652,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(14,'Over','Here','501 Illinois Ave','McCall','ID',83642,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(15,'Bye Bye','Falicia','332 W Dewey Ave','Sun Valley','ID',83632,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(16,'Tyson','Sides','501 Illinois Ave','Casper','ID',83619,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(17,'Katie','Mechling','332 W Dewey Ave','Lewiston','ID',83610,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(18,'JR','Simplot','501 Illinois Ave','Asotin','ID',83611,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(19,'Unique','Grow','332 W Dewey Ave','Sometown','ID',83512,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(20,'Bad','Customers','501 Illinois Ave','Anytown','ID',83532,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764'),
(21,'Tired','Of Making Up Names','332 W Dewey Ave','Nampa','ID',83686,4321,'kgross@jensalt.com','not a very nice person to deal with... opps that should not be in here','208-353-5248'),
(22,'Last','Name','501 Illinois Ave','Council','ID',83612,null,'kgross@bigfoot.com','not a very nice person to deal with... opps that should not be in here','208-697-6764');

insert into EMPLOYEES
VALUES(1,518942921,'1967-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,1,'Elaine Gross'),
(2,558942921,'1967-4-16',2,1,1,'2017-1-1','332 W Dewey','Nampa','ID',83685,null,1,'Kim Gross'),
(3,518940125,'1967-5-16',1,1,1,'2016-1-1','332 W Dewey','Nampa','ID',83612,null,1,'Billy Bob'),
(4,518941234,'1972-3-16',2,1,0,'1998-1-1','332 W Dewey','Nampa','ID',83687,null,1,'Who Me?'),
(5,518944325,'1982-3-16',4,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83612,null,0,'yes you'),
(6,519942911,'1985-3-16',5,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'not me'),
(7,516942931,'1984-3-16',3,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'then who'),
(8,515942941,'1961-3-16',1,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'peter pan'),
(9,514942951,'1990-3-16',0,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'wendy'),
(10,513942521,'2000-3-16',1,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83666,null,0,'mom'),
(11,512942621,'1999-3-16',1,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83666,null,1,'dad'),
(12,511942721,'1998-3-16',2,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'uncle'),
(13,510942821,'1997-3-16',3,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'aunty M'),
(14,528942121,'1969-3-16',5,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'Aunt Bee'),
(15,538941921,'1970-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'Lex Luther'),
(16,548943921,'1990-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83646,null,1,'Clark Kent'),
(17,558944921,'1991-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83636,null,1,'Lois Lane'),
(18,568945921,'1992-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83626,null,1,'Robert Gross'),
(19,578946921,'1995-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83626,null,1,'Melissa Gross'),
(20,588947921,'1996-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83286,null,1,'Beyza Dursan');

INSERT INTO SUPPLIERS
VALUES (1,'wholesale supply','here','there','ID',83686,null,'orders@wholesale.com','208-354-4512','jon jingle','CC only',null),
(2,'ricks stuff','here','there','WA',83681,null,'orders@wholesale.com','208-354-0101','jon jingle','CC only',null),
(3,'whole supply','here','there','OR',8368,null,'orders@wholesale.com','208-354-4021','jon jingle','CC only',null),
(4,'sale supply','here','there','ID',83686,null,'orders@wholesale.com','208-354-3485','jon jingle','CC only',null),
(5,'wsale supply','here','there','CO',83686,null,'orders@wholesale.com','208-354-8798','jon jingle','CC only',null),
(6,'wholesale sup','here','there','DC',83686,null,'orders@wholesale.com','208-354-7848','jon jingle','CC only',null),
(7,'wholeply','here','there','FL',83686,null,'orders@wholesale.com','208-354-9748','jon jingle','CC only',null),
(8,'wsale sup','here','there','CA',83686,null,'orders@wholesale.com','208-208-4512','jon jingle','CC only',null),
(9,'PFO supply','here','there','CA',83686,null,'orders@wholesale.com','208-253-4512','jon jingle','CC only',null),
(10,'Aquamedic supply','here','there','ID',83686,null,'orders@wholesale.com','209-354-4512','jon jingle','CC only',null),
(11,'Tuniz supply','here','there','ID',83686,null,'orders@wholesale.com','207-354-4512','jon jingle','CC only',null),
(12,'Ebo Jager supply','here','there','ID',83686,null,'orders@wholesale.com','228-354-4512','jon jingle','CC only',null),
(13,'Marine Depot supply','here','there','ID',83686,null,'orders@wholesale.com','218-354-4512','jon jingle','CC only',null),
(14,'wholesale Depot','here','there','ID',83686,null,'orders@wholesale.com','238-354-4512','jon jingle','CC only',null),
(15,'Depot supply','here','there','ID',83686,null,'orders@wholesale.com','248-354-4512','jon jingle','CC only',null),
(16,'wholy','here','there','ID',83686,null,'orders@wholesale.com','258-354-4512','jon jingle','CC only',null),
(17,'who supply','here','there','ID',83686,null,'orders@wholesale.com','268-354-4512','jon jingle','CC only',null),
(18,'wholes supply','here','there','ID',83686,null,'orders@wholesale.com','278-354-4512','jon jingle','CC only',null),
(19,'wholes sup','here','there','ID',83686,null,'orders@wholesale.com','298-354-4512','jon jingle','CC only',null),
(20,'whole supper','here','there','ID',83686,null,'orders@wholesale.com','208-324-4512','jon jingle','CC only',null),
(21,'who supplies','here','there','ID',83686,null,'orders@wholesale.com','208-314-4512','jon jingle','CC only',null),
(22,'who is here','here','there','ID',83686,null,'orders@wholesale.com','208-334-4512','jon jingle','CC only',null),
(23,'who is there','here','there','ID',83686,null,'orders@wholesale.com','208-254-4512','jon jingle','CC only',null),
(24,'who is over there','here','there','ID',83686,null,'orders@wholesale.com','208-154-4512','jon jingle','CC only',null);

INSERT INTO products
VALUES (1,'hardgood',2,null,'Light',199.99,3,1,2),
(2,'livestock',2,'med','yellow tang',20.99,5,2,1),
(3,'hardgood',2,null,'prizm skimmer',49.99,3,3,4),
(4,'livestock',10,'small','blue damsel',4.99,8,5,6),
(5,'hardgood',5,'1 inch','standard bulkhead',9.99,4,6,4),
(6,'service',0,null,'drill for bulkhead',19.99,99,8,1),
(7,'service',0,null,'install overflow',39.99,99,1,12),
(8,'hardgood',16,'1.5 inch','standard bulkhead',11.99,25,18,2),
(9,'hardgood',5,'2 inch','standard bulkhead',15.99,4,12,1),
(10,'livestock',3,'small','yellow tang',17.99,4,1,12),
(11,'livestock',3,'small','blue tang',38.99,2,16,15),
(12,'livestock',12,'med','blue tang',42.99,15,12,15),
(13,'hardgood',11,'1000','Turbofloter skimmer',199.99,25,5,13),
(14,'service',0,null,'install system',199.99,99,4,5),
(15,'hardgood',12,'3','Pump',37.99,5,3,2),
(16,'livestock',13,'3','coral frag',15.99,45,1,2),
(17,'livestock',14,'med','frogfish',29.99,2,3,16),
(18,'hardgood',15,'3/4 inch','double thread bulkhead',9.99,10,1,2),
(19,'hardgood',16,'large','pump',129.99,25,1,2),
(20,'livestock',17,'extra large','naso tang',89.99,2,3,4);

INSERT INTO ORDERS
VALUES (1,1,1,'2016-4-16'),
(2,2,1,'2017-5-1'),
(3,1,20,'2017-1-2'),
(4,12,19,'2017-2-1'),
(5,3,19,'2017-3-1'),
(6,4,18,'2017-4-1'),
(7,5,17,'2017-5-1'),
(8,5,16,'2017-6-1'),
(9,6,15,'2017-7-1'),
(10,7,14,'2017-7-2'),
(11,8,13,'2017-7-3'),
(12,9,12,'2017-7-4'),
(13,10,11,'2017-7-5'),
(14,11,11,'2017-7-6'),
(15,12,12,'2017-7-7'),
(16,13,13,'2017-7-8'),
(17,14,14,'2017-7-9'),
(18,15,15,'2017-7-10'),
(19,16,16,'2017-7-11'),
(20,17,17,'2017-7-12');

INSERT INTO ORDERDETAILS
VALUES (1,1,1),
(1,2,1),
(1,1,20),
(1,12,19),
(1,3,19),
(1,4,18),
(2,5,17),
(2,5,16),
(3,6,15),
(4,7,14),
(4,8,13),
(5,9,12),
(6,10,11),
(7,11,11),
(8,12,12),
(9,13,13),
(10,14,14),
(11,15,15),
(12,16,16),
(13,17,17),
(14,2,1),
(15,3,3),
(16,5,7),
(17,6,2),
(18,6,8),
(19,20,1),
(20,19,2),
(20,18,2),
(20,17,3),
(20,15,3);

4

The query above outputs a table with the employee name, category and sales total for each combination of category and employee.

What I would like to create is a table that has

Employee HardGoods livestock Service Total
Anne Thatcher 569.85 569.85
Cluster Gross 393.93 89.97 483.91
cody reed 515.88 515.88
Elaine Gross 329.67 329.67
Eric Mutchler 149.97 149.97
Johnny Thatcher 255.84 255.84
Katie Mechling 20.99 20.99
Kevin Reed 428.89 428.89
kim gross 3049.60 927.62 3977.22
Lainey Thatcher 2799.86 2799.86
melissa gross 155.87 559.86 715.73
Miles Thatcher 509.83 509.83
Rich T 159.92 159.92
Richard T 39.98 39.98
Richard Reed 69.93 69.93
Robert Gross 299.85 299.85
Sugar Gross 89.99 89.99
Tiegen Reed 197.89 197.89
Tyson sides 191.88 191.88
Vicotoria Thatcher 2599.87 2599.87

5

@Kristen,
Would you mind looking at this?

6

I'm not much good with PIVOT ... hopefully one of the others will be along shortly

1 Like
7

I'm looking at this now. I'll post back as soon as I have a working solution.

1 Like
8

Okay... #1... ALWAYS test your test data... I just spent WAY too long fixing errors. #2 ALWAYS make sure that you desired results are based on the test data you supplied. Anne Thatcher, for example doesn't exist in the data you supplied... So I have absolutely no idea if my calculations match your expectations.

That all said... I "think the following should be what you're looking for...

-- by employee...
SELECT 
	Employee = e.[Name],
	hardgood = SUM(CASE WHEN p.CATEGORY = 'hardgood' THEN od.QUANTITY * p.PRICE END),
	livestock = SUM(CASE WHEN p.CATEGORY = 'livestock' THEN od.QUANTITY * p.PRICE END),
	[service] = SUM(CASE WHEN p.CATEGORY = 'service' THEN od.QUANTITY * p.PRICE END),
	Total = SUM(od.QUANTITY * p.PRICE)
FROM
	dbo.ORDERS o
	JOIN dbo.EMPLOYEES e
		ON o.ENTEREDBYEMPLOYEE = e.ID
	JOIN dbo.ORDERDETAILS od
		ON o.ID = od.ORDERID
	JOIN dbo.PRODUCTS p
		ON od.PRODUCTID = p.ID
GROUP BY 
	e.ID,
	e.[Name];

-- by customer...
SELECT 
	Customer = CONCAT(c.fNAME + ' ', c.lNAME),
	hardgood = SUM(CASE WHEN p.CATEGORY = 'hardgood' THEN od.QUANTITY * p.PRICE END),
	livestock = SUM(CASE WHEN p.CATEGORY = 'livestock' THEN od.QUANTITY * p.PRICE END),
	[service] = SUM(CASE WHEN p.CATEGORY = 'service' THEN od.QUANTITY * p.PRICE END),
	Total = SUM(od.QUANTITY * p.PRICE)
FROM
	dbo.ORDERS o
	JOIN dbo.CUSTOMERS c
		ON o.BYCUSTOMER = c.ID
	JOIN dbo.ORDERDETAILS od
		ON o.ID = od.ORDERID
	JOIN dbo.PRODUCTS p
		ON od.PRODUCTID = p.ID
GROUP BY 
	c.ID,
	c.fNAME,
	c.lNAME;
1 Like
9

For anyone else who'd like you play along, here is the debugged DDL...

IF OBJECT_ID('tempdb.dbo.ORDERDETAILS', 'U') IS NOT NULL 
DROP TABLE tempdb.dbo.ORDERDETAILS;
GO

IF OBJECT_ID('tempdb.dbo.ORDERS', 'U') IS NOT NULL 
DROP TABLE tempdb.dbo.ORDERS;
GO

IF OBJECT_ID('tempdb.dbo.PRODUCTS', 'U') IS NOT NULL 
DROP TABLE tempdb.dbo.PRODUCTS;
GO

IF OBJECT_ID('tempdb.dbo.SUPPLIERS', 'U') IS NOT NULL 
DROP TABLE tempdb.dbo.SUPPLIERS;
GO

IF OBJECT_ID('tempdb.dbo.CUSTOMERS', 'U') IS NOT NULL 
DROP TABLE tempdb.dbo.CUSTOMERS;
go

IF OBJECT_ID('tempdb.dbo.EMPLOYEES', 'U') IS NOT NULL 
DROP TABLE tempdb.dbo.EMPLOYEES;
GO


CREATE TABLE tempdb.dbo.EMPLOYEES (
    ID INT NOT NULL,
    SSN DECIMAL(9, 0) NOT NULL,
    BIRTHDATE DATE NOT NULL,
    DEPENDANTS TINYINT NOT NULL,
    MARRIED BIT NOT NULL,
    FULL_PART_TIME BIT NOT NULL,
    HIRE_DATE DATE NOT NULL,
    ADDRESS VARCHAR(30) NOT NULL,
    CITY VARCHAR(30) NOT NULL,
    STATE CHAR(2) NOT NULL,
    ZIP CHAR(5) NOT NULL,
    ZIP4 CHAR(5),
    COMPLETED_TRAINING BIT
        DEFAULT 0,
    Name VARCHAR(30) NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT AK_SSN
        UNIQUE (SSN)
    );

CREATE TABLE tempdb.dbo.SUPPLIERS (
    ID INT NOT NULL,
    NAME VARCHAR(50) NOT NULL,
    ADDRESS VARCHAR(30) NOT NULL,
    CITY VARCHAR(30) NOT NULL,
    STATE CHAR(2) NOT NULL,
    ZIP CHAR(5) NOT NULL,
    ZIP4 CHAR(4),
    EMAIL VARCHAR(50) NOT NULL,
    PHONENUMBER CHAR(12) NOT NULL,
    CONTACT VARCHAR(30),
    TERMS VARCHAR(MAX),
    NOTES VARCHAR(MAX),
    PRIMARY KEY (ID)
    );

CREATE TABLE tempdb.dbo.PRODUCTS (
    ID INT NOT NULL,
    CATEGORY VARCHAR(12) NOT NULL,
    REORDER TINYINT NOT NULL,
    SIZE VARCHAR(12),
    DISCRIPTION VARCHAR(100) NOT NULL,
    PRICE SMALLMONEY NOT NULL,
    QNTY TINYINT,
    FROMSUPPLIER INT
        REFERENCES SUPPLIERS (ID) NOT NULL,
    FROMBACKUP INT
        REFERENCES SUPPLIERS (ID),
    PRIMARY KEY (ID)
    );

CREATE TABLE tempdb.dbo.CUSTOMERS (
    ID INT NOT NULL,
    fNAME VARCHAR(30) NOT NULL,
    lNAME VARCHAR(30) NOT NULL,
    ADDRESS VARCHAR(30) NOT NULL,
    CITY VARCHAR(30) NOT NULL,
    STATE CHAR(2) NOT NULL,
    ZIP CHAR(5) NOT NULL,
    ZIP4 CHAR(4),
    EMAIL VARCHAR(50) NOT NULL,
    NOTES VARCHAR(MAX),
    PHONENUMBER VARCHAR(12) NOT NULL,
    PRIMARY KEY (ID)
    );

CREATE TABLE tempdb.dbo.ORDERS (
    ID INT NOT NULL,
    BYCUSTOMER INT NOT NULL,
    ENTEREDBYEMPLOYEE INT NOT NULL,
    PLACEDON DATE NOT NULL,
    PRIMARY KEY (ID)
    );

CREATE TABLE tempdb.dbo.ORDERDETAILS (
    ORDERID INT NOT NULL
        REFERENCES ORDERS (ID) ON DELETE CASCADE,
    PRODUCTID INT NOT NULL
        REFERENCES PRODUCTS (ID) ON DELETE CASCADE,
    QUANTITY INT NOT NULL,
    );

--to add test data

INSERT INTO CUSTOMERS
VALUES(1,'Kim','Gross','332 W Dewey Ave','Council','ID',83612,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(2,'Elaine','Gross','501 Illinois Ave','Nampa','ID',83612,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764'),
(3,'Joe','blow','332 W Dewey Ave','Nampa','ID',83612,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(4,'Fred','Savage','501 Illinois Ave','Caldwell','ID',83612,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764'),
(5,'John','Jingle','332 W Dewey Ave','Boise','ID',83612,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(6,'Jane','Doe','501 Illinois Ave','Indian Valley','ID',83602,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764'),
(7,'Tim','Tiny','332 W Dewey Ave','Denver','ID',83632,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(8,'Bud','Wiser','501 Illinois Ave','Bear','ID',83642,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764'),
(9,'Gene','Ie','332 W Dewey Ave','Nampa','ID',83692,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(10,'Patty','Peppermint','501 Illinois Ave','Seattle','ID',83682,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764'),
(11,'Tiegen','Love','332 W Dewey Ave','Portland','ID',83672,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(12,'OJ','Simpson','501 Illinois Ave','Baler','ID',83662,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764'),
(13,'Hello','There','332 W Dewey Ave','Baker','ID',83652,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(14,'Over','Here','501 Illinois Ave','McCall','ID',83642,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764'),
(15,'Bye Bye','Falicia','332 W Dewey Ave','Sun Valley','ID',83632,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(16,'Tyson','Sides','501 Illinois Ave','Casper','ID',83619,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764'),
(17,'Katie','Mechling','332 W Dewey Ave','Lewiston','ID',83610,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(18,'JR','Simplot','501 Illinois Ave','Asotin','ID',83611,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764'),
(19,'Unique','Grow','332 W Dewey Ave','Sometown','ID',83512,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(20,'Bad','Customers','501 Illinois Ave','Anytown','ID',83532,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764'),
(21,'Tired','Of Making Up Names','332 W Dewey Ave','Nampa','ID',83686,4321,'kgross@jensalt.com','not a very nice person to deal with… opps that should not be in here','208-353-5248'),
(22,'Last','Name','501 Illinois Ave','Council','ID',83612,null,'kgross@bigfoot.com','not a very nice person to deal with… opps that should not be in here','208-697-6764');

insert into EMPLOYEES (ID, SSN, BIRTHDATE, DEPENDANTS, MARRIED, FULL_PART_TIME, HIRE_DATE, ADDRESS, CITY, STATE, ZIP, ZIP4, COMPLETED_TRAINING, Name) VALUES
	(1,518942921,'1967-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,1,'Elaine Gross'),
	(2,558942921,'1967-4-16',2,1,1,'2017-1-1','332 W Dewey','Nampa','ID',83685,null,1,'Kim Gross'),
	(3,518940125,'1967-5-16',1,1,1,'2016-1-1','332 W Dewey','Nampa','ID',83612,null,1,'Billy Bob'),
	(4,518941234,'1972-3-16',2,1,0,'1998-1-1','332 W Dewey','Nampa','ID',83687,null,1,'Who Me?'),
	(5,518944325,'1982-3-16',4,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83612,null,0,'yes you'),
	(6,519942911,'1985-3-16',5,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'not me'),
	(7,516942931,'1984-3-16',3,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'then who'),
	(8,515942941,'1961-3-16',1,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'peter pan'),
	(9,514942951,'1990-3-16',0,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83686,null,0,'wendy'),
	(10,513942521,'2000-3-16',1,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83666,null,0,'mom'),
	(11,512942621,'1999-3-16',1,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83666,null,1,'dad'),
	(12,511942721,'1998-3-16',2,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'uncle'),
	(13,510942821,'1997-3-16',3,0,0,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'aunty M'),
	(14,528942121,'1969-3-16',5,1,0,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'Aunt Bee'),
	(15,538941921,'1970-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83656,null,1,'Lex Luther'),
	(16,548943921,'1990-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83646,null,1,'Clark Kent'),
	(17,558944921,'1991-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83636,null,1,'Lois Lane'),
	(18,568945921,'1992-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83626,null,1,'Robert Gross'),
	(19,578946921,'1995-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83626,null,1,'Melissa Gross'),
	(20,588947921,'1996-3-16',5,1,1,'1985-1-1','332 W Dewey','Nampa','ID',83286,null,1,'Beyza Dursan');

INSERT INTO SUPPLIERS
VALUES (1,'wholesale supply','here','there','ID',83686,null,'orders@wholesale.com','208-354-4512','jon jingle','CC only',null),
(2,'ricks stuff','here','there','WA',83681,null,'orders@wholesale.com','208-354-0101','jon jingle','CC only',null),
(3,'whole supply','here','there','OR',8368,null,'orders@wholesale.com','208-354-4021','jon jingle','CC only',null),
(4,'sale supply','here','there','ID',83686,null,'orders@wholesale.com','208-354-3485','jon jingle','CC only',null),
(5,'wsale supply','here','there','CO',83686,null,'orders@wholesale.com','208-354-8798','jon jingle','CC only',null),
(6,'wholesale sup','here','there','DC',83686,null,'orders@wholesale.com','208-354-7848','jon jingle','CC only',null),
(7,'wholeply','here','there','FL',83686,null,'orders@wholesale.com','208-354-9748','jon jingle','CC only',null),
(8,'wsale sup','here','there','CA',83686,null,'orders@wholesale.com','208-208-4512','jon jingle','CC only',null),
(9,'PFO supply','here','there','CA',83686,null,'orders@wholesale.com','208-253-4512','jon jingle','CC only',null),
(10,'Aquamedic supply','here','there','ID',83686,null,'orders@wholesale.com','209-354-4512','jon jingle','CC only',null),
(11,'Tuniz supply','here','there','ID',83686,null,'orders@wholesale.com','207-354-4512','jon jingle','CC only',null),
(12,'Ebo Jager supply','here','there','ID',83686,null,'orders@wholesale.com','228-354-4512','jon jingle','CC only',null),
(13,'Marine Depot supply','here','there','ID',83686,null,'orders@wholesale.com','218-354-4512','jon jingle','CC only',null),
(14,'wholesale Depot','here','there','ID',83686,null,'orders@wholesale.com','238-354-4512','jon jingle','CC only',null),
(15,'Depot supply','here','there','ID',83686,null,'orders@wholesale.com','248-354-4512','jon jingle','CC only',null),
(16,'wholy','here','there','ID',83686,null,'orders@wholesale.com','258-354-4512','jon jingle','CC only',null),
(17,'who supply','here','there','ID',83686,null,'orders@wholesale.com','268-354-4512','jon jingle','CC only',null),
(18,'wholes supply','here','there','ID',83686,null,'orders@wholesale.com','278-354-4512','jon jingle','CC only',null),
(19,'wholes sup','here','there','ID',83686,null,'orders@wholesale.com','298-354-4512','jon jingle','CC only',null),
(20,'whole supper','here','there','ID',83686,null,'orders@wholesale.com','208-324-4512','jon jingle','CC only',null),
(21,'who supplies','here','there','ID',83686,null,'orders@wholesale.com','208-314-4512','jon jingle','CC only',null),
(22,'who is here','here','there','ID',83686,null,'orders@wholesale.com','208-334-4512','jon jingle','CC only',null),
(23,'who is there','here','there','ID',83686,null,'orders@wholesale.com','208-254-4512','jon jingle','CC only',null),
(24,'who is over there','here','there','ID',83686,null,'orders@wholesale.com','208-154-4512','jon jingle','CC only',null);

INSERT INTO products
VALUES (1,'hardgood',2,null,'Light',199.99,3,1,2),
(2,'livestock',2,'med','yellow tang',20.99,5,2,1),
(3,'hardgood',2,null,'prizm skimmer',49.99,3,3,4),
(4,'livestock',10,'small','blue damsel',4.99,8,5,6),
(5,'hardgood',5,'1 inch','standard bulkhead',9.99,4,6,4),
(6,'service',0,null,'drill for bulkhead',19.99,99,8,1),
(7,'service',0,null,'install overflow',39.99,99,1,12),
(8,'hardgood',16,'1.5 inch','standard bulkhead',11.99,25,18,2),
(9,'hardgood',5,'2 inch','standard bulkhead',15.99,4,12,1),
(10,'livestock',3,'small','yellow tang',17.99,4,1,12),
(11,'livestock',3,'small','blue tang',38.99,2,16,15),
(12,'livestock',12,'med','blue tang',42.99,15,12,15),
(13,'hardgood',11,'1000','Turbofloter skimmer',199.99,25,5,13),
(14,'service',0,null,'install system',199.99,99,4,5),
(15,'hardgood',12,'3','Pump',37.99,5,3,2),
(16,'livestock',13,'3','coral frag',15.99,45,1,2),
(17,'livestock',14,'med','frogfish',29.99,2,3,16),
(18,'hardgood',15,'3/4 inch','double thread bulkhead',9.99,10,1,2),
(19,'hardgood',16,'large','pump',129.99,25,1,2),
(20,'livestock',17,'extra large','naso tang',89.99,2,3,4);

INSERT INTO ORDERS
VALUES (1,1,1,'2016-4-16'),
(2,2,1,'2017-5-1'),
(3,1,20,'2017-1-2'),
(4,12,19,'2017-2-1'),
(5,3,19,'2017-3-1'),
(6,4,18,'2017-4-1'),
(7,5,17,'2017-5-1'),
(8,5,16,'2017-6-1'),
(9,6,15,'2017-7-1'),
(10,7,14,'2017-7-2'),
(11,8,13,'2017-7-3'),
(12,9,12,'2017-7-4'),
(13,10,11,'2017-7-5'),
(14,11,11,'2017-7-6'),
(15,12,12,'2017-7-7'),
(16,13,13,'2017-7-8'),
(17,14,14,'2017-7-9'),
(18,15,15,'2017-7-10'),
(19,16,16,'2017-7-11'),
(20,17,17,'2017-7-12');

INSERT INTO ORDERDETAILS
VALUES (1,1,1),
(1,2,1),
(1,1,20),
(1,12,19),
(1,3,19),
(1,4,18),
(2,5,17),
(2,5,16),
(3,6,15),
(4,7,14),
(4,8,13),
(5,9,12),
(6,10,11),
(7,11,11),
(8,12,12),
(9,13,13),
(10,14,14),
(11,15,15),
(12,16,16),
(13,17,17),
(14,2,1),
(15,3,3),
(16,5,7),
(17,6,2),
(18,6,8),
(19,20,1),
(20,19,2),
(20,18,2),
(20,17,3),
(20,15,3);

--======================================================================================

SELECT * FROM dbo.EMPLOYEES e;
SELECT * FROM dbo.SUPPLIERS s;
SELECT * FROM dbo.PRODUCTS p;
SELECT * FROM dbo.CUSTOMERS c;
SELECT * FROM dbo.ORDERS o;
SELECT * FROM dbo.ORDERDETAILS od;
1 Like
10

Sorry about the errors. I gave you my original scripts, but I had some some changes figuring out sql statements. I thought I reset everything to my original but I must have missed some.

Thank you very much for the help

That works very well on my data. I was trying to see if I could make it use the categories listed so the script would not have to be changed when a category was changed, which I can see is not the simple way in SQL.

11

No worries. Just something to keep in mid moving forward. The better your test data and the better your description, the more people you'll find willing to take a crack at answering your question.

As far as pulling the categories dynamically, it's easy enough to do but you need to use dynamic sql to do it... Give a few and I'll post a dynamic version.

12

Trying to make sure I understand this. I have one question. Why the brackets around e.[name]?

13

"Name" is a reserved keyword in SQL server. I shouldn't be used to name user defined objects. The square brackets tell SSMS that we're using it as a column name anyway, so quit highlighting it as a keyword.

It also satisfies my OCD by preventing it being colored like a keyword in SSMS... :crazy_face:

1 Like
14

[quote="Jason_A_Long, post:13, topic:11003"]
“Name” is a reserved keyword in SQL server.
[/quote] Thank you very much

15

Kim, What version of SQL Server are you using?

16

2012 is what I have installed to play with.

17

Sweet!... Wanted to make sure I can use the CONCAT function.

1 Like
18

Here's a dynamic solution...

-- dynamic solution... (employee)
DECLARE 
	@PivotCols VARCHAR(8000) = '',
	@sql VARCHAR(8000) = '',
	@DeBug BIT = 0


SELECT 
	@PivotCols = CONCAT(@PivotCols, CHAR(13), CHAR(10), CHAR(9), '[', dpc.CATEGORY, '] = SUM(CASE WHEN p.CATEGORY = ''', dpc.CATEGORY, ''' THEN od.QUANTITY * p.PRICE END),')
FROM
	(
	SELECT DISTINCT
		p.CATEGORY
	FROM
		dbo.PRODUCTS p
	) dpc
ORDER BY 
	dpc.CATEGORY;

SET @sql = CONCAT('
SELECT 
	Employee = e.[Name],', 
	@PivotCols, '
	Total = SUM(od.QUANTITY * p.PRICE)
FROM
	dbo.ORDERS o
	JOIN dbo.EMPLOYEES e
		ON o.ENTEREDBYEMPLOYEE = e.ID
	JOIN dbo.ORDERDETAILS od
		ON o.ID = od.ORDERID
	JOIN dbo.PRODUCTS p
		ON od.PRODUCTID = p.ID
GROUP BY 
	e.ID,
	e.[Name];');


IF @DeBug = 1
BEGIN
	PRINT(@sql);
END;
ELSE
BEGIN
	EXEC(@sql);
END;
1 Like
19

Your creating a sql statement dynamically using the category information that you pull out of the table in the first select statement statement. With the @pivotCols, being the variable you put all the different categories into and @sql being the full sql statement you create, and then you run it with the exec statement correct?

20

You got it! :slight_smile:

You'll notice that I added a @Debug variable. You can set it to 1 to see the code that the dynamic is building, w/o actually executing the generated code.

1 Like