Results 1 to 5 of 5

Thread: Query Help 2

  1. #1
    Join Date
    May 2004
    Posts
    25

    Unanswered: Query Help 2

    Hello,

    I have table which contain the names of the tests (20 records). I want to use those names of the tests as a column names in a new table. Is it possible to do that using query?

    Thanks for help.
    DJ

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    Yes, through dynamic-sql. It basically means you create a sql-statement by another sql-statement. The new sql-statement will be a 'create table...'-statement which is then executed against the database.

    There's usually a better, simpler way though. Perhaps if you could tell us what your'e after we may have an alternative.

  3. #3
    Join Date
    May 2004
    Posts
    25
    Actually, I am just helping my friend. So I don't know what he exactly wants. I know he wants to use those names in records as a column names in new table. Could you post an example of that?

    Thank you
    DJ

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You may be able to accomplish this using a cross-tab query. If you look it up in Books Online you will find an excellent explanation and example.

    But I must tell you that it is almost NEVER a good idea to store your data in such a denormalized form. It will make future data searches and maintenance more difficult.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    dynamic sql example:
    you'll have to improve the insert-statement to match the dynamic-table though. Blindman's suggestion might work better for you...

    use monkey
    go

    set nocount on

    create table t1 (id integer identity(1,1), myColumn varchar(10))
    go

    insert into t1 (myColumn) (select 'Col001' union select 'Col002' union select 'Col003' union select 'Col004')

    select * from t1

    declare @id as integer
    , @mysql as varchar(1000)
    set @id = (select min(id) from t1)
    set @mysql = 'CREATE TABLE T2 ('
    while @id <= (select max(id) from t1)
    begin
    set @mysql = @mysql + (select myColumn from t1 where id = @id) + ' varchar(10), '
    set @id = @id + 1
    end
    set @mysql = substring(@mysql, 1, len(@mysql) - 1) + ')'
    select @mysql
    exec (@mysql)
    go

    insert into t2 values ('a','b','c','d')
    select * from t2
    go

    drop table t2
    drop table t1
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •