Combining Two Tables Via T-SQL

Hello,

I have two tables that have different column names so I can not combine them using UNION statement. Is there a way to combine two tables and have all the columns from both tables.

Thank you for your help!

UNION does not require that the column names be the same, only that the datatypes are similar enough to combine. See this Example:

Code Snippet

USE Northwind
GO

SELECT CompanyName FROM Customers
UNION
SELECT FirstName + ' ' + LastName FROM Employees

CompanyName
-
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Andrew Fuller
Anne Dodsworth
Antonio Moreno Taquería
Around the Horn
Berglunds snabbk?p
Bill Smith
...

|||

Donnie:

The column names do not have to be the same for you to union together two tables. If you are trying to union together to tables column-for-column, it is sufficient to have:

The number of columns the same The datatypes of corresponding columns be the same|||

Can you expand on what you are trying to accomplish?. As long as the data type of the columns be the same, including collation, then there is no problem using union or "union all".

declare @.t1 table(c1 int, c2 int)

declare @.t2 table(c3 int, c4 int)

insertinto @.t1 values(1, 2)

insertinto @.t2 values(3, 4)

select c1, c2 from @.t1

union all

select c3, c4 from @.t2

AMB

|||

Are you certain that it is a UNION that you need to perform, and not a JOIN?

A JOIN will allow you to return all columns from both tables as individual columns within the same resultset (i.e. merge the data vertically), like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

--

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

--

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1a Column2a Column3a Column1b Column2b Column3b

--

1 T1C2R1 T1C3R1 1 T2C2R1 T2C3R1

2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2

3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3

SELECT t1.Column1a,
t1.Column2a,
t1.Column3a,
t2.Column1b,
t2.Column2b,
t2.Column3b
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column1a = t2.Column1b

A UNION will allow you to horizontally merge the data from both tables, like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

--

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

--

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1 Column2 Column3

-

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

SELECT t1.Column1a AS Column1,
t1.Column2a AS Column2,
t1.Column3a AS Column3

FROM Table1 t1

UNION ALL

SELECT t2.Column1b,
t2.Column2b,
t2.Column3b

FROM Table2 t2

Chris|||

Some kind of join is probably a good idea since I want to join matching rows as well as non matching rows from both tables. Maybe, a full join would be good but I don't want duplicates. Please see my example of the output. What do you think?

Thanks for your help!

Table 1 - Sample Data Column1a Column2a Column3a 1 T1C2R1 T1C3R1 2 T1C2R2 T1C3R2 3 T1C2R3 T1C3R3 5 T1C2R5 T1C3R5 Table 2 - Sample Data Column1b Column2b Column3b 4 T2C2R4 T2C3R4 2 T2C2R2 T2C3R2 3 T2C2R3 T2C3R3 6 T2C2R6 T2C3R6 Ouptput: Column1a Column2a Column3a Column1b Column2b Column3b 1 T1C2R1 T1C3R1 NULL NULL NULL 2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2 3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3 NULL NULL NULL 4 T2C2R4 T2C3R4 5 T1C2R5 T1C3R5 NULL NULL NULL NULL NULL NULL 6 T2C2R6 T2C3R6

|||

Yes, a full join should work for you.

SELECT a.Column1a, a.Column2a, a.Column3a,

b.Column1b, b.Column2b, b.Column3b

FROM Table1 a FULL JOIN Table2 b ON (a.Column1a = b.Column1b)

There should not be any duplicates in the result set.

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure?
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.com
Aditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure?
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com
|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatenation, or expose a
computed column in the table. That way the data doesn't get out of sync in case someone modifies the
first name or last name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How will
> i do it?Is it possible with a Stored Procedure?
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com
|||Thank you very much Uri, but there is one problem, there are five null spaces
in between the fullname, how do i remove these?
"Uri Dimant" wrote:

> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>
|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:

> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concatenation, or expose a
> computed column in the table. That way the data doesn't get out of sync in case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>
|||I don't know what a "null space" is. Are you saying that you can have NULL in either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to go on (CREATE TABLE,
INSERT).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...[vbcol=seagreen]
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables
sqlsql

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure'
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.comAditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatena
tion, or expose a
computed column in the table. That way the data doesn't get out of sync in c
ase someone modifies the
first name or last name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How wil
l
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Thank you very much Uri, but there is one problem, there are five null space
s
in between the fullname, how do i remove these?
"Uri Dimant" wrote:

> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:

> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concate
nation, or expose a
> computed column in the table. That way the data doesn't get out of sync in
case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>|||I don't know what a "null space" is. Are you saying that you can have NULL i
n either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to
go on (CREATE TABLE,
INSERT).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...[vbcol=seagreen]
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
>|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure'
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.comAditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatenation, or expose a
computed column in the table. That way the data doesn't get out of sync in case someone modifies the
first name or last name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Thank you very much Uri, but there is one problem, there are five null spaces
in between the fullname, how do i remove these?
"Uri Dimant" wrote:
> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
> > LASTNAME, ADDRESS, TELEPHONE,...
> > Now i have added a new column in the table, naming it FULLNAME. In this i
> > want to insert the FIRSTNAME and the LASTNAME
> > combined with a space in between, the rest all remaining the same. How
> > will
> > i do it?Is it possible with a Stored Procedure'
> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> >
> > You can also email me at : aditya595@.yahoo.com
>
>|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:
> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concatenation, or expose a
> computed column in the table. That way the data doesn't get out of sync in case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
> > LASTNAME, ADDRESS, TELEPHONE,...
> > Now i have added a new column in the table, naming it FULLNAME. In this i
> > want to insert the FIRSTNAME and the LASTNAME
> > combined with a space in between, the rest all remaining the same. How will
> > i do it?Is it possible with a Stored Procedure'
> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> >
> > You can also email me at : aditya595@.yahoo.com
>
>|||I don't know what a "null space" is. Are you saying that you can have NULL in either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to go on (CREATE TABLE,
INSERT).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
>> Assuming no nulls in firstname or lastname column:
>> UPDATE tbl
>> SET FULLNAME = FirstName + ' ' + LastName
>> But, why do you need to store this? You can create a view with the concatenation, or expose a
>> computed column in the table. That way the data doesn't get out of sync in case someone modifies
>> the
>> first name or last name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
>> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
>> > LASTNAME, ADDRESS, TELEPHONE,...
>> > Now i have added a new column in the table, naming it FULLNAME. In this i
>> > want to insert the FIRSTNAME and the LASTNAME
>> > combined with a space in between, the rest all remaining the same. How will
>> > i do it?Is it possible with a Stored Procedure'
>> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
>> >
>> > You can also email me at : aditya595@.yahoo.com
>>|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
George
Assuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
David Portas
SQL Server MVP
|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
GeorgeAssuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
David Portas
SQL Server MVP
--|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining two columns as third column

Maybe a dumb question or me being burnt out.
The people that wrote the DB I am working on were not the brightest in the
world.
They created an inventory item with the manufacturer post pended to the
number.
Example.
81335C12 AMP
Where 81553C12 is the part number and AMP is the abbreviation for the
Manufacturer.
Please don't ask me why.
But I am pushing data to the DB and I need to combine the part number from
the new DB which is kept in a column by itself and then concatenate the
Manufacturer code which is kept in a column by itself in to one column on an
append query.
It is possible or do I need to do an intermedate table?
It is partnumber space manufacturercode. That is there primary key.
Suggestions appreciated
GeorgeAssuming this is just an INSERT and assuming you don't have any NULLs to
worry about, could this be what you're looking for:
INSERT INTO NewTable (part_number, ...)
SELECT partnumber+' '+manufacturercode, ...
FROM OtherTable
--
David Portas
SQL Server MVP
--|||Thanks, more than you can know, brain burnt out today.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:fLOdnQ8cJ5zoOQfcRVn-sw@.giganews.com...
> Assuming this is just an INSERT and assuming you don't have any NULLs to
> worry about, could this be what you're looking for:
> INSERT INTO NewTable (part_number, ...)
> SELECT partnumber+' '+manufacturercode, ...
> FROM OtherTable
> --
> David Portas
> SQL Server MVP
> --
>

Combining results in Comma delimitered strings

I know this has been addressed before but I can't find it...
I have a table with with a column called PersonId. I want a query that will return all the PersonId's as a comma delimited string...
Anyone able to help?DECLARE @.commadelimitedthisisanannoyingstringname VARCHAR(8000)

SELECT @.thatstringupthereyouregoingtonottypethis = ''

SELECT @.String = PersonId + ', ' + @.String
FROM Person

SELECT LEFT(@.String, LEN(@.String)-1)

or something like that|||Thanks, that seems to work,... I didn't think it would but some how it does...|||Thanks, that seems to work,... I didn't think it would but some how it does...

It should have worked. :) Why would you not think it would, just curious.|||I would have thought that for that structure to work there would have had to be some sort of loop or something...

I thought I had seen it done using some other function like coalesce or something but when I read the help it didn't look right...

Hmmm,... actually thinking about it now it makes sense... basically it ignores what if selected in the table select and just selects the final built up string...

Seems a little inefficient, is there a better way??|||Actually, if you do a search here or on www.sqlteam.com, you'll find all kinds of ways to do it. :) You can do it with a function using COALESCE. I think for a single string though, this one is pretty efficient.|||Come on, derrick, NOBODY here thought that would work the first time we saw it months back! It's probably one of the most popular tricks on the forum!|||?? You're joking right? I have scripts going way back using that.|||I know that folks use the living stuff out of this construct, but it shouldn't work. It actually violates the SQL-92 standard, since the column value is derived iteratively. My guess is that once the standards committee realizes that this flaw exists in a widely used SQL dialect, they'll add a test for it to the test suite and shortly thereafter Microsoft will either eliminate the behavior or make it switch dependant.

-PatP|||Yup, it's a nifty little thing that has gotchya's people don't think about, and as always tend to overuse it. Like with everything else that is non-standard, when it comes to an end, a lot of these guys are gonna be screaming bloody hell, but it's only because they were lazy at the beginning, though not lazy enough to prevent it right there, and have a beer ;)|||Yeah, but it is lots of fun to watch them running around, screaming to the four winds when the database engine behavior is fixed and their code breaks. As you are fond of observing, those who ignore history are doomed to repeat it!

-PatP|||Okie then guys,... what is the right way to do it? To be honest I have actually implemented a different solution as I didn't get a reply in time so it isn't going to cause me any problems but I would be interested in knowing if there is a proper way or not...|||The proper way would be to reorg your front-end and not display a comma-separated string or PersonIDs, but rather display a grid of such, where you can also include PersonName and other pertinent information. It's a christomatic drill-down approach where you see the header, click on Detail button and get everything that is associated with the highlighted header record. This IS the right approach, without trying to trick life and SQL engine. But if you continue, what are you gonna tell your users when they start getting partial PersonID at the end of the string, because the total length of the returned string exceeded 8000 character?|||Well that answer gains zero points for usefullness...

No offense meant. For the purposes of the project I am doing I need a comma delimited string of all the id's. This is not for displaying but for internal processing. A string may not be the best option but at this point it time is the most flexible and the project at hand. The truth is that there is no front in for the problem I am working on. It's a logic problem that I want to solve. Once it is solved I might decide that it is no use or I might decide that there is a front end requirement but at this point there is not.

Now while I agree that I probably don't want SQL to return a comma delimited string because it is a misrepresentation of the actual data I do want to know what is the best way of getting sql to return a comma delimited string so I know what I am talking about when I rule it out as an option.|||No matter how the string is getting formed, it's still limited to the total length of 8000 character (wonder how many times I tried to hint it?:rolleyes: )

Why don't you tell us what the app is for, and why it needs a comma-delimited listing of PersonIDs? Someone (maybe me) would be able to come up with an alternative, hey?! ;)|||I think the answer to your hint question is once (in this post anyway).

The string is used for determining individual branches in a family tree. The processing is all done using asp/vbscript. An array might be better then a string but because you are shuffling data into and out of other stings/arrays at this point a string is the best contruct to test the theory and determine it's usefulness.

At this point conversion of a recordset to a string is simplier then a recordset to an array.

The actually processing overhead of using a string is probably higher but until I determine the usefulness of the function their is little point moving to an array which would be more complex at this point.

Before you ask, yes, you are stuck with asp and vbscript. It can't go to a vb component and it can't upgrade to asp.Net.

Knock yourself out.|||Hey-hey-hey, with that kind of attitude, YOU knock yourself out, not me, alright?! I was just trying to help you (which is why I choose to post here), but as you MIGHT have noticed, it's NOT my problem. If you think you're better than that, - KNOCK YOURSELF OUT, do yourself a favor.

EDITED: and BTW, I hinted on VARCHAR limitation at least twice. I simply didn't think that such an obvious thing as this needs to be mentioned...Obviously it does, hey?!|||*sigh* why is it that people are easily offended...

Look, I appreciate what you are saying and I appreciate your input, the "knock yourself out" line is a standard line from where I come from to say "go for it and good luck". I'm sorry it offended you.|||Fair dinkum. :cool:|||WHAT WAS WRONG WITH THIS ANSWER? Tel me straight, please, I I promise I will take a serious consideration over how I post my answers...The proper way would be to reorg your front-end and not display a comma-separated string or PersonIDs, but rather display a grid of such, where you can also include PersonName and other pertinent information. It's a christomatic drill-down approach where you see the header, click on Detail button and get everything that is associated with the highlighted header record. This IS the right approach, without trying to trick life and SQL engine. But if you continue, what are you gonna tell your users when they start getting partial PersonID at the end of the string, because the total length of the returned string exceeded 8000 character?|||What's wrong with the answer is it doesn't actually answer the question.

The question is, is there a proper way to create a comma delimited string in the manner described within SQL (and if there is, what is it).

It is not, how should I build my application so that I do not have to use a comma delimited string, nor is it what restrictions will I put in place by using a comma delimited string.

I'd be quite happy if some one answered "there is no 'proper' way to generate a comma delimited string from sql". I am aware that there are going to be limitations, but in some cases (not this one) the limitation may not come into play (depending on data and data structures).|||Hey, have it your way. I hope the the limitation pointed out is not gonna be a problem for you ;)|||Eventually it would be, but since this was only to test a theory it's not going to and like I said previously I have actually gone about it a different way, but it would still be interesting to know if there is a "proper" way to do it...

An example of where it might be useful is when you want a comma delimited list of the months that are stored in a particular table. You can be certain that the length of the string will not exceed 8000 characters...

I'm not sure where else it might be useful but there are bound to be others.

Thanks for your input.sqlsql

Combining Multiple rows into 1 row x 1 column

I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?

Employee Name John Mike Dale

There are a number of ways to complete what you wish. Some features that you can take advantage of include:

select with CASE and MAX

User defined functions

SELECT with FOR XML syntax (better in SQL 2005 than SQL 2000)

PIVOT

Transact SQL SELECT extensions|||Very Cool, Thanks.

|||

Just for the sake of completeness, this can also be achieved via cursors:

Assuming #Employee temp table contains the data.

declare @.sql varchar(200), @.k int

set @.sql = ''

set @.k = 0

declare @.EmpName varchar(50)

declare abc cursor for select EmployeeName from #Employee

open abc

fetch next from abc into @.EmpName

while @.@.FETCH_STATUS = 0

begin

if @.k > 0 set @.SQL = @.SQL +', '

set @.SQL = @.SQL + @.EmpName

set @.k = @.k +1

fetch next from abc into @.EmpName

end

close abc

deallocate abc

