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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy