Professional Web Applications Themes

Rebuild a table - Microsoft SQL / MS SQL Server

Try: create table #t(a int, b int) insert into #t values (1,4) insert into #t values (2,5) select 'A' col1, a from #t union all select 'B' col1, b from #t -- -Vishal "Jane" <JaneK123.com> wrote in message news:072201c3417e$1d51a300$a001280aphx.gbl... > How can I change (programtically) the following table: > > FROM: > > A B > --- --- > 1 4 > 2 5 > > TO: > > column 1 column 2 > --------- --------- > A 1 > A 2 > B 4 > B 5 > > Thank you in advance for your help!...

  1. #1

    Default Re: Rebuild a table

    Try:
    create table #t(a int, b int)
    insert into #t values (1,4)
    insert into #t values (2,5)

    select 'A' col1, a from #t
    union all
    select 'B' col1, b from #t

    --
    -Vishal
    "Jane" <JaneK123.com> wrote in message
    news:072201c3417e$1d51a300$a001280aphx.gbl...
    > How can I change (programtically) the following table:
    >
    > FROM:
    >
    > A B
    > --- ---
    > 1 4
    > 2 5
    >
    > TO:
    >
    > column 1 column 2
    > --------- ---------
    > A 1
    > A 2
    > B 4
    > B 5
    >
    > Thank you in advance for your help!

    Vishal Parkar Guest

  2. #2

    Default Re: Rebuild a table

    It is close to what I need. However, the column name-A/B,
    is manually added in the Select statement. Is there a way
    to programtically capture it and put in the Select
    statement?
    >-----Original Message-----
    >Try:
    >create table #t(a int, b int)
    >insert into #t values (1,4)
    >insert into #t values (2,5)
    >
    >select 'A' col1, a from #t
    >union all
    >select 'B' col1, b from #t
    >
    >--
    >-Vishal
    >"Jane" <JaneK123.com> wrote in message
    >news:072201c3417e$1d51a300$a001280aphx.gbl...
    >> How can I change (programtically) the following table:
    >>
    >> FROM:
    >>
    >> A B
    >> --- ---
    >> 1 4
    >> 2 5
    >>
    >> TO:
    >>
    >> column 1 column 2
    >> --------- ---------
    >> A 1
    >> A 2
    >> B 4
    >> B 5
    >>
    >> Thank you in advance for your help!
    >
    >
    >.
    >
    Jane Guest

  3. #3

    Default Re: Rebuild a table

    since you know which column you are referring in the query there is no need
    to put it programatically.
    if i query information_schema view and find the column name theb indirectly
    im hardcoding it.
    ex:

    select (select column_name from information_schema.columns
    where table_name='t' and ordinal_position = 1)--hardcoded
    col1, a from t
    union all
    select (select column_name from information_schema.columns
    where table_name='t' and ordinal_position = 2)--hardcoded
    col1, b from t

    --
    -Vishal
    "Jane" <JaneK123.com> wrote in message
    news:024401c34182$774ced70$a101280aphx.gbl...
    > It is close to what I need. However, the column name-A/B,
    > is manually added in the Select statement. Is there a way
    > to programtically capture it and put in the Select
    > statement?
    >
    > >-----Original Message-----
    > >Try:
    > >create table #t(a int, b int)
    > >insert into #t values (1,4)
    > >insert into #t values (2,5)
    > >
    > >select 'A' col1, a from #t
    > >union all
    > >select 'B' col1, b from #t
    > >
    > >--
    > >-Vishal
    > >"Jane" <JaneK123.com> wrote in message
    > >news:072201c3417e$1d51a300$a001280aphx.gbl...
    > >> How can I change (programtically) the following table:
    > >>
    > >> FROM:
    > >>
    > >> A B
    > >> --- ---
    > >> 1 4
    > >> 2 5
    > >>
    > >> TO:
    > >>
    > >> column 1 column 2
    > >> --------- ---------
    > >> A 1
    > >> A 2
    > >> B 4
    > >> B 5
    > >>
    > >> Thank you in advance for your help!
    > >
    > >
    > >.
    > >

    Vishal Parkar Guest

  4. #4

    Default Re: Rebuild a table

    however with the dynamic sql u can try following.

    Ex:

    drop table t
    create table t (a int, b int, c int)
    insert into t values(1,2,3)
    insert into t values (3,4,5)

    declare t_name varchar(8000)
    declare qry_str varchar(8000)
    declare tab varchar(8000)
    select tab ='t'
    declare c1 cursor static for select 'select ''' + column_name + '''
    ''col1'',' + column_name + ' from ' + table_name from
    information_schema.columns
    where table_name=tab --just pass the table name here

    open c1
    fetch next from c1 into t_name
    set qry_str =t_name
    while 1=1
    begin
    fetch next from c1 into t_name
    if fetch_status <> 0
    break
    select qry_str = qry_str + ' union all ' +t_name
    end
    close c1
    deallocate c1
    exec (qry_str )


    --
    -Vishal
    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:OrnfcRYQDHA.2636TK2MSFTNGP10.phx.gbl...
    > since you know which column you are referring in the query there is no
    need
    > to put it programatically.
    > if i query information_schema view and find the column name theb
    indirectly
    > im hardcoding it.
    > ex:
    >
    > select (select column_name from information_schema.columns
    > where table_name='t' and ordinal_position = 1)--hardcoded
    > col1, a from t
    > union all
    > select (select column_name from information_schema.columns
    > where table_name='t' and ordinal_position = 2)--hardcoded
    > col1, b from t
    >
    > --
    > -Vishal
    > "Jane" <JaneK123.com> wrote in message
    > news:024401c34182$774ced70$a101280aphx.gbl...
    > > It is close to what I need. However, the column name-A/B,
    > > is manually added in the Select statement. Is there a way
    > > to programtically capture it and put in the Select
    > > statement?
    > >
    > > >-----Original Message-----
    > > >Try:
    > > >create table #t(a int, b int)
    > > >insert into #t values (1,4)
    > > >insert into #t values (2,5)
    > > >
    > > >select 'A' col1, a from #t
    > > >union all
    > > >select 'B' col1, b from #t
    > > >
    > > >--
    > > >-Vishal
    > > >"Jane" <JaneK123.com> wrote in message
    > > >news:072201c3417e$1d51a300$a001280aphx.gbl...
    > > >> How can I change (programtically) the following table:
    > > >>
    > > >> FROM:
    > > >>
    > > >> A B
    > > >> --- ---
    > > >> 1 4
    > > >> 2 5
    > > >>
    > > >> TO:
    > > >>
    > > >> column 1 column 2
    > > >> --------- ---------
    > > >> A 1
    > > >> A 2
    > > >> B 4
    > > >> B 5
    > > >>
    > > >> Thank you in advance for your help!
    > > >
    > > >
    > > >.
    > > >
    >
    >

    Vishal Parkar Guest

  5. #5

    Default Re: Rebuild a table

    Thank you for your help and agree with you that with your
    solution,it will be also a hardcoding one but you have
    given me some idea to move on.
    >-----Original Message-----
    >since you know which column you are referring in the
    query there is no need
    >to put it programatically.
    >if i query information_schema view and find the column
    name theb indirectly
    >im hardcoding it.
    >ex:
    >
    >select (select column_name from information_schema.columns
    >where table_name='t' and ordinal_position = 1)--hardcoded
    > col1, a from t
    >union all
    >select (select column_name from information_schema.columns
    >where table_name='t' and ordinal_position = 2)--hardcoded
    >col1, b from t
    >
    >--
    >-Vishal
    >"Jane" <JaneK123.com> wrote in message
    >news:024401c34182$774ced70$a101280aphx.gbl...
    >> It is close to what I need. However, the column name-
    A/B,
    >> is manually added in the Select statement. Is there a
    way
    >> to programtically capture it and put in the Select
    >> statement?
    >>
    >> >-----Original Message-----
    >> >Try:
    >> >create table #t(a int, b int)
    >> >insert into #t values (1,4)
    >> >insert into #t values (2,5)
    >> >
    >> >select 'A' col1, a from #t
    >> >union all
    >> >select 'B' col1, b from #t
    >> >
    >> >--
    >> >-Vishal
    >> >"Jane" <JaneK123.com> wrote in message
    >> >news:072201c3417e$1d51a300$a001280aphx.gbl...
    >> >> How can I change (programtically) the following
    table:
    >> >>
    >> >> FROM:
    >> >>
    >> >> A B
    >> >> --- ---
    >> >> 1 4
    >> >> 2 5
    >> >>
    >> >> TO:
    >> >>
    >> >> column 1 column 2
    >> >> --------- ---------
    >> >> A 1
    >> >> A 2
    >> >> B 4
    >> >> B 5
    >> >>
    >> >> Thank you in advance for your help!
    >> >
    >> >
    >> >.
    >> >
    >
    >
    >.
    >
    Jane Guest

Similar Threads

  1. Can not rebuild RPMs
    By Christian Wolff in forum Linux Setup, Configuration & Administration
    Replies: 0
    Last Post: August 10th, 07:14 PM
  2. Web site rebuild
    By DavidF in forum Web Design
    Replies: 3
    Last Post: July 14th, 03:19 AM
  3. Rebuild index in SQL server ??
    By SAlva in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: June 30th, 04:42 PM
  4. Index Partition Rebuild is using Full Table Scan
    By Dave in forum Oracle Server
    Replies: 1
    Last Post: December 13th, 03:11 PM
  5. Rebuild a database from a backup set
    By Sybrand Bakker in forum Oracle Server
    Replies: 1
    Last Post: December 6th, 12:22 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139