Data manipulation query help

Transact-SQL
1

hi guys

i need some help maniplating some date.

example is below

image

so currently i have the date which has title 1 and i want to munipliate this so its looks like table 2

i also want a diff column added which shows the difference between level 1 and level 2, can this be achieved ?

i would be grateful for any help given , my sql scripting is not at a high level

thank you guys

2

We need useable sample data, i.e., create table and insert statements, not just a splat/picture of data.

1 Like
3

I do not understand your data but this should get you started:

-- *** Test Data ***
CREATE TABLE #t
(
	[system] varchar(20) NOT NULL
	,[date] date NOT NULL
	,level1 int NOT NULL
	,result int NOT NULL
	,overallScore int NOT NULL
);
INSERT INTO #t
VALUES ('red', '20171101', 1, 10, 5)
	,('red', '20171101', 2, 2, 3)
	,('pop', '20171101', 1, 3, 1)
	,('pop', '20171101', 2, 1, 1)
	,('red', '20171102', 1, 3, 1)
	,('red', '20171102', 2, 1, 2)
	,('pop', '20171102', 1, 5, 7)
	,('pop', '20171102', 2, 10, 8);
-- *** End Test Data ***

SELECT D.[system], T.[Type], D.[date]
	,MAX(X.Level1) AS Level1
	,MAX(X.Level2) AS Level2
	,MAX(X.Level1) - MAX(X.Level2) AS [Difference]
FROM #t D
	CROSS JOIN (SELECT 'Result' UNION ALL SELECT 'OverallScore') T ([Type])
	CROSS APPLY
	(
		VALUES
		(
			CASE
				WHEN D.level1 = 1 AND T.[Type] = 'Result'
				THEN D.result
				WHEN D.level1 = 1 AND T.[Type] = 'OverallScore'
				THEN D.overallScore
			END
			,CASE
				WHEN D.level1 = 2 AND T.[Type] = 'Result'
				THEN D.result
				WHEN D.level1 = 2 AND T.[Type] = 'OverallScore'
				THEN D.overallScore
			END
		)
	) X (Level1, Level2)
GROUP BY D.[system], T.[Type], D.[date]
ORDER BY [date], [system] DESC, [Type] DESC;