I'm not certain I understand your requirements for this, however the following will return a row for each row in Table1.
Assuming [accountID] is an integer, you'd need to:
Select [lookup] = CONVERT(VARCHAR(30), accountID) + state + product
ORDER BY accountID + state + product;
You should always specify the schema (I used
dbo since it is the default). I've also added an
ORDER BY clause to make the output easier to understand.
It might make more sense to extend this to creation of the actual key field, as such:
-- add the [Lookup] column, make it nullable for now.
ALTER TABLE dbo.Table1 ADD [Lookup] VARCHAR(255) NULL;
-- insert the concatenated value into the [Lookup] column
SET [Lookup] = CONVERT(VARCHAR(30), accountID) + state + product
-- make the [Lookup] column NOT nullable so we can turn it into a primary key
ALTER TABLE dbo.Table1 ALTER COLUMN [Lookup] VARCHAR(255) NOT NULL;
-- create the clustered primary key constraint
ALTER TABLE dbo.Table1 ADD CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED ([Lookup]);
If the table
dbo.Table already has a clustered primary key, you'd either not make
Lookup a primary key, or perhaps you'd drop the existing primary key, and make
Lookup the new primary key.