building select list from a subquery.
-
Im trying to populated a select list for a query from a subuery? Example. I have 90 columns in a table, but I only want to select the columns that start with Cat.
Select ( query that pulls back all the columns that start with cat) from table animals. - This doesn’t work because the subquery pulls back more than one value..
Stuck.. any assistance appreciated.
-
Try this:
-----------------------------------------------------
-- GET TABLE COLUMN NAMES FOR INSERT --
-----------------------------------------------------
DECLARE @tblColumnString TABLE(iRowId INT IDENTITY(1,1),vcColumnName VARCHAR(255))
DECLARE @i INT
DECLARE @iFirstColumn INT
DECLARE @vcColumnString VARCHAR(8000)
DECLARE @vcColumnName VARCHAR(255)
DECLARE @vcExecSQL VARCHAR(8000)
INSERT INTO @tblColumnString Select name from syscolumns where id = OBJECT_ID(N'Animals') and name like 'cat%'
SET @i = @@ROWCOUNT
SET @iFirstColumn = 1
SET @vcColumnString = ''
WHILE @i > 0
BEGIN
SET @vcColumnName = (Select vcColumnName from @tblColumnString where iRowId = @i)
IF @iFirstColumn = 1
BEGIN
SET @vcColumnString = @vcColumnName
SET @iFirstColumn = 0
END
ELSE
BEGIN
SET @vcColumnString = @vcColumnString+','+@vcColumnName
END
SET @i = @i-1
END
SET @vcExecSQL = 'select '+@vcColumnString+' from animals'
EXEC @vcExecSQL
-
-
Sorry Jason, I could not resist...
IF EXISTS (SELECT * FROM sysobjects WHERE sysstat & 0xf = 3
AND id = OBJECT_ID('Animals'))
DROP TABLE Animals
GO
CREATE TABLE Animals (cat1 int, cat2 int, cat3 int, cat4 int
, dog1 int, dog2 int, dog3 int)
-- Above just for testing, don't delete your table!
DECLARE @TableName sysname
, @ColumnFilter varchar(255)
, @SQL varchar(8000)
SELECT @TableName = N'Animals'
, @ColumnFilter = 'cat%' --'dog%' --
SELECT @SQL =ISNULL(@SQL+',','')+name
FROM syscolumns
WHERE id = OBJECT_ID(@TableName) --N'Animals')
AND name LIKE @ColumnFilter --'cat%' --
SET @SQL = 'select '+@SQL+' from '+@TableName
--SELECT @SQL -- toggle SELECT vs EXEC for testing
EXEC (@SQL)
Andy
-
Andy,
Don't be sorry, this is what makes forums great. A user posts a question hoping to get the answer to solve some major problem he/she is having. Other users post the ways that they've done it in the past or how they think it should be done, the original poster has a lot of choices... (and in the case of this one I got to learn a little too
) so thank you!Bobko,
FYI... The method posted by Andy has two major advantages over the method that I posted... 1st Andy's method doesn't use a WHILE... END loop which helps perfomance... 2nd (and my favorite) Andy's method requires less typing to do the same thing. In your choices, I'd recommend Andy's.
-
-
Thanks everyone for thier assistance.
I actually wrote my own script that uses a cursor to loop through the field names.
However the problem i have now, is that I was trying to use this script to export specific data into excel. However when building a dts package, the script will not populate the create table statement to build the schema within excel from which to import into. If I use a standard select statement it populates the create table statement for me. Must have something to do with running a stored proc. I even encapsulated all of my script into a stored proc but it doesnt like this either.
any further assistance is appreciated.
thanks.
-
Build your string first and use it as part of the spec of your DTS package. You should be able to create the package in TSQL, and incorporate the string into it that way. You could then rerun your script to recreate the package when schema changes occur. Not quite sure if this is what you are after, though. Perhaps all you need to do is PRINT the string, copy and paste it into the DTS gui?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Agree that meaningful grouping of columnname prefixes is almost certainly symptomatic of denormalisation. Might not be a business problem though.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply