Need help in 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 2005 Forums

Transact-SQL (2005)

Need help in query

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-06 : 22:51:35

I had 50 columns in a table. 10 columns start with Axxxxx, Next 10 columns start with Bxxxxx, Next 10 with cxxx and so on. Instead of retriving data from all 50 columns i want to get data from limited columns by passing an input as alphabet. Suppose if i give input as "b" the query has to retrive the data from columns start with "B".
I tried it in following ways....But unable to...

Select top 1 (
select Top 1 stuff((select ', '+ column_name from information_schema.columns where table_name = 'SampleData'
and column_name like 'B%' for xml path('')),1,1,'')
) t from SampleData

With nested query i am able to get the columns started with B appending with commas. My idea is to pass this columns names to outer query to get the data.. But it is failed. Please give the query for this requirement.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 00:57:57

Variable column name (and/or table name) need always dynamic sql.

No, you're never too old to Yak'n'Roll if you're too young to die.

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-07 : 01:31:35

Can you let me know how can we frame query for above requirement ?

quote:
Originally posted by webfred

Variable column name (and/or table name) need always dynamic sql.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 02:14:59

[code]
declare @colnames varchar(max)
select @colnames = stuff((select ', '+ column_name from information_schema.columns where table_name = 'SampleData'
and column_name like 'b%' for xml path('')),1,1,'')

--select @colnames

exec('select '+@colnames+' from SampleData')
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-07 : 02:24:30

Thanks. It works Perfect.

quote:

Originally posted by webfred


declare @colnames varchar(max)
select @colnames = stuff((select ', '+ column_name from information_schema.columns where table_name = 'SampleData'
and column_name like 'b%' for xml path('')),1,1,'') 

--select @colnames

exec('select '+@colnames+' from SampleData')

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 02:27:43

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-17 : 08:02:24

Hi webfred,

By using your query i am able to get the values of the column names which i desired. Suppose if i give query as

ALTER Procedure SP_Test
@ColName Varchar(50),
@Zcode Int
As
Begin
declare @colnames varchar(max)
select @colnames = stuff((select ', '+ column_name from information_schema.columns where table_name = 'MyData'
and column_name like '' + @ColName + '%' for xml path('')),1,1,'')
exec('select '+@colnames+' from MyData where Zipcode =' + @Zcode)
--Exec('SELECT TOP 1 WITH TIES theCol FROM (select '+@colnames+' from MyData where Zipcode =' + @Zcode + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @colnames + ')) AS u5 ORDER BY theValue DESC')
End

up to first exec statement i am able to retrive the result of columns start with 'as' for corressponding zipcode.
Getting output as

ASF ASM ASG ASK
3 6 0 4

But i want the final out as column name having the highest value.
i.e from above scenario. i need out out as "ASM"(because it is highest value)

I tried the same with second "Exec" statement(commented one). but i am failed. Please can you give the correct query for this.

quote:

Originally posted by webfred


declare @colnames varchar(max)
select @colnames = stuff((select ', '+ column_name from information_schema.columns where table_name = 'SampleData'
and column_name like 'b%' for xml path('')),1,1,'') 

--select @colnames

exec('select '+@colnames+' from SampleData')

No, you're never too old to Yak'n'Roll if you're too young to die.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-17 : 08:47:02

It seems to be a difficult one because you are constructing the columns name from the system view information_schema.columns and you want the columns to be ordered as per it's content that is not available at that time.Again how will be your column order if the query returns more than one row?.

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-17 : 09:48:28

No, The query alwasy return only one row. But the thing is i need to select highest among them

quote:
Originally posted by sanoj_av

It seems to be a difficult one because you are constructing the columns name from the system view information_schema.columns and you want the columns to be ordered as per it's content that is not available at that time.Again how will be your column order if the query returns more than one row?.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-17 : 10:00:55

Sorry that's too high for me

No, you're never too old to Yak'n'Roll if you're too young to die.

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-17 : 10:07:17

Hi webfred, you only gave solution for my last post. Below is ur query..i added on mor requirement for this.

declare @colnames varchar(max)
select @colnames = stuff((select ', '+ column_name from information_schema.columns where table_name = 'SampleData'
and column_name like 'b%' for xml path('')),1,1,'')

--select @colnames

exec('select '+@colnames+' from SampleData')

Now i am asking need to get highest among those columns.

quote:
Originally posted by webfred

Sorry that's too high for me

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-17 : 10:46:20

I don't see a solution for that - sorry.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources