creating headr name dynamically

[shangita]

I created 2 tables

1 st table as csat
csat_code csat_ou_cod csat_csaeid cus name
CSA00001 ADM00001 6825 CUS08349
CSA00002 ADM00001 6826 CUS08347
CSA00003 ADM00001 6832 CUS08351
CSA02547 ADM00001 6824 CUS04150

table2 as csat_survey

surveycode csat_code qns id ans id
SUR00001 CSA02547 1 3
SUR00002 CSA02547 4 7

now i want the resut is

csat_code csat_case_id qns id(column name)

like the row field of qns id ( table2) is cloumn for my solution table. and the number of qns also varied .so i have to create the dynamic header.. plz any one help me? how can i get this solution?

Thanks in advance

Sangita.

[kplcjl]

1. Your format is almost unreadable. I suggest putting commas between column names and if your column name has a space, bracket it like <cus name>
2. You can't have a dynamic number of columns in a single result set
3. What you want isn't none too clear either.
4. if you want data combined from several rows into one row something like this:
CSA02547 S=SUR00001,Q=1,A=3;S=SUR00002,Q=4,A=7
CUS08347 S=SUR00001,Q=1,A=4;S=SUR00002,Q=3,A=1;S=SUR00003,Q=7,A=6
here is the code that will produce that result:

--Initialize two tables
declare @tbl1 table(c_code varchar(20), ou_code varchar(20), id int null,CName varchar(20))
insert into @tbl1
select 'CSA00001', 'ADM00001', 6825, 'CUS08349'
union select 'CSA00002', 'ADM00001', 6826, 'CUS08347'
union select 'CSA00003', 'ADM00001', 6832, 'CUS08351'
union select 'CSA02547', 'ADM00001', 6824, 'CUS04150'
declare @tbl2 table(surveycode varchar(20), c_code varchar(20), Qns_id int ,Ans_id int)
insert into @tbl2
select 'SUR00001', 'CSA02547', 1, 3
union select 'SUR00001', 'CUS08347', 1, 4
union select 'SUR00002', 'CUS08347', 3, 1
union select 'SUR00003', 'CUS08347', 7, 6
union select 'SUR00002', 'CSA02547', 4, 7

-- calculate cumulative total
declare @tbl3 table(id int identity primary key,
surveycode varchar(20), c_code varchar(20), Qns_id varchar(20),
Ans_id varchar(20),Sum_id int null, S_Ans varchar(max) null)
insert into @tbl3 (surveycode, c_code, Qns_id, Ans_id) select * from @tbl2 order by c_code,surveycode
update @tbl3 set S_Ans='S=' + surveycode + ',Q=' + Qns_id + ',A=' + Ans_id
update  @tbl3 set Sum_id=id where id in  (select min(id) from @tbl3 group by c_code)
while @@rowcount > 0
update a SET S_Ans=a.S_Ans + ';' + b.S_Ans, Sum_id=a.Sum_id+1
from @tbl3 a join @tbl3 b on b.id=a.Sum_id+1 and a.c_code=b.c_code
select c_code,S_Ans from @tbl3 where Sum_id>0
order by c_code

[kplcjl]

Whoops when I modified the last select

delete from @tbl3 where Sum_id is null
select a.*,S_Ans from @tbl1 a left join @tbl3 b on a.c_code=b.c_code
order by c_code

for results
CSA00001 ADM00001 6825 CUS08349 NULL
CSA00002 ADM00001 6826 CUS08347 S=SUR00001,Q=1,A=4;S=SUR00002,Q=3,A=1;S=SUR00003,Q=7,A=6
CSA00003 ADM00001 6832 CUS08351 NULL
CSA02547 ADM00001 6824 CUS04150 S=SUR00001,Q=1,A=3;S=SUR00002,Q=4,A=7
I realized I'd typo'd the column value and had to correct the wrong value to be CSA00002