sql data manipulation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql data manipulation

Author

Topic

jay_sql
Starting Member

3 Posts

Posted - 2009-01-31 : 17:48:41

I got a table named tblAlerts. It has two columns viz., ALERTS, ROW_TEXT and has many rows in the table. The sample of few rows is shown below

ALERTS ROW_TEXT
------- --------
1010748 Yes
1010748 No
1010748 N/A
1010749 Yes
1010749 No
1010749 N/A
1010751 Yes
1010752 Yes
1010752 No
1010752 N/A
1010753 Yes
1010754 Yes
1010754 No
1010754 N/A
1010755 Not OK
1010755 N/A
1010757 11111
1010757 22222
1010757 33333

Now, I need to do sql manipulation and produce the table as below with three columns viz., ALERTS, ROW_TEXT and ROW_ID. Basically ROW_ID is evolved from ALERTS and it is just a sequence number relevant to ALERTS. As ALERTS 1010748 has three rows, so in the ROW_ID you have numbers 1,2,3.... similarly for ALERTS 1010753 has only one row, thus the ROW_ID has 1. For ALERTS 1010754 has four rows, thus the ROW_ID has 1,2,3,4.

ALERTS ROW_TEXT ROW_ID
------ -------- ----------
1010748 Yes 1
1010748 No 2
1010748 N/A 3
1010749 Yes 1
1010749 No 2
1010749 N/A 3
1010751 Yes 1
1010752 Yes 1
1010752 No 2
1010752 N/A 3
1010753 Yes 1
1010754 Yes 1
1010754 No 2
1010754 N/A 3
1010754 None 4
1010755 Not OK 1
1010755 N/A 2
1010757 11111 1
1010757 22222 2
1010757 33333 3

Thanks,
Jay
Thanks,
Jay

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-31 : 18:33:03

In SQL Server 2005 or higher:


select
ALERTS,
ROW_TEXT,
ROW_NUMBER() OVER (partition by ALERTS order by ALERTS) AS ROW_ID
from tblAlerts

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-31 : 21:11:32

In SQL 2000

Declare @t table
(ALERTS int,ROW_TEXT varchar(10))

-- Preparing Sample Data
Insert @t
Select 1010748, 'Yes' union all
Select 1010748, 'No'  union all
Select 1010748, 'N/A' union all
Select 1010749 ,'Yes' union all
Select 1010749 ,'No' union all
Select 1010749 ,'N/A' union all
Select 1010751 ,'Yes' union all
Select 1010752, 'Yes' union all
Select 1010752, 'No' union all
Select 1010752, 'N/A' union all
Select 1010753, 'Yes' union all
Select 1010754, 'Yes' union all
Select 1010754, 'No' union all
Select 1010754, 'N/A' union all
Select 1010755, 'Not OK' union all
Select 1010755, 'N/A' union all
Select 1010757, '11111' union all
Select 1010757, '22222' union all
Select 1010757, '33333'

-- Make Unique
Select IDENTITY(int,1,1) as ALERTID,* into #K 
from @T

Select ALERTS,ROW_TEXT,(Select Count(*) from #K Where ALERTS = Z.ALERTS
and ALERTID <= Z.ALERTID) as ROW_ID
from #K Z

--output
1010748	Yes	1
1010748	No	2
1010748	N/A	3
1010749	Yes	1
1010749	No	2
1010749	N/A	3
1010751	Yes	1
1010752	Yes	1
1010752	No	2
1010752	N/A	3
1010753	Yes	1
1010754	Yes	1
1010754	No	2
1010754	N/A	3
1010755	Not OK	1
1010755	N/A	2
1010757	11111	1
1010757	22222	2
1010757	33333	3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 01:01:20

quote:
Originally posted by webfred

In SQL Server 2005 or higher:

select
ALERTS,
ROW_TEXT,
ROW_NUMBER() OVER (partition by ALERTS order by ALERTS) AS ROW_ID
from tblAlerts

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

will this make sure Yes will always be given value of 1,No 2,...

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-01 : 01:25:18

Yes it will.

quote:
Originally posted by visakh16

quote:
Originally posted by webfred

In SQL Server 2005 or higher:

select
ALERTS,
ROW_TEXT,
ROW_NUMBER() OVER (partition by ALERTS order by ALERTS) AS ROW_ID
from tblAlerts

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

will this make sure Yes will always be given value of 1,No 2,...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 01:32:33

how? the code's ordering by ALERTS field itself. so on what basis does it apply rownumbering over a ALERT value group? how can we always guarantee that yes will get 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-01 : 11:22:16

Hi Visabot