0

I have a requirement to generate an ID field for a combination of a few fields in SQL Server. Let me give you an example.

I have a table like below

Brand Owner Source
Pip People Online
whip People Online
Pip People Offline
zip Demons Online
Rip Zombies Online
Dip Ghosts Online

I need to derive BrandID for the distinct BrandName and Owner Combination (irrespective of their source)

I have written SQL like the following which got me the desired output.

Select
  DENSE_RANK() OVER (ORDER BY Brand,Owner) AS BrandID,
  Brand AS BrandName,
  Owner AS BrandOwner,
  Source
From derivdTable

generate the output as

BrandID BrandName Owner Source
1 Dip Ghosts Online
2 Pip People Online
2 Pip Poeple Offline
3 Rip Zombies Online
4 whip people Online
5 zip Demons Online

However, after a few days if I get another entry into my "dervdTable" like

Brand Owner Source
Pip People Online
whip People Online
Pip People Offline
zip Demons Online
Rip Zombies Online
Dip Ghosts Online
Bip People Online

Then my output with the same SQL will change like this.

BrandID BrandName Owner Source
1 Bip People Online
2 Dip Ghosts Online
3 Pip People Online
3 Pip Poeple Offline
4 Rip Zombies Online
5 whip people Online
6 zip Demons Online

Basically the query changed the brandIDs.

If I had BrandID=2 for Pip, I want to keep the same number forever. How do I get it.

I want my output to look like this

BrandID BrandName Owner Source
1 Dip Ghosts Online
2 Pip People Online
2 Pip Poeple Offline
3 Rip Zombies Online
4 whip people Online
5 zip Demons Online
6 Bip People Online

All the new brands should take newID numbers although the orderby caluse in Dense_Rank assigns a different ID.

I don't mind changing the table structure if auto Increment or any other type of settings make me achieve this.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Cody
  • 21
  • 3

1 Answers1

1

Your database structure does not respect the fundamentals of the relational principles.

You need to have BrandName as a separate table with an ID that can be autogenerated.

The way to do that is :

SELECT DISTINCT IDENTITY(INT, 1, 1) AS BRAND_ID, BrandName
INTO BrandTable
FROM   MyTable:

ALTER TABLE BrandTable ADD PRIMARY KEY (BRAND_ID);

ALTER TABLE  MyTable ADD BRAND_ID INT;

UPDATE MT
SET    BRAND_ID = MT.BRAND_ID
FROM   MyTable AS MT
       JOIN BrandTable AS T 
          ON MT.BrandName = T.BrandName;

ALTER TABLE  MyTable ADD FOREIGN KEY (BRAND_ID) REFERENCES BrandTable (BRAND_ID);
SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • so which table is the final table? MyTable or BrandTable? I understood the need of two tables but I didn't fully understood the implementation. – Cody Aug 30 '22 at 22:08
  • You need to have the two tables do deal with – SQLpro Aug 31 '22 at 06:31