+ Reply to Thread
Results 1 to 4 of 4

Subquery for column names

  1. Subquery for column names

    I have a database table with a large amount of analytical data in it -
    all floats but for a timestamp column. Instead of returning the
    entire set, I want to be able to return specific columns based on a
    "system" number that is specified as a parameter (in this case it is
    written in as "S02" for testing purposes) and compare the provided
    system number with the column names within the table - which is what
    the subquery currently does correctly.

    Aside from creating a stored procedure to handle the result and
    reformat it into a string to then use as a parameter of a second
    query, is there any way to use the above as a subquery as I have tried
    below (unsuccessfully since SQL understandably returns the error that
    "Subquery returned more than 1 value...").

    I hope I have explained what I am trying to do well enough - it is a
    bit convoluted. Please ask if more clarification is required.

    SELECT dtDateTime,
    (SELECT Devices=syscolumns.name
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
    WHERE sysobjects.xtype='U'
    AND syscolumns.name like ('%S02%'))
    FROM tbAnalogs

  2. Re: Subquery for column names

    "jtertin" wrote in message
    news:4cb2eaee-d52c-48ee-8cc2-d92fde257e22atq16g2000yqqdotgooglegroups.com...
    >I have a database table with a large amount of analytical data in it -
    > all floats but for a timestamp column. Instead of returning the
    > entire set, I want to be able to return specific columns based on a
    > "system" number that is specified as a parameter (in this case it is
    > written in as "S02" for testing purposes) and compare the provided
    > system number with the column names within the table - which is what
    > the subquery currently does correctly.
    >
    > Aside from creating a stored procedure to handle the result and
    > reformat it into a string to then use as a parameter of a second
    > query, is there any way to use the above as a subquery as I have tried
    > below (unsuccessfully since SQL understandably returns the error that
    > "Subquery returned more than 1 value...").
    >
    > I hope I have explained what I am trying to do well enough - it is a
    > bit convoluted. Please ask if more clarification is required.
    >
    > SELECT dtDateTime,
    > (SELECT Devices=syscolumns.name
    > FROM sysobjects
    > JOIN syscolumns ON sysobjects.id = syscolumns.id
    > JOIN systypes ON syscolumns.xtype=systypes.xtype
    > WHERE sysobjects.xtype='U'
    > AND syscolumns.name like ('%S02%'))
    > FROM tbAnalogs


    You must have more than one column with 'S02' in the name. Do you want to
    return all columns that have 'S02' in them or just one of them? If just one
    of them, which one?

    --
    Thanks

    Michael Coles
    SQL Server MVP
    Author, "Expert SQL Server 2008 Encryption"
    (http://www.apress.com/book/view/1430224649)
    ----------------


  3. Re: Subquery for column names

    On Mar 8, 8:34*pm, "Michael Coles" wrote:
    > "jtertin" wrote in message
    >
    > news:4cb2eaee-d52c-48ee-8cc2-d92fde257e22atq16g2000yqqdotgooglegroups.com...
    >
    >
    >
    > >I have a database table with a large amount of analytical data in it -
    > > all floats but for a timestamp column. *Instead of returning the
    > > entire set, I want to be able to return specific columns based on a
    > > "system" number that is specified as a parameter (in this case it is
    > > written in as "S02" for testing purposes) and compare the provided
    > > system number with the column names within the table - which is what
    > > the subquery currently does correctly.

    >
    > > Aside from creating a stored procedure to handle the result and
    > > reformat it into a string to then use as a parameter of a second
    > > query, is there any way to use the above as a subquery as I have tried
    > > below (unsuccessfully since SQL understandably returns the error that
    > > "Subquery returned more than 1 value...").

    >
    > > I hope I have explained what I am trying to do well enough - it is a
    > > bit convoluted. *Please ask if more clarification is required.

    >
    > > SELECT dtDateTime,
    > > * *(SELECT Devices=syscolumns.name
    > > * * FROM sysobjects
    > > * * JOIN syscolumns ON sysobjects.id = syscolumns.id
    > > * * JOIN systypes ON syscolumns.xtype=systypes.xtype
    > > * * WHERE sysobjects.xtype='U'
    > > * * AND syscolumns.name like ('%S02%'))
    > > FROM tbAnalogs

    >
    > You must have more than one column with 'S02' in the name. *Do you wantto
    > return all columns that have 'S02' in them or just one of them? *If just one
    > of them, which one?
    >
    > --
    > Thanks
    >
    > Michael Coles
    > SQL Server MVP
    > Author, "Expert SQL Server 2008 Encryption"
    > (http://www.apress.com/book/view/1430224649)
    > ----------------


    The idea is to return all column with 'S02' in the column name. I
    know the subquery returning all of them is the issue, I just need to
    know if I can us this approach.

    I am looking, basically, for the following (which I know is wrong, but
    gets the point across):

    SELECT dtDateTime, %S02% FROM tbAnalogs

    Thanks for your insight!

  4. Re: Subquery for column names

    On Mar 9, 8:31*am, jtertin wrote:
    > On Mar 8, 8:34*pm, "Michael Coles" wrote:
    >
    >
    >
    > > "jtertin" wrote in message

    >
    > >news:4cb2eaee-d52c-48ee-8cc2-d92fde257e22atq16g2000yqqdotgooglegroups.com....

    >
    > > >I have a database table with a large amount of analytical data in it -
    > > > all floats but for a timestamp column. *Instead of returning the
    > > > entire set, I want to be able to return specific columns based on a
    > > > "system" number that is specified as a parameter (in this case it is
    > > > written in as "S02" for testing purposes) and compare the provided
    > > > system number with the column names within the table - which is what
    > > > the subquery currently does correctly.

    >
    > > > Aside from creating a stored procedure to handle the result and
    > > > reformat it into a string to then use as a parameter of a second
    > > > query, is there any way to use the above as a subquery as I have tried
    > > > below (unsuccessfully since SQL understandably returns the error that
    > > > "Subquery returned more than 1 value...").

    >
    > > > I hope I have explained what I am trying to do well enough - it is a
    > > > bit convoluted. *Please ask if more clarification is required.

    >
    > > > SELECT dtDateTime,
    > > > * *(SELECT Devices=syscolumns.name
    > > > * * FROM sysobjects
    > > > * * JOIN syscolumns ON sysobjects.id = syscolumns.id
    > > > * * JOIN systypes ON syscolumns.xtype=systypes.xtype
    > > > * * WHERE sysobjects.xtype='U'
    > > > * * AND syscolumns.name like ('%S02%'))
    > > > FROM tbAnalogs

    >
    > > You must have more than one column with 'S02' in the name. *Do you want to
    > > return all columns that have 'S02' in them or just one of them? *If just one
    > > of them, which one?

    >
    > > --
    > > Thanks

    >
    > > Michael Coles
    > > SQL Server MVP
    > > Author, "Expert SQL Server 2008 Encryption"
    > > (http://www.apress.com/book/view/1430224649)
    > > ----------------

    >
    > The idea is to return all column with 'S02' in the column name. *I
    > know the subquery returning all of them is the issue, I just need to
    > know if I can us this approach.
    >
    > I am looking, basically, for the following (which I know is wrong, but
    > gets the point across):
    >
    > SELECT dtDateTime, %S02% FROM tbAnalogs
    >
    > Thanks for your insight!


    I ended up using the following stored procedure. If anyone has a
    better suggestion, please let me know, otherwise this will work.

    CREATE PROCEDURE GetChartData
    @intSystemNumber Int
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @DeviceList varchar(255)

    SELECT @DeviceList=COALESCE(@DeviceList+ ', ', '')+syscolumns.name
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
    WHERE sysobjects.xtype='U'
    AND syscolumns.name like
    '%S'+right('0'+convert(varchar,@intSystemNumber),2)+'%'
    ORDER BY syscolumns.name

    --Print @DeviceList

    EXEC('SELECT dtDateTime,' + @DeviceList + ' FROM tbAnalogs ORDER BY
    dtDateTime')
    END
    GO

+ Reply to Thread