Help to retrieve Column# - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help to retrieve Column#

Author

Topic

tripplx
Starting Member

3 Posts

Posted - 2007-04-06 : 17:00:33

Hi,

Help with SQL2000

TBL001
examNo Q1 Q2 Q3 Q4
------ -- -- -- --
1234 1 0 1 1
1324 1 1 1 1

I need help retrieving the questions answers at the same time replacing the value and writing into another file.
1 = y, 0 = n.

TBL002
Qno Examno1 examno2
--- ------- -------
Q1 y y
Q2 n y
Q3 y y
Q4 y y

This is what the finished table will look like.

I tried using CASE with Q'Cast(int)' to loop thru rows and replaceing the value but i cant get it to work.

Any help would be great as i am STUCK!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-06 : 17:20:49

What you need is the PIVOT operator. Read up documentation about it. I have not used it so am not very familiar with it.

************************
Life is short. Enjoy it.
************************

tripplx
Starting Member

3 Posts

Posted - 2007-04-06 : 17:40:35

I was searching for this operator but all i see is for 2005?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-06 : 19:22:02

PIVOT is not available for SQL 2000.

What is the PK for TBL001 ?

tripplx
Starting Member

3 Posts

Posted - 2007-04-06 : 22:52:13

PK for tble001 is Examno
pk for tbl002 is RowID (which is also Question number[Q1-Q3 from Tble001])

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-04-07 : 02:52:18

Check the following code, it may help u solve u r problem

Declare @t table ( i int identity(1, 1), exmno int, q1 int, q2 int, q3 int, q4 int )

Insert into @t
Select 1234, 1, 0, 1, 1 union
Select 1324, 1, 1, 1, 1 union
Select 1432, 1, 0, 0, 1

-- Select * from @t

Select 'q1' as 'QNo',
q1 as '1234',
( select q1 from @t where i = t.i + 1 )as '1324',
( select q1 from @t where i = t.i + 2 )as '1432'
From @t t
Where q1 is not null
and i = 1
union
Select 'q2' as 'QNo',
q2,
( select q2 from @t where i = t.i + 1 ),
( select q2 from @t where i = t.i + 2 )
From @t t
Where q2 is not null
and i = 1
union
Select 'q3' as 'QNo',
q3,
( select q3 from @t where i = t.i + 1 ),
( select q3 from @t where i = t.i + 2 )
From @t t
Where q3 is not null
and i = 1
union
Select 'q4' as 'QNo',
q4,
( select q4 from @t where i = t.i + 1 ),
( select q4 from @t where i = t.i + 2 )
From @t t
Where q4 is not null
and i = 1

it is static, to fit any no. of Examno u have to build dynamic sql statement

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-07 : 03:17:32

Also see if this helps

http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-07 : 08:03:57

This will get you a start

-- Prepare sample data
DECLARE @TBL001 TABLE (examNo int, Q1 int, Q2 int, Q3 int, Q4 int)

insert	@tbl001
select	1234, 1, 0, 1, 1 union all
select	1324, 1, 1, 1, 1

-- Show the expected output
SELECT		Qno,
		MAX(CASE WHEN ExamNo = '1234' THEN Done END) AS ExamNo1,
		MAX(CASE WHEN ExamNo = '1324' THEN Done END) AS ExamNo2
FROM		(
			SELECT	'Q1' AS Qno, ExamNo, CASE WHEN Q1 = 0 THEN 'n' ELSE 'y' END AS Done FROM @tbl001 UNION ALL
			SELECT	'Q2', ExamNo, CASE WHEN Q2 = 0 THEN 'n' ELSE 'y' END FROM @tbl001 UNION ALL
			SELECT	'Q3', ExamNo, CASE WHEN Q3 = 0 THEN 'n' ELSE 'y' END FROM @tbl001 UNION ALL
			SELECT	'Q4', ExamNo, CASE WHEN Q4 = 0 THEN 'n' ELSE 'y' END FROM @tbl001
		) AS d
GROUP BY	Qno
ORDER BY	Qno

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources