Not sure how to pivot this data?

1

I am trying to pivot some data in SQL, but in all honesty, I dont really understand it. So the data is like this:

Create table Company_rank (company_name varchar(100), Public_rank varchar(20), Peer_rank varchar(20), Online_rank varchar(20), Company_id integer)

Insert into Company_rank (company_name , Public_rank , Peer_rank , Online_rank , Company_id )
VALUES ('ABCCompany', '20','35', '15',1)
VALUES ('BCDCompany', '25','32', '20',2)
VALUES ('DEFCompany', '18','20', '25',3)

What I need to see is the ranking as rows, which I can use to join to another table, for each company. I need the company_id to stay as a column, as I need that for joining, but I dont think that's possible? So, basically, I need the type of ranking and the company_name to be available for joining, as another table has ranking_name, and company_id

Sorry if this seems jumbled!

I was thinking of this:

Rank_type ABCCompany BCDCompany DEF Company
Public_rank 20 25 18
Peer_rank 35 32 20
Online_rank 15 20 25

But then I've lost the ability to get a key from another table, which needs both the rank_type, and the company_id (this can be derived from company_name or company_id)

So ideally the end result, I would have three tables, which would look like this (company_rank is my staged data, which I am trying to get into these):

Tables Expected values Notes
rank_history
Rank_History_Id 1 Identity
Ranking_Id 1 FK to_Ranking_type
Value 15
Valid_from 04/11/2022
Valid_to 99/99/9999
Ranking_type
Ranking_Id 1 Identity
name Online_rank
Company_id 1 FK to Company
Create table Company
Company_id 1 Identity
Company_name ABCCompany'
2

It is not entirely clear what you want but the following should get you started.

With:

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE dbo.Rank_Types
(
	Rank_Type_id int NOT NULL
		CONSTRAINT Rank_Types_PK PRIMARY KEY
	,Rank_Type_Name varchar(20) NOT NULL
);
INSERT INTO dbo.Rank_Types
VALUES (1, 'Public_rank')
	,(2, 'Peer_rank')
	,(3, 'Online_rank');
GO
CREATE TABLE dbo.Companies
(
	Company_id int NOT NULL
		CONSTRAINT Companies_PK PRIMARY KEY
	,Company_name varchar(30) NOT NULL
);
GO
CREATE TABLE dbo.Rank_History
(
	Company_id int NOT NULL
		CONSTRAINT Rank_History_Company_id_FK REFERENCES dbo.Companies(Company_id)
	,Rank_Type_id int NOT NULL
		CONSTRAINT Rank_History_Rank_Type_id_FK REFERENCES dbo.Rank_Types(Rank_Type_id)
	,Valid_From date NOT NULL
	,Ranking int NOT NULL
	,CONSTRAINT Rank_History_PK
		PRIMARY KEY (Company_id, Rank_Type_id, Valid_From)
);

Try something like:

INSERT INTO dbo.Companies
SELECT S.Company_id, S.Company_Name
FROM dbo.Company_rank S
WHERE NOT EXISTS
(
	SELECT 1
	FROM dbo.Companies C
	WHERE C.Company_id = S.Company_id
);

WITH Last_Ranks
AS
(
	/* This is wrong
	SELECT Company_id, Rank_Type_id
		,LAST_VALUE(Ranking) OVER (PARTITION BY Company_id, Rank_Type_id ORDER BY Valid_From) AS Ranking
	FROM dbo.Rank_History
	*/
	SELECT Company_id, Rank_Type_id
		,CAST
		(
			SUBSTRING
			(
				MAX
				(
					CONVERT(char(8), Valid_From, 112)
						+ CAST(Ranking AS varchar(10))
				)
				, 9, 10
			)
			AS int
		)
		AS Ranking
	FROM dbo.Rank_History
	GROUP BY Company_id, Rank_Type_id
)
,Rank_Pivot
AS
(
	SELECT S.Company_id, X.Rank_Type_id
		,CAST(CURRENT_TIMESTAMP AS date) AS Valid_From
		,X.Ranking
	FROM dbo.Company_rank S
		CROSS APPLY
		(
			VALUES (1, S.Public_rank)
				,(2, S.Peer_rank)
				,(3, S.Online_rank)
		) X (Rank_Type_id, Ranking)
)
INSERT INTO dbo.Rank_History
SELECT S.Company_id, S.Rank_Type_id, S.Valid_From, S.Ranking
FROM Rank_Pivot S
WHERE NOT EXISTS
(
	SELECT 1
	FROM Last_Ranks L
	WHERE L.Company_id = S.Company_id
		AND L.Rank_Type_id = S.Rank_Type_id
		AND L.Ranking = S.Ranking
);

The results can then be viewed with something like:

SELECT C.Company_name, T.Rank_Type_Name, H.Ranking, H.Valid_From
	,COALESCE
	(
		LEAD(H.Valid_From)
			OVER (PARTITION BY H.Company_id, H.Rank_Type_id ORDER BY H.Valid_From)
		,'9999'
	) AS Valid_To
FROM dbo.Rank_History H
	JOIN dbo.Companies C
		ON H.Company_id = C.Company_id
	JOIN dbo.Rank_Types T
		ON H.Rank_Type_id = T.Rank_Type_id;