MS SQL

creating headr name dynamically

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Oct 2009

Posts: 1

Reputation: shangita is an unknown quantity at this point

Solved Threads: 0

shangita

Offline

Newbie Poster

creating headr name dynamically

Oct 28th, 2009

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.

•

Join Date: Sep 2009

Posts: 42

Reputation: kplcjl is an unknown quantity at this point

Solved Threads: 5

kplcjl

Offline

Light Poster

Nov 5th, 2009

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:

 Help with Code Tags 
 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

•

Join Date: Sep 2009

Posts: 42

Reputation: kplcjl is an unknown quantity at this point

Solved Threads: 5

kplcjl

Offline

Light Poster

Nov 5th, 2009

Whoops when I modified the last select

 Help with Code Tags 
 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