November 7, 2007 at 1:28 pm
I need assistance with creating a column in my table that shows a row number.
The reason for this is that my table data currently does not have a unique key, and there are duplicate rows.
Please let me know how I can do this in SQL. I am currently building a model within Cognos Framework, and within this tool is the ability to create Query Subject....this allows the use of SQL SELECT statements.
Below is an example of my table:
Select
GENERAL_CONTACTS.PROD_CATEGORY ,
GENERAL_CONTACTS.PROD_CATEGORY_DESC ,
GENERAL_CONTACTS.PROD_TYPE ,
GENERAL_CONTACTS.PROD_TYPE_DESC ,
GENERAL_CONTACTS.RESPONSE_CDE ,
GENERAL_CONTACTS.RESPONSE_CDE_DESC ,
GENERAL_CONTACTS.SUB_RESPONSE_CDE ,
GENERAL_CONTACTS.SUB_RESPONSE_DESC ,
GENERAL_CONTACTS.HIST_DATE AS WEEK_DTE
From
[CEC 1.1].GENERAL_CONTACTS as GENERAL_CONTACTS
So I am hopeful that I can do this.
November 7, 2007 at 2:44 pm
First you would have to find a way of eliminating those duplicates that you mentioned.
For that, you need to run a SELECT statement to catch records where the same value is repeated in a field, for example:
SELECT CompanyName, COUNT(CompanyName) AS [CountofCompanyName]
FROM dbo.Test
GROUP BY CompanyName
HAVING COUNT(CompanyName) > 1
In this case, I'm looking for records where there's duplicates in the CompanyName field of the Test table.
Hope that helps,
-Simon Doubt
November 7, 2007 at 3:01 pm
The duplicates are acceptable....there may be a case where a an entry is submitted twice....and is used for counting purposes.
November 7, 2007 at 3:29 pm
In that case, you should create a new table with a new column something like this:
CREATE TABLE dbo.NewTable
(
ColRowID INT NOT NULL IDENTITY (1,1)
....
)
Where ... is the other columns, which shoud be the same as the columns in your existing table, i.e.
PROD_CATEGORY ,
PROD_CATEGORY_DESC ,
PROD_TYPE ,
PROD_TYPE_DESC ,
RESPONSE_CDE ,
RESPONSE_CDE_DESC ,
SUB_RESPONSE_CDE ,
SUB_RESPONSE_DESC ,
HIST_DATE AS WEEK_DTE
Then, use an INSERT ... SELECT statement to dump all of your existing data into the new table. As the rows are inserted, a unique ascending value (starting at 1) will be assigned to each row/record.
The syntax will look something like this:
INSERT dbo.NewTable
(
PROD_CATEGORY ,
PROD_CATEGORY_DESC ,
PROD_TYPE ,
PROD_TYPE_DESC ,
RESPONSE_CDE ,
RESPONSE_CDE_DESC ,
SUB_RESPONSE_CDE ,
SUB_RESPONSE_DESC ,
HIST_DATE AS WEEK_DTE
)
SELECT * FROM
[CEC 1.1].GENERAL_CONTACTS
Note that you DO NOT include the ColRowID in the field list for your INSERT ... SELECT statement.
Good luck,
-Simon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply