selecting 2 columns into 1 - 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

SQL Server 2000 Forums

Transact-SQL (2000)

selecting 2 columns into 1

Author

Topic

rico_bano
Starting Member

35 Posts

Posted - 2007-05-10 : 06:09:17

Im trying to create a stored proc that selects 2 columns and puts them into one column in a temp table.

Below is the code ive tried, however i get an error stating

Column name 'classification_1' appears more than once in the result column list

insert into #temp_record(
record_id,
Knowledgebase_Id,
title,
[Text],
Date_Created,
classification_1,
classification_1,
document_type_name
)select
Id,
knowledgebase_Id,
Title,
[text],
Date_Created,
classification_1,
classification_2,
document_type_name

Has anybody got any ideas. I know i could run the query twice. Once for each classification, however i really want to avoid this of possible

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-10 : 06:12:04

classification_1 column appears twice in ur #temp_recods check after date_created column

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-10 : 06:17:19

i think u need like this...

insert into #temp_record(
record_id,
Knowledgebase_Id,
title,
[Text],
Date_Created,
classification_1,
document_type_name
)select
Id,
knowledgebase_Id,
Title,
[text],
Date_Created,
classification_1 + classification_2,
document_type_name

rico_bano
Starting Member

35 Posts

Posted - 2007-05-10 : 06:19:53

pbguy i know it appears twice. Thats cos i want classification_1 and classification_2 to be inserted into classification_1 of the temp table

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 06:26:29

I guess you want somthing like this


insert into #temp_record(
record_id,
Knowledgebase_Id,
title,
[Text],
Date_Created,
classification_1,
classification_1,
document_type_name
)
select
Id,
knowledgebase_Id,
Title,
[text],
Date_Created,
classification_1,
classification_2,
document_type_name
From YourTable 
Union All 
select
Id,
knowledgebase_Id,
Title,
[text],
Date_Created,
classification_2,
document_type_name
From yourTable

or may be somthing like this


insert into #temp_record(
record_id,
Knowledgebase_Id,
title,
[Text],
Date_Created,
classification_1,
classification_1,
document_type_name
)
select
Id,
knowledgebase_Id,
Title,
[text],
Date_Created,
classification_1 + '@' + classification_2,
document_type_name
From YourTable

What you are trying is not possible in SQL SERVER any version..

Chirag

http://chirikworld.blogspot.com/

rico_bano
Starting Member

35 Posts

Posted - 2007-05-10 : 06:38:35

ok thanks guys. one more question can you order rows without actually using a select

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 07:16:39

if you will not select the row then how will you order it.. and btw order by clause is only for the
select statement...

Chirag

http://chirikworld.blogspot.com/

rico_bano
Starting Member

35 Posts

Posted - 2007-05-10 : 07:26:20

yeh that was a silly question.

rico_bano
Starting Member

35 Posts

Posted - 2007-05-10 : 07:27:30

How about creating 2 temp tables. 1 to manipulate the data the other the hold the results once manipulated. Is creating 2 temp tables a bad idea

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 08:02:48

quote:
Originally posted by rico_bano

How about creating 2 temp tables. 1 to manipulate the data the other the hold the results once manipulated. Is creating 2 temp tables a bad idea

Can you provide the detail information, on what you are trying to manipulate???

Normal most of the manipulation can be achived by the Select Statements..so just provide us with the detail..

Chirag

http://chirikworld.blogspot.com/

rico_bano
Starting Member

35 Posts

Posted - 2007-05-10 : 09:01:01

here goes :-)

i have 2 columns in 1 table called classification_1 and classification_2. Im inserting them into a temp table. I want to be able to put the data from classification_1 and classification_2 into 1 column. Im not looking to merge the 2 e.g classification_1 + classification_2. Im looking to just put the results of each into 1 column. I can do this like this

insert into #temp_record(
classification
)
select classification_1 from some table

then

insert into #temp_record(
classification
)
select classification_2 from some table

however i do not want 2 seperate select / insert statements as i do some pagination on the primary key of the temp_table

I tried this

insert into #temp_record(
classification_1,
classification_1
)select
classification_1,
classification_2
)

which doesnt work. Any ideas?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 09:06:38

Well dude..

Did you follow my earlier post, it was the samthing in one SQL Statment... you dont have to write 2 differents insert statments..


insert into #temp_record
(
   classification
)
select classification_1 from some table
Union 
select classification_2 from some table

OR 
Just make sure that classification_1 and Classification doesnt go above 8000 characters else it will generate error.. 

insert into #temp_record
(
   classification
)
select classification_1 + classification_2 from some table

Chirag

http://chirikworld.blogspot.com/

rico_bano
Starting Member

35 Posts

Posted - 2007-05-10 : 09:24:58

thanks chiragkhabaria thats sorted it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources