Sorting within a record Vs updating

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 within a record Vs updating

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-07-27 : 10:52:45

hello

i have a table structured like this

CREATE TABLE [dbo].[COST](
[ID] [varchar](5) NOT NULL,
[LOC1] [varchar](2) NOT NULL,
[LOC2] [varchar](2) NOT NULL,
[LOC3] [varchar](2) NOT NULL
CONSTRAINT [PK_COST] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[LOC1] ASC,
[LOC2] ASC,
[LOC3] ASC,

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

insert into COST select 'AAAAA' , 'NU' 'KL' , '--'
insert into COST select 'AAAAA' , 'RR' 'KL' , 'UE'
insert into COST select 'AAAAA' , 'KL' 'KK' , '--'
insert into COST select 'AAAAA' , '--' 'KL' , 'KG'
insert into COST select 'AABBB' , 'NU' 'KL' , '--'
insert into COST select 'AABBB' , 'BA' 'MS' , 'UE'
insert into COST select 'AACCC' , 'KK' 'KL' , '--'
insert into COST select 'AACCC' , '--' 'UE' , '--'

the above is current structure of my table

i want to sort within a record for the LOC1, LOC2 and LOC3 variables

This is my sorting order NU RR UE MS BA KC KG KK KL -- with highest precedence being NU and lowest being --.

so 1st record in above insert statement would remain same
the 2nd would be UE, RR , KL
3rd would be KK, KL, -- and so on

Can this be done in a function or stored procedure?

i am trying to build a update statement but i think it would look big and cumbersome

any help.... Thanks

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-07-27 : 11:35:28

sorry, the second insert would be RR, UE, KL

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-07-27 : 14:09:28

Never mind....i got it

i replaced the modifiers with numbers starting from 1 to 10 and used pivot and unpivot to sort it ascendingly and rearrange it....

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources