October 10, 2008 at 2:57 pm
Hello Experts,
Actually I have a Dynamically generated table with the columns names like Q1, Q2.... up to any number don't know. I just wanted todo a select * from second column. i.e, avoid the first column any idea? I am using MSSQL 2005.
Thank you,
Arshad
October 10, 2008 at 3:03 pm
select * gets expanded to all columns. If you do not want all columns, you must specifically name the columns in the query.
October 10, 2008 at 3:26 pm
Thanks for your prompt reply Champion.
Actually i created the table through XML and i dont know the number of columns generated by the XML into the table. some time they created the columns from Q1 to Q30. i.e 30 columns and sometime its just Q1 to Q3 i.e. 3 columns only. So i dont know the exact number of columns my temporary table have :(. the number one column is ID of that table. I dont want to * the ID. i just wanted to start with the second column but with Select * option :(.
Regards
October 10, 2008 at 3:47 pm
You're making contradictory statements. I understand you don't know the column name, but selecting only one column and selecting all columns are conflicting requirements.
In addition - if your first column is ID, and all other columns are called Q1 through Q30, wouldn't that make column #2 Q1 at all times?
If it's not that simple - you may need to go to dynamic SQL:
declare @column_num int
declare @columnname varchar(100)
set @column_num=2
select @columnname=column_name from information_schema.columns
where table_name='Mytable' and ordinal_position=@column_num
declare @sql varchar(max)
set @sql='select ['+@columnname+'] from MyTable'
Exec(@sql)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2008 at 3:50 pm
Now that I reread that - you want all BUT the first column? You then might need to use what I initially gave you and just build the dynamic SQL to do that.
What is so bad about including the ID field?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 13, 2008 at 5:32 am
Probably not the best solution as i often think 'like a programmer' but...
cant you select the whole results into a temp table and then drop the first column then select * from that?
October 13, 2008 at 5:41 am
you play with "information_schema.columns", based on this you can generate dynamic sql statement.
October 13, 2008 at 8:28 am
Can I ask why the first column is a problem? Perhaps we have another way to do this.
Honestly any other way than dealing with this in the client is asking for performance issues under load.
October 14, 2008 at 8:23 am
Hi All,
Thank you very much for your replies
SSChampion Actually i wanted to generate an XML document through stored procedure based on the dynamic Columns and dont need any ID in the XML document. you already know i dont know the number of the columns and names and the columns would be generated in the form of Q1, Q2, Q3........ up to any number. My Query in the stored procedure is
(Select * from FOR XML PATH('QUESTIONS'), Type) FROM #tmp_DataFeed.
It displays all the fields from the temp table with ID but i dont want to display that ID.
Hope that would help you to understand my problem.
Regards
Arshad
October 14, 2008 at 8:29 am
Hi Enthusiastic,
Thank you for your reply. Actually i dont know the name and number of columns thats why cant able to to create a temp table. If you have any syntax is available to generate or loop through the table for getting the number of columns than please help me? the columns generated like Q1, Q2, Q3 .... up to any number.
Regards
Arshad
October 14, 2008 at 9:08 am
declare @column_num int
declare @columnname varchar(max)
declare @tablename varchar(max)
set @column_num=2
set @columnname='';
set @tablename='MyTable'
select @columnname=@columnname+',['+column_name+']' from information_schema.columns
where table_name=@tablename and ordinal_position>=@column_num
declare @sql varchar(max)
set @sql='select '+substring(@columnname,2,len(@columnname))+' from '+@tablename
Print (@sql)
Exec(@sql)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 14, 2008 at 9:52 am
Thank you Matt, It works fine just a one more quick Question, how can i make a select statement with your script Actually i wanted to generate an XML document through SQL Query like
(Select * from FOR XML PATH('QUESTIONS'), Type) FROM @sql where ID = @id.
Waiting for your reply.
Arshad
October 14, 2008 at 4:11 pm
Hi Matt,
Sorry to bother you again. Using same information_Schema.Column how do we filter the record through the same eliminated ID before eliminating the ID? is there any to use the where clause in information_Schema.Column?
Regards
Arshad
October 14, 2008 at 4:19 pm
Arshad - remember that you're simply build a SQL command. Build the string so that it returns the query you wish.
As to using parameters - you probably will want to switch to sp_executeSQL instead of just EXEC, since sp_executeSQL will allow you to pass in parameters.
I'd suggest this: use the code I gave you to build your string, but instead of EXEC, use PRINT(@SQL) for a while, so that you can get the query code you want.
Once you have the right code - have a gander at using sp_executeSQL, which is described here:
http://msdn.microsoft.com/en-us/library/ms188001.aspx
Give it a shot, and if you get stuck, then post back here.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 15, 2008 at 3:28 am
Here is some code that does what i think you need in the sample database.
Hope it helps.
(Please note this uses the old methods to reference the system objects (syscolumns). From SQL 2005 it is recommended to use the newer catalog views. If this is more than a one off process you might want to look into that so that you dont get issues as SQL versions progress...)
USE [AdventureWorks]
GO
-- gather your data into a 'scrap' table
IF OBJECT_ID(N'temptbl') > 0
BEGIN
DROP TABLE temptbl
END
SELECT *
INTO temptbl
FROM [Person].[Address] AS a
--DROP TABLE temptbl
GO
-- prove its there
SELECT *
FROM temptbl ;
-- show the table id
DECLARE @TableID INT
SELECT @TableID = OBJECT_ID(N'temptbl')
PRINT @tableid
-- uncomment this TO see the contents OF the syscolumns table
--SELECT * -- colid
--FROM [sys].[syscolumns] AS s
--WHERE s.[id] = @TableID
--ORDER BY s.[name]
DECLARE @ColName CHAR(100)
SELECT @ColName = sc.NAME
FROM [sys].[syscolumns] AS sc
WHERE sc.[id] = @TableID
AND sc.[colid] = 1
/*
If the column is a constraint you will need to drop the constraint first
*/
DECLARE @sql CHAR(100)
EXEC ( 'ALTER TABLE [temptbl] DROP COLUMN ' + @colname + ';') ;
GO
SELECT *
FROM [temptbl] AS t
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply