how to add columns in sql?

Question

1.00/5 (1 vote)

See more:

SQL

prefix          srno   
TS-SIV-VS-	1
TS-SIV-VS-	4
TS-SIV-VS-	2

i want to combine both column into one column like in below format

SQL

prefix          srno   invoice
TS-SIV-VS-	1       TS-SIV-VS-1   
TS-SIV-VS-	4       TS-SIV-VS-4
TS-SIV-VS-	2       TS-SIV-VS-2

Posted 13-May-15 2:28am

Member 11337367

Updated 2-Apr-18 21:09pm

King Fisher

v2

Add a Solution

Comments

_Asif_ 13-May-15 8:47am

Are you using MS SQL?

King Fisher 13-May-15 8:58am

Re-design your Table?

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2015-05-13T02:36:00

Solution 1

1. Add column using ALTER TABLE [table] ADD COLUMN [column]
2. Update new column with combined values

Posted 13-May-15 2:36am

Kornfeld Eliyahu Peter

Comments

Member 11337367 13-May-15 8:48am

update sal set invoice=prefix+srno
but this is giving error...

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

Kornfeld Eliyahu Peter 13-May-15 8:52am

When you are combining different types of columns you have to convert them to something common, otherwise SQL will do it - like in your case...
In your case srno is the last element of the expression so SQL will try to convert all the other elements to the type of rsno - float...It is impossible to convert the value of prefix to float!!!
Try: update sal set invoice = prefix + cast(srno as nvarchar)

_Asif_ · Answer 2 · 2015-05-13T02:53:00

Solution 2

Another approach could be like

CREATE TABLE TEST
(
    prefix VARCHAR(10),
    SRNO   INT ,
    INVOICE AS prefix + CAST(SRNO AS VARCHAR(10))
);

INSERT INTO TEST(prefix, SRNO)
SELECT 'TS-SIV-VS-',	1
UNION ALL
SELECT 'TS-SIV-VS-',	2
UNION ALL
SELECT 'TS-SIV-VS-',	3

SELECT * FROM TEST

Posted 13-May-15 2:53am

_Asif_

riteshshrvstv · Answer 3 · 2015-05-14T03:52:00

Solution 3

SQL

select prefix,srno,prefix+ CAST(srnoAS VARCHAR(10)) as invoice into TableName from
(
SELECT 'TS-SIV-VS-' as  a,    1 as b
UNION ALL
SELECT 'TS-SIV-VS-',    2
UNION ALL
SELECT 'TS-SIV-VS-',    3
)  t

Posted 14-May-15 3:52am

riteshshrvstv

Updated 14-May-15 3:54am

v2

Vinay Haravi · Answer 4 · 2015-05-15T00:11:00

Are you asking for creating third column in the table ?
or If you want only select query to view the result, you can use this following query.

SQL

WITH CTE as
(
select 'TS-SIV-VS-' as Prefix, 1 as srno
union all
select 'TS-SIV-VS-' , 4
union all
select 'TS-SIV-VS-' , 1
)

select Prefix,Srno,prefix+cast(srno as nvarchar(2)) Invoice from cte