SQL Query for creating table with column as query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query for creating table with column as query

Author

Topic

kania
Starting Member

4 Posts

Posted - 2011-01-21 : 02:03:03

Hi All

I am newbie in SQL server 2000 and i do really need ur guidance to solve this problem

i have a table named TIDlist
which look like this...

TID item1 item2
1 a b
2 a
3 b

what i need to be doing is to convert this table into binary format
below

TID a b
1 1 1
2 1 0
3 0 1

MY PROBLEM IS:
a) how to create the table with query result as the column?

i've tried:
Create table TIDbinary(TID integer,Select distinct(prodID) from order details integer) and it absolutely didn't work

b) after creating the table of TIDbinary,,how to fill it with 1,indicating TID consist of the product or 0 otherwise

I really appreciate any suggestion,or comment to this problem
May god bless u for ur kindness

Best regards

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-21 : 02:20:29

[code]
select TID,
case when item1 = 'a' then 1 else 0 end as a,
case when item2 = 'b' then 1 else 0 end as b
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

kania
Starting Member

4 Posts

Posted - 2011-01-21 : 02:44:41

thanks 4 giving me an idea khtan

anyway,i am still having the problem to create the table TIDbinary

i want to create the column of TIDbinary with query selection
is that possible if let's say i write this one
create table TIDbinary (TID integer,select distinct(productid) from order details)
i have tried it and it didn't work at all:(

is there any other SQL query to create the table whose name of column comes from query result"select"???

Thanks in advance for advice:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-21 : 03:19:17

[code]
select TID,
case when item1 = 'a' then 1 else 0 end as a,
case when item2 = 'b' then 1 else 0 end as b
into TIDbinary
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

kania
Starting Member

4 Posts

Posted - 2011-01-21 : 03:50:53

ohy,i've just known that select into will work with this...
but..

i am so sorry i forgot to tell u a very important things earlier

the number of item column is less than the number of productid(a,b)

so here is the real data:

TID item1 item2 item3 item4
1 a b
2 b c d e
3 c d
4 d e

it has to be converted like this

TID a b c d e
1 1 1 0 0 0
2 0 1 1 1 1
3 0 0 1 1 0
4 0 0 0 1 1

i've also tried the query u given to me
select TID,
case when item1 = 'a' then 1 else 0 end as a,
case when item2 = 'b' then 1 else 0 end as b
into TIDbinary
from yourtable

but there is an error message says:"incorrect syntact near a":(
what's wrong..

thanks

kania
Starting Member

4 Posts

Posted - 2011-01-21 : 08:46:37

it works!!!! thank u so much khtan^_^

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources