How can I seleecet multiple words from sql server?

Question

create table mytable(id numder, name varchar(20))
insert into mytable values(1,'john')
insert into mytable values(2,'nati')
insert into mytable values(3,'reda')

How can I select the names john, nati and reda: at once by using a single instraction?

Answer 1

select a.name, b.name, c.name
from mytable a,
mytable b,
mytable c
where a.id_number = 1
and b.id_number = 2
and c.id_number = 3

Answer 2

I know its just an example, but first check if Id and name are reserved words. If they are, then encapsulate them using square brackets.

If the table stays with just the three rows, then

SELECT [name] FROM mytable

should do it. However, if you add more names you may want to do something like

SELECT [name] FROM mytable WHERE [name] IN ('john', 'nati', 'reda')

Obviously if you have more than one john then you may need to change the query to work on the id column instead.

If the names changed, you could use a subquery, and maintain a reference table with all the names you need:

SELECT [name] FROM mytable WHERE [name] IN (SELECT [name] FROM myRefTable)

Remember for large tables IN isn't very efficient.

Hope this helps

Answer 3

You need a pivot table

Assuming that you mean SQL Server from Microsoft
If you are on SQL2005 look up Pivot Table in Online Help! or if you are on an earlier version goto SQLServerCentral and search for Pivot Table or do the same search on Google.

Answer 4

select name from mytable;