Simple Pivot Question

Transact-SQL
1

I have a table like the following:

image

and I am having a hard time with the syntax and help is appreciated!

2 
SELECT ca1.*
FROM dbo.atable a
CROSS APPLY (
    SELECT *
    FROM (VALUES
        (Date, Location, Department, Sales1), 
        (Date, Location, Department, Sales2), 
        (Date, Location, Department, Sales3), 
        (Date, Location, Department, Sales4), 
        (Date, Location, Department, Sales5) ) 
    AS result(Date, Location, Department, SalesMeasures)
) AS ca1
1 Like
3

In future please provide consumable test data with dates in ISO format. (ie A test rig.)

CREATE TABLE #t
(
	[Date] date NOT NULL
	,[Location] varchar(30) NOT NULL
	,Department varchar(30) NOT NULL
	,Sales1 money NOT NULL
	,Sales2 money NOT NULL
	,Sales3 money NOT NULL
	,Sales4 money NOT NULL
	,Sales5 money NOT NULL
);
INSERT INTO #t
VALUES ('20200903', 'Central North', 'SalesTeam', 100.02, 44.26, 101.19,83.35, 64.26)
	,('20200904', 'Central North', 'SalesTeam', 88.98, 466.22, 66.42, 24.38, 11.11)
	,('20200905', 'Central North', 'SalesTeam', 66.72, 22.46, 27.26, 63.43, 67.48)
	,('20200906', 'Central North', 'SalesTeam', 209.33, 636.36, 333.65, 56.37, 68.38)
	,('20200907', 'Central North', 'SalesTeam', 55.33, 234.2, 375.25, 57.37, 88.95);

One option:

SELECT T.[Date], T.[Location], T.Department
	,X.SalesMeasures, X.SalesValue
FROM #t T
	CROSS APPLY
	(
		VALUES ('Sales1', T.Sales1), ('Sales2', T.Sales2), ('Sales3', T.Sales3)
			,('Sales4', T.Sales4), ('Sales5', T.Sales5)
	) X (SalesMeasures, SalesValue);
1 Like