Select Query - 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

SQL Server Development (2000)

Select Query

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-08-22 : 16:33:59

Is possible to select all the fields that begin with "ctgry"?
Something like:

select ctgry%
from features

Thanks,
Lane

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-22 : 16:49:39

I think you could go through the sysobjects table and build a query and execute it like Dynamic SQL.

I'd advise against this. Your query will run faster if you list out the fields.

If you have "too many to type" then you probably need to re-evaulate your database design.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-22 : 16:50:29

Not without doing some really fancy Dynamic SQL that you probably don't really want to get into.

Beyond that, it concerns me that you would have many fields that have a similar name like that. It suggests to me that the database might not be normalized. There are some valid reasons why you might have that, such as some kind of naming convention, but in general, I find that when a table has multiple fields with similar names, the table is not properly normalized.

Edited by - ajarnmark on 08/22/2002 16:51:37

lane0618
Posting Yak Master

134 Posts

Posted - 2002-08-23 : 17:18:26

I've got this far:

SELECT DISTINCT name
into #temp
FROM dbo.syscolumns
WHERE (name LIKE N'ctgry_%')
order by name

DECLARE @skulist varchar(1000)
SELECT @skulist = COALESCE(@skulist + ', ', '') +
CAST(name AS varchar(20))
FROM #temp

declare @sql varchar(1000)
select @sql = 'select theid, ftrdesc, category, ' + @Skulist + ' FROM dbo.Features
ORDER BY category, ftrDesc ASC'
exec(@sql)

drop table #temp

--------------
If I print the above, I get :
select theid, ftrdesc, category, ctgry1, ctgry17, ctgry2, ctgry3, ctgry4, ctgry5, ctgry6 FROM dbo.Features
ORDER BY category, ftrDesc ASC

---------------
This gives me what I need, except that I need to change the field names from ctgry1, ctgry17 etc to kitchen, bathroom per another table that looks like this:

id desc
1 kitchen
17 bathroom
etc....

Is it possible to dynamically end up with a statement that reads something like:

select theid, ftrdesc, category, ctgry1 as kitchen, ctgry17 as bathroom, etc....
FROM dbo.Features
ORDER BY category, ftrDesc ASC

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-23 : 18:01:51

You probably need to use some CASE logic in the place where it builds your dynamic SQL:

 
DECLARE @skulist varchar(1000) 
SELECT @skulist = COALESCE(@skulist + ', ', '') + 
CASE CAST(name AS varchar(20))
    WHEN ctgry1 THEN 'kitchen'
    WHEN ctgry17 THEN 'bathroom'
    ELSE CAST(name AS varchar(20))
END 
FROM #temp

This is untested, but you should get the idea.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources