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
 General SQL Server Forums
 New to SQL Server Programming
 create dynamic table

Author  Topic 

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-02-28 : 01:08:32
hi,

this is my sample code:

create table c_product
(id int
,name nvarchar(50)
)

insert into c_product
select 1,'table' union all
select 2,'chair' union all
select 3,'tavolo'

create table c_action
(id int
,name nvarchar(50)
)

insert into c_action
select 1,'invest' union all
select 2,'purchase' union all
select 3,'bid' union all
select 4,'sell'

create table t_person
(id_person int
,id_product int
,id_action int
)

insert into t_person
select 1, 1, 1 union all
select 1, 1, 3 union all
select 1, 2, 4 union all
select 2, 3, 1 union all
select 2, 3, 4


what i want is to get dynamic table with following outlook reading data from t_person with table structure from c_product and c_action.
outlook should be like:

data for t_person.id_person = 1
\ | invest | purchase | bid | sell
-----------------------------------------
table| yes | NULL | yes | NULL
chair| NULL | NULL | NULL | yes
tavol| NULL | NULL | NULL | NULL


where if i add additional product or additional action, this table will have another row/column with belonging data. (both code tables c_product and c_action will not grow big; max 10 rows per each table). and this table will show only data for one person at the time.

thank you for any hints.

p.s.: i'm using sql server 2000 or 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 09:39:20
[code]
SELECT pr.name AS [\],
MAX(CASE WHEN a.name='invest' THEN 'yes' ELSE NULL END) AS invest,
MAX(CASE WHEN a.name='purchase' THEN 'yes' ELSE NULL END) AS purchase,
MAX(CASE WHEN a.name='bid' THEN 'yes' ELSE NULL END) AS bid,
MAX(CASE WHEN a.name='sell' THEN 'yes' ELSE NULL END) AS sell
FROM t_person p
INNER JOIN c_product pr
ON pr.id=p.id_product
INNER JOIN c_action a
ON a.id=p.id_action
WHERE p.id_person=1
GROUP BY pr.name
[/code]
Go to Top of Page
   

- Advertisement -