Sorting and updating rows using T-Sql

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

SQL Server 2005 Forums

Transact-SQL (2005)

Sorting and updating rows using T-Sql

Author

Topic

nn
Starting Member

2 Posts

Posted - 2008-10-15 : 22:35:06

Hi,
I have a table with 10 columns. There is an ID column which is 17 characters long (eg., '000763-P06-LAWNBL' ). There is a description column which is a varchar(30) datatype.

I need to sort the rows by the ID column and then the Description. If there is more than one of the same description for the a particular ID, I need to append the value in both the ID and the description field by a 01, 02 and 03, and so on.

How do I code this in T-SQL? Sorry, but I need this urgently.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-15 : 23:15:17

Can you give sample data and expected output?

nn
Starting Member

2 Posts

Posted - 2008-10-15 : 23:58:40

Sorry this is not formatted.

Input
Id
====
003718-P01-SIDEWK-SWK
003478-P01-PATHWY-PTH
003478-P01-SSOCCE-SOC
003478-P01-SSOCCE-SOC
003479-P01-PATHWY-PTH

Desc
========
Sidewalk #
Pathway #
Sport Field-Soccer #
Sport Field-Soccer #
Pathway #

output
------

Id
----
003738-P01-PATHWY-PTH001
004588-P01-PATHWY-PTH001
005850-P01-NTAREA-NAT001
005850-P01-PICNIC-PCN001
005854-P01-TURFS-TUR001
005985-P01-SIDEWK-SWK001
006008-P01-TURFS-TUR001
006068-P01-SFBASB-BSB001
006078-P01-PATHWY-PTH001
006149-P01-NTAREA-NAT001
006153-P01-NTAREA-NAT001
006159-P01-PATHWY-PTH001
006159-P01-SFBASB-BSB001
006162-P01-SSOCCE-SOC001
006162-P01-NTAREA-NAT001

Desc
------
Pathway #01
Pathway #01
Naturalized Area #01
Picnic Area #01
Turf #01
Sidewalk #01
Turf #01
Sport Field-Baseball #01
Pathway #01
Naturalized Area #01
Naturalized Area #01
Pathway #01
Sport Field-Baseball #01
Sport Field-Soccer #01
Naturalized Area #01
Pathway #01
Pathway #01
Pathway #01
Naturalized Area #01
Sport Field-Soccer #01
Lawn Bowling #01
Picnic Area #01
Naturalized Area #01
Sport Field-Football #01
Sport Field-Football #02

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 02:29:58

[code]SELECT id,Desc+ CAST(RIGHT('0'+COALESCE(tmp.Total,0),2) AS varchar(5))
FROM YourTable t
OUTER APPLY (SELECT COUNT(*) AS Total
FROM YourTable
WHERE Desc=t.Desc
AND LEFT(id,CHARINDEX('-',id)-1)=LEFT(t.id,CHARINDEX('-',t.id)-1))tmp[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources