creating headr name dynamically

Expand Post »

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.

Similar Threads

Creating table dynamically in JavaScript / DHTML / AJAX
Creating a new array dynamically for checkboxes? in PHP
Creating a popup menu on mouse over in JavaScript / DHTML / AJAX
Creating Images dynamically in Javascript in ASP.NET
Creating Conrols dynamically in ASP.NET

shangita

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Oct 2009

Permalink

Nov 5th, 2009

Re: creating headr name dynamically

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:

MS SQL Syntax (Toggle Plain Text)
--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
--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

Reputation Points: 14

Solved Threads: 12

Junior Poster

Offline

146 posts
since Sep 2009

Permalink

Nov 5th, 2009

Re: creating headr name dynamically

Whoops when I modified the last select

MS SQL Syntax (Toggle Plain Text)
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
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

Last edited by kplcjl; Nov 5th, 2009 at 12:47 am. Reason: Clarify one statement

kplcjl

Reputation Points: 14

Solved Threads: 12

Junior Poster

Offline

146 posts
since Sep 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Stored Procedure with Increments

Next Thread in MS SQL Forum Timeline: strong sa password error

DaniWeb Message

Cancel Changes

User Name
Password