SELECT @.SQL as Employees

Drop Table #Employee

|||

Code Snippet

declare @.Output varchar(max)

select @.Output = isnull(@.Output + ', ' + [Employee Name] , [Employee Name] )

from MyTable

select @.Output as [OneColumn]

combining multiple rows in 1 row

how can i get a 1 row result set having multiple rows joined into 1 row
if i have 1 column having 5 rows i want to use a select statement that selects all rows joined into 1 row (results seperated by a comma for example)
thx
samhamWant to show us the query? What would make them join together?|||see Using COALESCE to Build Comma-Delimited String (http://sqlteam.com/item.asp?ItemID=2368)

rudy
http://r937.com/|||He wants to combine multiple rows..

And you don't use COALESCE to build a comma delimited srting..

Is used so that any null value in the string does not blow away the results...

It's the ability to do SELECT @.x = @.X + col1

like...

DECLARE @.x varchar(8000)
SELECT @.x = ISNULL(@.x,'') + ISNULL(FirstName,'') FROM Employees
SELECT @.x

The coalesec trick allows you to eliminmate commas if the value in the column is Null

so you dont get 1,,2,3,4,,5|||He wants to combine multiple rows yes, he wants the values from multiple rows to be put into a comma-delimited string
And you don't use COALESCE to build a comma delimited srting damned straight on that one, i certainly don't, i would never do it that way -- in fact, i would probably just never do it
The coalesec trick allows you to eliminmate commas if the value in the column is Null yes, that's correct, that's what it does for the first row

rudy|||thx guys that's exactly what i wanted

i'll use the code from the article

DECLARE @.EmployeeList varchar(100)

SELECT @.EmployeeList = COALESCE(@.EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @.EmployeeList
--Results--

---
1, 2, 4

Combining multiple columns into one column.

Combing multiple columns like [LastName],[FirstName] and
[MiddleName]into one column named as [Name] is very simple in Access,
but how will i do that in SQL? Any suggestions? PLease?
Thanks in advance,
Geri
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You have to add another column, update with existing data,
and then drop existing columns.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Geri Gavertz" <gerific@.yahoo.com> wrote in message
news:ezPCuV8GFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Combing multiple columns like [LastName],[FirstName] and
> [MiddleName]into one column named as [Name] is very simple in Access,
> but how will i do that in SQL? Any suggestions? PLease?
> Thanks in advance,
> Geri
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||same as in Access
Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
Madhivanan|||same as in Access
Select LastName+' ' + FirstName + ' ' + MiddleName as Name from Table
Madhivanan|||<madhivanan2001@.gmail.com> wrote in message
news:1109400766.869281.200560@.o13g2000cwo.googlegroups.com...
> same as in Access
> Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
> Madhivanan
>
You might want to wrap them in IsNull so that a NULL in one of the columns
doesn't NULL out the entire result:
Select IsNull(FirstName, '') + ' ' + IsNull(MiddleName, '') + ' ' +
IsNull(LastName, '') As FullName from MyTable
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company

Combining multiple columns into one column.

Dear all,
I am having a problem on how to merge 3 columns into one column. I have a
columns named [LastName], [FirstName] and [MiddleName], I want those columns
to be as one and name it as [Name]. How will I do that? I already tried the
trick like what I did in Access but it does'nt work in SQL. Please help me
with this. Any suggestions will be much appreciated.
Thanks in advance,
Jir
Try:
alter table dbo.MyTable
add
MyComputedCOlumn as LastName + ', ' + FirstName + ' ' + MiddleName
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Jir" <Jir@.discussions.microsoft.com> wrote in message
news:F64E0FD3-3E0E-4B9A-AB49-0CFFE29B5D8F@.microsoft.com...
Dear all,
I am having a problem on how to merge 3 columns into one column. I have a
columns named [LastName], [FirstName] and [MiddleName], I want those columns
to be as one and name it as [Name]. How will I do that? I already tried the
trick like what I did in Access but it does'nt work in SQL. Please help me
with this. Any suggestions will be much appreciated.
Thanks in advance,
Jir

combining many contains()

Hi all,
I have a nvarchar(255) column on a tblKeyword table with many queries in a
verified FTS form. i.e-
"Microsoft Corporation"
("Hewelet Packard") OR HP
Google OR Froogle
Sun
I want to issue a combined FTS query that will gather all (*) information
from the indexed table, based on all existing queries in the tblKeyword
table.
Ie, something like:
SELECT * FROM myftstable
WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
OR (Google OR Froogle) OR (Sun)])
Is it possible to do it dynamiclly in some sort? other then connecting all
strings and sending them all to the contains?
Thanks!
Guy,
Yes, there is a way to do this via a stored proc:
use pubs
go
-- DROP PROCEDURE usp_FTSearchPubsInfo
CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
AS
declare @.s as varchar (8000)
set @.s='select pub_id, pr_info from pub_info where
contains(pr_info,'+''''+@.vcSearchText+''''+')'
exec (@.s)
go
-- returns 2 rows
EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
go
-- Using your example:
EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard" or
"HP") or ("Google" or "Froogle") or ("Sun"))'
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a nvarchar(255) column on a tblKeyword table with many queries in a
> verified FTS form. i.e-
> "Microsoft Corporation"
> ("Hewelet Packard") OR HP
> Google OR Froogle
> Sun
> I want to issue a combined FTS query that will gather all (*) information
> from the indexed table, based on all existing queries in the tblKeyword
> table.
> Ie, something like:
> SELECT * FROM myftstable
> WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
> OR (Google OR Froogle) OR (Sun)])
> Is it possible to do it dynamiclly in some sort? other then connecting all
> strings and sending them all to the contains?
> Thanks!
>
|||Hi John,
I meant how to do it programatically, so that the queries exists in
tblKeyword will be populated automatically as a long (@.vcSearchText varchar)
Guy
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
> Guy,
> Yes, there is a way to do this via a stored proc:
> use pubs
> go
> -- DROP PROCEDURE usp_FTSearchPubsInfo
> CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
> AS
> declare @.s as varchar (8000)
> set @.s='select pub_id, pr_info from pub_info where
> contains(pr_info,'+''''+@.vcSearchText+''''+')'
> exec (@.s)
> go
> -- returns 2 rows
> EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
> go
> -- Using your example:
> EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard"
or[vbcol=seagreen]
> "HP") or ("Google" or "Froogle") or ("Sun"))'
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
a[vbcol=seagreen]
information[vbcol=seagreen]
HP)[vbcol=seagreen]
all
>
|||Guy,
I'm not sure what you're asking for here... Could you provide some examples?
Are you looking for a client-side (IE-based) solution or a server-side
(T-SQL based) solution? If the former, you may want to checkout KB article
246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defaul...b;EN-US;246800
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> I meant how to do it programatically, so that the queries exists in
> tblKeyword will be populated automatically as a long (@.vcSearchText
varchar)[vbcol=seagreen]
> Guy
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
Packard"[vbcol=seagreen]
> or
in[vbcol=seagreen]
> a
> information
tblKeyword
> HP)
> all
>
|||John hi,
I need a server-side solution (T-SQL based) for connecting all of the
records appear in tblKeyword into 1 long string. Is it possible?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Guy,
> I'm not sure what you're asking for here... Could you provide some
examples?
> Are you looking for a client-side (IE-based) solution or a server-side
> (T-SQL based) solution? If the former, you may want to checkout KB article
> 246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries"
at:[vbcol=seagreen]
> http://support.microsoft.com//defaul...b;EN-US;246800
> Regards,
> John
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> varchar)
> Packard"
queries[vbcol=seagreen]
> in
> tblKeyword
OR[vbcol=seagreen]
connecting
>
|||Hi Guy,
Ok. I looked back over your original posting and I didn't realize that you
had two tables - tblKeyword and myftstable - and that in affect you wanted
to "pass" the tblKeyword table values to CONTAINS search_condition clause.
I'm sure there are other ways of doing this, but for now, I've developed two
cursor based solutions - assuming I'm understanding your question properly:
The below examples use the database (pubs) and the FT-enabled table
(authors) and the table (keyword) is your tblKeyword table:
use pubs
go
create table keyword(kword varchar(50))
go
insert into keyword values ('white')
insert into keyword values ('("john" or "paul")')
insert into keyword values ('Yokomoto')
go
select * from keyword
go
-- Simple Cursor Fetch with a CONTAINS statement...
SET NOCOUNT ON
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
OPEN keyword_cursor
DECLARE @.keyword varchar(50)
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE (@.@.fetch_status <> -1)
BEGIN
select * from authors where contains(*,@.keyword)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
SET NOCOUNT OFF
GO
-- Complex Cursor Fetch into a temp table and then select from it...
set nocount on
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
CREATE TABLE #authors_PK (author_pk char(11))
OPEN keyword_cursor
DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sql = 'insert into #authors_PK (author_pk) select au_id from
authors where contains(*, ''' + @.keyword + ''')'
exec(@.sql)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
select * from #authors_PK
drop table #authors_PK
go
-- Clean-up
drop table keyword
go
Let me know if this is what you're looking for.
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> John hi,
> I need a server-side solution (T-SQL based) for connecting all of the
> records appear in tblKeyword into 1 long string. Is it possible?
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> examples?
article[vbcol=seagreen]
> at:
varchar(7800))[vbcol=seagreen]
> queries
Packard")
> OR
> connecting
>
|||Exactly!!
Thank you John!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uwMwhCnKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi Guy,
> Ok. I looked back over your original posting and I didn't realize that you
> had two tables - tblKeyword and myftstable - and that in affect you wanted
> to "pass" the tblKeyword table values to CONTAINS search_condition clause.
> I'm sure there are other ways of doing this, but for now, I've developed
two
> cursor based solutions - assuming I'm understanding your question
properly:[vbcol=seagreen]
> The below examples use the database (pubs) and the FT-enabled table
> (authors) and the table (keyword) is your tblKeyword table:
> use pubs
> go
> create table keyword(kword varchar(50))
> go
> insert into keyword values ('white')
> insert into keyword values ('("john" or "paul")')
> insert into keyword values ('Yokomoto')
> go
> select * from keyword
> go
> -- Simple Cursor Fetch with a CONTAINS statement...
> SET NOCOUNT ON
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> select * from authors where contains(*,@.keyword)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> SET NOCOUNT OFF
> GO
> -- Complex Cursor Fetch into a temp table and then select from it...
> set nocount on
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> CREATE TABLE #authors_PK (author_pk char(11))
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
> -- Fetch the first row in the cursor.
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sql = 'insert into #authors_PK (author_pk) select au_id from
> authors where contains(*, ''' + @.keyword + ''')'
> exec(@.sql)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> select * from #authors_PK
> drop table #authors_PK
> go
> -- Clean-up
> drop table keyword
> go
> Let me know if this is what you're looking for.
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...
> article
Queries"
> varchar(7800))
> Packard")
>

Combining established columns into one

I have a table whose schema is already defined and populated with data. I would like to create a column named Name that combines the first and last name columns in the following format "last name, first name". I tried to create a formula that concatenated these two columns, but it kept spitting up on me. Any ideas?Could you please post your syntax?|||It is best to do the formatting for display purposes on the client. What if you want to change the formatting later? You hav e to make schema changes even if you use computed columns or views or queries in SPs.|||

you may wish to use a calculated column

use northwind
select * from employees
go
alter table employees
add
fullname as rtrim(lastname)+','+rtrim(firstname)
go

select fullname,lastname,firstname from employees

|||I realize that it would be best to do all the formatting on the client. The problem is that I have about 50 stored procedures that were developed on another database that was "supposed to" have the same table schemas. Unfortunately, the developer decided to split apart the names into first name and last name fields. It would be easier to just created a computed column.|||Actually, splitting the name into it's constituent parts and storing it is the correct way. You can use a computed column or view with the computed expression or modify your SP to include the computed expression. With all these methods, you can get the required column for display purposes. But if you want to search on this concatenated string then it is a different deal. Performance depends on lot of factors like index on the computed column, whether optimizer matches the computed column expression and uses the index and so on.

Combining date field and time field in a column

SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
Why my date minus two day? so what should i need to do? Urgent please reply to me at Babies001@.yahoo.co
ThankWhat datatypes are you using for storing your date and time values? If using
strings, then concatenate them and use CAST or CONVERT functions to convert
the concatenated value into a datetime value.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TM" <anonymous@.discussions.microsoft.com> wrote in message
news:E6A4D56F-97F0-4E97-AD2D-3348504128DF@.microsoft.com...
SELECT RequireDate + ' ' + RequireTime AS date
FROM IN_Header
My Database:
Date Time
28/03/2004 01:34:09PM
After run SQL statement, my result become:
26/03/2004 01:34:09PM
Why my date minus two day? so what should i need to do? Urgent please reply
to me at Babies001@.yahoo.com
Thanks|||Regarding the Question, my database fiel
Field DataTyp
Date DateTim
Time DateTim
So my result become
SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
What the code for convert the date and time together and my date will not minus two day
Can adding the source code inside
Thank
-- Narayana Vyas Kondreddi wrote: --
What datatypes are you using for storing your date and time values? If usin
strings, then concatenate them and use CAST or CONVERT functions to conver
the concatenated value into a datetime value
--
HTH
Vyas, MVP (SQL Server
http://vyaskn.tripod.com
Is .NET important for a database professional
http://vyaskn.tripod.com/poll.ht
"TM" <anonymous@.discussions.microsoft.com> wrote in messag
news:E6A4D56F-97F0-4E97-AD2D-3348504128DF@.microsoft.com..
SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
Why my date minus two day? so what should i need to do? Urgent please repl
to me at Babies001@.yahoo.co
Thanksqlsql

combining columns into one table

What is the easiest way to combine the output of a several selects on a table and have each output become a column on a new table?
Thanks
Joel
Assuming each SELECT has the same output and that the datatypes match:
INSERT newtable
SELECT col = <...some_select...>
UNION ALL
SELECT <...some_other_select...>
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:CFA8303B-D2E0-4746-BBA4-04FA61743685@.microsoft.com...
> What is the easiest way to combine the output of a several selects on a
table and have each output become a column on a new table?
> Thanks
> Joel
|||well not exactly what I wanted - here's what I'm looking for. for example, suppose you have one table A with 3 columns as shown below:
oid name desc
-- -- --
1 vase container
2 lamp light
1 desk furniture
2 table furniture
1 table furniture
1 lamp light
then execute "select desc from A where oid=1 and desc=container" -- with result
container
and then execute "select desc from A where oid=1 and desc=furniture" -- with result
furniture
furniture
what I want to do is combine both outputs into 2 columns like this:
container furniture
furniture
furniture
Actually the queries and tables are more involved than this simple example but I hope I am getting the concept across.
Thanks
Joel
|||This looks like a report of some kind, and the relationship here is not,
well, relational... probably better to iterate through and combine things
together at the client.
I don't see exactly how container ends up being directly related to
furniture and why furniture has three rows (one associated with container
and two not).
Can you provide REAL table schema, REAL sample data, and REAL desired
results? See http://www.aspfaq.com/5006
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:3D60D589-51C3-4586-BF44-9FFE063AD95B@.microsoft.com...
> well not exactly what I wanted - here's what I'm looking for. for
example, suppose you have one table A with 3 columns as shown below:
> oid name desc
> -- -- --
> 1 vase container
> 2 lamp light
> 1 desk furniture
> 2 table furniture
> 1 table furniture
> 1 lamp light
> then execute "select desc from A where oid=1 and desc=container" -- with
result
> container
> and then execute "select desc from A where oid=1 and desc=furniture" --
with result
> furniture
> furniture
> what I want to do is combine both outputs into 2 columns like this:
> container furniture
> furniture
> furniture
> Actually the queries and tables are more involved than this simple example
but I hope I am getting the concept across.
> Thanks
> Joel
|||You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that I could build the table and then the client (ColdFusion) would iterate thru each row and present the row values via an HTML table. And yes, ther
e really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a column is added to the table thereby increasing the number of columns by 1 each time a column is added? Also when one column (with all rows containing values) to be added is longer
that the table to be added to, then will extra rows (which can be empty) be added so that all columns have same number of rows?
Thanks
Joel
|||Joel,
A table consists of a number of rows, where each row has the same column structure and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added?>>
Yes. If the table is a stored table, you do "ALTER TABLE tblname ADD colname ...". If the table is a result
from a SELECT statement, then you define that structure by the column list in the SELECT statement.
<<Also when one column (with all rows containing values) ...>>
"All rows containing values" is always true in a table. You never have a row which "doesn't contain values".
<<...to be added is longer that the table to be added to...>>
What is "longer" than what? Again, a table consists of a number of rows where each row has the same column
structure.
<<..., then will extra rows (which can be empty)...>>
There is no such thing as an empty row. That concept doesn't exist. The values for each column in a row is
restricted by the datatype that the column has, and a column can also possibly be NULL.
<<... be added so that all columns have same number of rows?>>
? A column doesn't "have a number of rows". A table is defined by a datatype for each column, and for each
row, you have a value for each column in the table.
I agree with Aaron that you seem to confuse data (what we have stored in a database and also the result of
SELECT statements) with presentation of the data (what you do in a client application).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:5BE36CEC-27A7-4E75-8C2C-72C8A104E5E6@.microsoft.com...
> You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that
I could build the table and then the client (ColdFusion) would iterate thru each row and present the row
values via an HTML table. And yes, there really is no relation between cells on the same row.
> But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added? Also when one column (with all rows
containing values) to be added is longer that the table to be added to, then will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel
sqlsql

combining columns into one table

What is the easiest way to combine the output of a several selects on a tabl
e and have each output become a column on a new table?
Thanks
JoelAssuming each SELECT has the same output and that the datatypes match:
INSERT newtable
SELECT col = <...some_select...>
UNION ALL
SELECT <...some_other_select...>
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:CFA8303B-D2E0-4746-BBA4-04FA61743685@.microsoft.com...
> What is the easiest way to combine the output of a several selects on a
table and have each output become a column on a new table?
> Thanks
> Joel|||well not exactly what I wanted - here's what I'm looking for. for example,
suppose you have one table A with 3 columns as shown below:
oid name desc
-- -- --
1 vase container
2 lamp light
1 desk furniture
2 table furniture
1 table furniture
1 lamp light
then execute "select desc from A where oid=1 and desc=container" -- with r
esult
container
and then execute "select desc from A where oid=1 and desc=furniture" -- wi
th result
furniture
furniture
what I want to do is combine both outputs into 2 columns like this:
container furniture
furniture
furniture
Actually the queries and tables are more involved than this simple example b
ut I hope I am getting the concept across.
Thanks
Joel|||This looks like a report of some kind, and the relationship here is not,
well, relational... probably better to iterate through and combine things
together at the client.
I don't see exactly how container ends up being directly related to
furniture and why furniture has three rows (one associated with container
and two not).
Can you provide REAL table schema, REAL sample data, and REAL desired
results? See http://www.aspfaq.com/5006
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:3D60D589-51C3-4586-BF44-9FFE063AD95B@.microsoft.com...
> well not exactly what I wanted - here's what I'm looking for. for
example, suppose you have one table A with 3 columns as shown below:
> oid name desc
> -- -- --
> 1 vase container
> 2 lamp light
> 1 desk furniture
> 2 table furniture
> 1 table furniture
> 1 lamp light
> then execute "select desc from A where oid=1 and desc=container" -- with
result
> container
> and then execute "select desc from A where oid=1 and desc=furniture" --
with result
> furniture
> furniture
> what I want to do is combine both outputs into 2 columns like this:
> container furniture
> furniture
> furniture
> Actually the queries and tables are more involved than this simple example
but I hope I am getting the concept across.
> Thanks
> Joel|||You're right, it is a report that will be displayed via ColdFusion on a dyna
mic web page. I was hoping that I could build the table and then the client
(ColdFusion) would iterate thru each row and present the row values via an
HTML table. And yes, ther
e really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a colu
mn is added to the table thereby increasing the number of columns by 1 each
time a column is added? Also when one column (with all rows containing valu
es) to be added is longer
that the table to be added to, then will extra rows (which can be empty) be
added so that all columns have same number of rows?
Thanks
Joel|||Joel,
A table consists of a number of rows, where each row has the same column str
ucture and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a co
lumn is added to the table thereby
increasing the number of columns by 1 each time a column is added?>>
Yes. If the table is a stored table, you do "ALTER TABLE tblname ADD colname
...". If the table is a result
from a SELECT statement, then you define that structure by the column list i
n the SELECT statement.
<<Also when one column (with all rows containing values) ...>>
"All rows containing values" is always true in a table. You never have a row
which "doesn't contain values".
<<...to be added is longer that the table to be added to...>>
What is "longer" than what? Again, a table consists of a number of rows wher
e each row has the same column
structure.
<<..., then will extra rows (which can be empty)...>>
There is no such thing as an empty row. That concept doesn't exist. The valu
es for each column in a row is
restricted by the datatype that the column has, and a column can also possib
ly be NULL.
<<... be added so that all columns have same number of rows?>>
? A column doesn't "have a number of rows". A table is defined by a datatype
for each column, and for each
row, you have a value for each column in the table.
I agree with Aaron that you seem to confuse data (what we have stored in a d
atabase and also the result of
SELECT statements) with presentation of the data (what you do in a client ap
plication).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:5BE36CEC-27A7-4E75-8C2C-72C8A104E5E6@.microsoft.com...
> You're right, it is a report that will be displayed via ColdFusion on a dynamic we
b page. I was hoping that
I could build the table and then the client (ColdFusion) would iterate thru
each row and present the row
values via an HTML table. And yes, there really is no relation between cells on the same r
ow.
> But as a general question is it possible to manufacture a table where a column is
added to the table thereby
increasing the number of columns by 1 each time a column is added? Also whe
n one column (with all rows
containing values) to be added is longer that the table to be added to, the
n will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel

combining columns into one table

What is the easiest way to combine the output of a several selects on a table and have each output become a column on a new table
Thank
JoelAssuming each SELECT has the same output and that the datatypes match:
INSERT newtable
SELECT col = <...some_select...>
UNION ALL
SELECT <...some_other_select...>
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:CFA8303B-D2E0-4746-BBA4-04FA61743685@.microsoft.com...
> What is the easiest way to combine the output of a several selects on a
table and have each output become a column on a new table?
> Thanks
> Joel|||well not exactly what I wanted - here's what I'm looking for. for example, suppose you have one table A with 3 columns as shown below
oid name des
-- -- --
1 vase containe
2 lamp ligh
1 desk furnitur
2 table furnitur
1 table furnitur
1 lamp ligh
then execute "select desc from A where oid=1 and desc=container" -- with resul
containe
and then execute "select desc from A where oid=1 and desc=furniture" -- with resul
furnitur
furnitur
what I want to do is combine both outputs into 2 columns like this
container furnitur
furnitur
furnitur
Actually the queries and tables are more involved than this simple example but I hope I am getting the concept across
Thank
Joel|||This looks like a report of some kind, and the relationship here is not,
well, relational... probably better to iterate through and combine things
together at the client.
I don't see exactly how container ends up being directly related to
furniture and why furniture has three rows (one associated with container
and two not).
Can you provide REAL table schema, REAL sample data, and REAL desired
results? See http://www.aspfaq.com/5006
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:3D60D589-51C3-4586-BF44-9FFE063AD95B@.microsoft.com...
> well not exactly what I wanted - here's what I'm looking for. for
example, suppose you have one table A with 3 columns as shown below:
> oid name desc
> -- -- --
> 1 vase container
> 2 lamp light
> 1 desk furniture
> 2 table furniture
> 1 table furniture
> 1 lamp light
> then execute "select desc from A where oid=1 and desc=container" -- with
result
> container
> and then execute "select desc from A where oid=1 and desc=furniture" --
with result
> furniture
> furniture
> what I want to do is combine both outputs into 2 columns like this:
> container furniture
> furniture
> furniture
> Actually the queries and tables are more involved than this simple example
but I hope I am getting the concept across.
> Thanks
> Joel|||You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that I could build the table and then the client (ColdFusion) would iterate thru each row and present the row values via an HTML table. And yes, there really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a column is added to the table thereby increasing the number of columns by 1 each time a column is added? Also when one column (with all rows containing values) to be added is longer that the table to be added to, then will extra rows (which can be empty) be added so that all columns have same number of rows
Thank
Joel|||Joel,
A table consists of a number of rows, where each row has the same column structure and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added?>>
Yes. If the table is a stored table, you do "ALTER TABLE tblname ADD colname ...". If the table is a result
from a SELECT statement, then you define that structure by the column list in the SELECT statement.
<<Also when one column (with all rows containing values) ...>>
"All rows containing values" is always true in a table. You never have a row which "doesn't contain values".
<<...to be added is longer that the table to be added to...>>
What is "longer" than what? Again, a table consists of a number of rows where each row has the same column
structure.
<<..., then will extra rows (which can be empty)...>>
There is no such thing as an empty row. That concept doesn't exist. The values for each column in a row is
restricted by the datatype that the column has, and a column can also possibly be NULL.
<<... be added so that all columns have same number of rows?>>
? A column doesn't "have a number of rows". A table is defined by a datatype for each column, and for each
row, you have a value for each column in the table.
I agree with Aaron that you seem to confuse data (what we have stored in a database and also the result of
SELECT statements) with presentation of the data (what you do in a client application).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:5BE36CEC-27A7-4E75-8C2C-72C8A104E5E6@.microsoft.com...
> You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that
I could build the table and then the client (ColdFusion) would iterate thru each row and present the row
values via an HTML table. And yes, there really is no relation between cells on the same row.
> But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added? Also when one column (with all rows
containing values) to be added is longer that the table to be added to, then will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel