Query - 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)

Query

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-08 : 08:57:32

Hi all,

I have one table with structure and instances under:


CREATE TABLE Course
(
    S_No int,
    course nvarchar(25)     
)
 
INSERT Course
SELECT 1,'math 1'       UNION ALL
SELECT 1,'phisisc 1'    UNION ALL
SELECT 2,'math 1'       UNION ALL
SELECT 3,'culture'      UNION ALL
SELECT 3,'history'      UNION ALL
SELECT 3,'art'


S_No        course
----------- -------------------------
1           math 1
1           phisisc 1
2           math 1
3           culture
3           history
3           art

Then I want to get the result similar to:


S_No        Course1                   Course2                   Course3
----------- ------------------------- ------------------------- -------------------------
1           math 1                    phisisc 1                 
2           math 1                                              
3           art                       culture                   history

please create a query without using RANKING FUNCTION and PIVOT.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-08 : 09:03:36

Ranking and Pivot are not available in SQL Server 2000.
Is this the very same question as posted here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130859

N 56°04'39.26"
E 12°55'05.63"

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-08 : 09:11:17

In fact this topic is the same that topic.

I want to achieve this result to SQL Server 2000.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-08-08 : 14:59:38

[code]Declare @T Table
(
S_No int,
course nvarchar(25)
)

INSERT @T
SELECT 1,'math 1' UNION ALL
SELECT 1,'phisisc 1' UNION ALL
SELECT 2,'math 1' UNION ALL
SELECT 3,'culture' UNION ALL
SELECT 3,'history' UNION ALL
SELECT 3,'art'

--select * from @T

Select S_No,
Max(Case When S_No in (1,2,3) and course in('math 1','art') then course else '' end) as Course1,
Max(Case When S_No in (1,2,3) and course in('phisisc 1','culture') then course else '' end) as Course2,
Max(Case When S_No in (1,2,3) and course = 'history' then course else '' end) as Course3
from @T
Group by S_No[/code]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-09 : 03:53:50

thank you sodeep.
but imagin I changed the data then what will be result?
It is easier that use this query!



select s_no=1, [course1]='math 1', [course2]='phisisc 1', [course3]=''    
union
select 2,'math 1', '', ''
union
select 3,'art', 'culture', 'history'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources