SQL 2000 query trouble

Question

consider the 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 into columns.

in other words. one answer will have one row. there can be multiple answers to one question.

the output should have answer numbers as columns.

is there any quick and easy technique in sql 2000?

Answer 1

Firstly, if I understand your problem, then I think your first result should be 'vivek','ab','efg','hij' rather than 'vivek','ab','ef','hij'

If so, then I think the following may do what you want.

IF OBJECT_ID('TempDB..#TempSample') IS NOT NULL
    DROP TABLE #TempSample

DECLARE @PrevName  nvarchar(80),
@PrevRow_number     INT,
@PrevQno int 


select
    isNull(id, 0)   as id , 
    isNull(sname, '')   as sName , 
    IsNull(qno,0)       as qno ,
    ans1, 
    ans2, 
    ans3  , 
    CAST(0 AS INT) AS row_number ,
    Cast('' as Nvarchar(80)) as newAns1, 
    Cast('' as Nvarchar(80)) as newAns2, 
    Cast('' as Nvarchar(80)) as newAns3
    INTO #TempSample
    from 
    (select id, sname, qno, ans1, ans2, ans3 from sample ) t3


ALTER TABLE #TempSample ADD PRIMARY KEY CLUSTERED (sName, qno , id)

UPDATE #TempSample 
    SET @PrevRow_number = row_number = CASE WHEN   sName = @PrevName and qno = @PrevQno THEN @PrevRow_number +1 ELSE 1 END,
        @PrevName  = sname ,
        @PrevQno   = qno,
        newAns1                        = case when sName = @PrevName and qno=1 Then newAns1 + ans1+ans2+ans3 else newAns1 end ,
        newAns2                        = case when sName = @PrevName and qno=2 Then newAns2 + ans1+ans2+ans3 else newAns2 end ,
        newAns3                        = case when sName = @PrevName and qno=3 Then newAns3 + ans1+ans2+ans3 else newAns3 end 
FROM #TempSample  WITH(TABLOCKX)
OPTION (MAXDOP 1)


Select sName,  Max(newAns1) as Ans1 , Max(newAns2) as Ans2, Max(newAns3) as Ans3 
From #TempSample 
Group by sName, Row_Number
Order By sName  

Had a similar problem with Sql 2000 recently, so credit must go to this article on SqlServerCentral