Automatically appending the value to column

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)

Automatically appending the value to column

Author

Topic

thiyait
Yak Posting Veteran

70 Posts

Posted - 2007-05-22 : 02:20:54

Hi all

i have query

select col1,col2 from table1

i need to append the incremental value to the col2 for each row

Here is the table data:-

col1, col2

Expecting query result like below

col1, col2
---------
0001 aus1
0002 usa2
0003 Bel3

Any one help me...........

Thanks in advance
thiya

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-05-22 : 02:29:38

the "incremental value" is the value returned in col1 correct?

-ec

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 02:31:12

Where do you want to show your data?
If you use front end application, you can easily do numbering there
or if you want to append col1 values to col2, then try using

Select col1, col2+cast(cast(col1 as int) as varchar(4)) from table

Madhivanan

Failing to plan is Planning to fail

thiyait
Yak Posting Veteran

70 Posts

Posted - 2007-05-22 : 02:44:23

Hi eyechart & madhivanan

Thanks for quick reply,

i didnt mean to concatinate the first column with second column.i want display to front end applicaition with appended incremental value in column 2

like this:

col1, col2
---------
0235 aus1
0023 usa2
0345 Bel3
0345 har4

Thanks in advance

thiya

thiyait
Yak Posting Veteran

70 Posts

Posted - 2007-05-22 : 02:47:27

hey, ihave missed the table data in previous replay

This is the table values

col1, col2
---------
0235 aus
0023 usa
0345 Bel
0345 har

expected result like this
col1, col2
---------
0235 aus1
0023 usa2
0345 Bel3
0345 har4

Thanks in advance
thiya

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-22 : 02:52:55

What is the order for incrementing the value?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 02:57:58

quote:
Originally posted by thiyait

Hi eyechart & madhivanan

Thanks for quick reply,

i didnt mean to concatinate the first column with second column.i want display to front end applicaition with appended incremental value in column 2

like this:

col1, col2
---------
0235 aus1
0023 usa2
0345 Bel3
0345 har4

Thanks in advance

thiya

What is your front end application?

Here is the general approach


Dim Sno as integer
Sno=1
While not Rs.eof
      print Rs("col1")
      print Rs("col2")+Sno
      Sno=Sno+1
      Rs.moveNext
Loop

Madhivanan

Failing to plan is Planning to fail

thiyait
Yak Posting Veteran

70 Posts

Posted - 2007-05-22 : 03:16:25

HI Madhivanan,

Thanks,i would like to have in single query

Thanks in advance
thiya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 03:56:11

<<
i want display to front end applicaition with appended incremental value in column 2
>>

If the above is the case, then the code I have given is front end code
You didnt answer to my question on which your front end application is.
When it can be done easily in your front end, then why do you want to do it in query?

Madhivanan

Failing to plan is Planning to fail

thiyait
Yak Posting Veteran

70 Posts

Posted - 2007-05-22 : 10:38:19

Hi,

I have given the part of requirement in the query..table has more than lakhs of record.its not wise to manipulate on frontend for such huge amount

So,please any one help me to solve this issue.

thanks in advance
thiya

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-22 : 10:52:16

quote:
Originally posted by thiyait

Hi,

I have given the part of requirement in the query..table has more than lakhs of record.its not wise to manipulate on frontend for such huge amount

So,please any one help me to solve this issue.

thanks in advance
thiya

try this ...

Create Table #Temp
(Num Numeric
,TName VarChar(10))

Insert Into #Temp Values (111, 'A')
Insert Into #Temp Values (222, 'B')
Insert Into #Temp Values (333, 'C')
Insert Into #Temp Values (444, 'D')

Select Num, TName + Convert(VarChar(3), SrNo) As New_Col From (
Select (Select Count(*) From #Temp As A Where A.Num <= B.Num) As SrNo, Num, TName From #Temp As B) As Test

Drop Table #Temp

Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 11:00:24

quote:
Originally posted by mahesh_bote

quote:
Originally posted by thiyait

Hi,

I have given the part of requirement in the query..table has more than lakhs of record.its not wise to manipulate on frontend for such huge amount

So,please any one help me to solve this issue.

thanks in advance
thiya

try this ...

Create Table #Temp
(Num Numeric
,TName VarChar(10))

Insert Into #Temp Values (111, 'A')
Insert Into #Temp Values (222, 'B')
Insert Into #Temp Values (333, 'C')
Insert Into #Temp Values (444, 'D')

Select Num, TName + Convert(VarChar(3), SrNo) As New_Col From (
Select (Select Count(*) From #Temp As A Where A.Num <= B.Num) As SrNo, Num, TName From #Temp As B) As Test

Drop Table #Temp

Mahesh

Never do this type of Serilazation using sql
If there are thousands of millions of rows in the table, the query will take long time to finish

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 11:06:27

quote:
Originally posted by thiyait

Hi,

I have given the part of requirement in the query..table has more than lakhs of record.its not wise to manipulate on frontend for such huge amount

So,please any one help me to solve this issue.

thanks in advance
thiya

Is there a case when you need to show lakhs of records in front end?
Which is your front end and how you are sending data to it?
How will you display data in front end?
Why do you think generating serial no at front end is difficult and ineffecient?
It is very very easy to show data with serial no in front end
There is no point in doing this in sql if you want to show them in front end

Madhivanan

Failing to plan is Planning to fail

thiyait
Yak Posting Veteran

70 Posts

Posted - 2007-05-23 : 06:10:25

Hi mahesh,

Thanks for the reply,i have one more question on this
if first column not be the numeric.how to solve this.

col1 col2
----------
asdf A
gfds B
lkjh C
ouyt D

Mathi,
people here using the own custom made framework to display the data. There is no option for front end manipulation

hope now you clear

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-23 : 06:29:04

<<
people here using the own custom made framework to display the data
>>

What framework is that?
When data are displayed using framework, isnt it possible simple add row id there as I have specified in my second reply?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-23 : 06:35:06

quote:
Originally posted by thiyait

Hi mahesh,

Thanks for the reply,i have one more question on this
if first column not be the numeric.how to solve this.

col1 col2
----------
asdf A
gfds B
lkjh C
ouyt D

Mathi,
people here using the own custom made framework to display the data. There is no option for front end manipulation

hope now you clear

As you use SQL Server 2005, you can make use of row_number()

Select col1,col2+cast(row_id as varchar(5)) as col2 from
(
select col1,col2,row_number() over (order by col1 asc) as row_id from yourtable)
T

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources