sql server - SQL 2000 query trouble -
consider following table
create table sample(id, name, numeric, qno, ans1,ans2,ans3) sample data 1, 'vivek', 1, 'a', 'b', '' 2, 'vivek', 1, 'c', 'd', '' 3, 'vivek', 2, 'e', 'f', 'g' 4, 'vivek', 3, 'h', 'i', 'j' 5, 'vijay', 1, 'k', '', 'l' 6, 'vijay', 2, 'm', '', 'n' 7, 'vijay', 2, 'o', '', 'p' 8, 'vikram', 3, 'q', 'r', 's' output expected column names: name, info1, info2, info3 values 'vivek','ab','ef','hij' 'vivek','cd','','' 'vijay','kl', 'mn','' 'vijay','','op','' 'vikram','','','qrs'
converting rows columns.
in other words. 1 answer have 1 row. there can multiple answers 1 question.
the output should have answer numbers columns.
is there quick , easy technique in sql 2000?
firstly, if understand problem, think first result should 'vivek','ab','efg','hij' rather 'vivek','ab','ef','hij'
if so, think following may want.
if object_id('tempdb..#tempsample') not null drop table #tempsample declare @prevname nvarchar(80), @prevrow_number int, @prevqno int select isnull(id, 0) id , isnull(sname, '') sname , isnull(qno,0) qno , ans1, ans2, ans3 , cast(0 int) row_number , cast('' nvarchar(80)) newans1, cast('' nvarchar(80)) newans2, cast('' nvarchar(80)) newans3 #tempsample (select id, sname, qno, ans1, ans2, ans3 sample ) t3 alter table #tempsample add primary key clustered (sname, qno , id) update #tempsample set @prevrow_number = row_number = case when sname = @prevname , qno = @prevqno @prevrow_number +1 else 1 end, @prevname = sname , @prevqno = qno, newans1 = case when sname = @prevname , qno=1 newans1 + ans1+ans2+ans3 else newans1 end , newans2 = case when sname = @prevname , qno=2 newans2 + ans1+ans2+ans3 else newans2 end , newans3 = case when sname = @prevname , qno=3 newans3 + ans1+ans2+ans3 else newans3 end #tempsample with(tablockx) option (maxdop 1) select sname, max(newans1) ans1 , max(newans2) ans2, max(newans3) ans3 #tempsample group sname, row_number order sname
had similar problem sql 2000 recently, credit must go this article on sqlservercentral
Comments
Post a Comment