Derived Tables - 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

General SQL Server Forums

New to SQL Server Programming

Derived Tables

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2006-12-12 : 05:06:48

Hi. We have to create an export from our system to be imported into another system. To get the data out we need to create some SQl but we're struggling a bit.

I presently have the following code

SELECT     ProSolution.dbo.StudentDetail.RefNo, ProSolution.dbo.StudentDetail.FirstForename, ProSolution.dbo.StudentDetail.Surname, 

                      ProSolution.dbo.StudentDetail.MobileTel, ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID, ProSolution.dbo.Enrolment.CompletionStatusID, 

                      ProSolution.dbo.Offering.Code

FROM         ProSolution.dbo.StudentDetail INNER JOIN

                      ProSolution.dbo.Enrolment ON ProSolution.dbo.StudentDetail.StudentDetailID = ProSolution.dbo.Enrolment.StudentDetailID INNER JOIN

                      ProSolution.dbo.Offering ON ProSolution.dbo.Enrolment.OfferingID = ProSolution.dbo.Offering.OfferingID

WHERE     (ProSolution.dbo.StudentDetail.AcademicYearID = '06/07') AND (ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID = '9') AND 

                      (ProSolution.dbo.Enrolment.CompletionStatusID = '1')

The above code returns the data one line per course but we need it to be one line per student with all their courses on one line too, like follows.

567897 Tom Smith 07111 111111 TCFT1 CKSAN1 DHICS

Can anyone give us any guidance please?

Thanks
Chip

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 05:15:47

Nicer to read with aliases

SELECT		sd.RefNo,
		sd.FirstForename,
		sd.Surname,
		sd.MobileTel,
		sd.RestrictedUseIndicatorID,
		e.CompletionStatusID,
		o.Code
FROM		ProSolution.dbo.StudentDetail AS sd
INNER JOIN	ProSolution.dbo.Enrolment AS e ON e.StudentDetailID = sd.StudentDetailID
INNER JOIN	ProSolution.dbo.Offering AS o ON o.OfferingID = e.OfferingID
WHERE		sd.AcademicYearID = '06/07'
		AND sd.RestrictedUseIndicatorID = '9'
		AND e.CompletionStatusID = '1'

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 05:23:29

For the output you describe above, you must do a CROSSTAB or PIVOT report.

Peter Larsson
Helsingborg, Sweden

chipembele
Posting Yak Master

106 Posts

Posted - 2006-12-12 : 05:33:07

quote:
Originally posted by Peso

For the output you describe above, you must do a CROSSTAB or PIVOT report.

Peter Larsson
Helsingborg, Sweden

I've tried that but it's not like we have code1, code2, code3, code4 to create the columns on. I've used crosstab case before but this seems different.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 05:37:27

Do you always have the same columns in the output?
What is the headers in the output you posted?

Peter Larsson
Helsingborg, Sweden

chipembele
Posting Yak Master

106 Posts

Posted - 2006-12-12 : 06:12:17

quote:
Originally posted by Peso

Do you always have the same columns in the output?
What is the headers in the output you posted?

Peter Larsson
Helsingborg, Sweden

normally it would be

Student Ref First Name Surname Code

Ideally it should be

Student Ref First Name Surname Code1 Code2 Code3 Code 4

I'm thinking subqueries but to be honest i'm at a loss.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 07:42:49

How many codes at most do you expect?

Peter Larsson
Helsingborg, Sweden

chipembele
Posting Yak Master

106 Posts

Posted - 2006-12-12 : 08:34:31

quote:
Originally posted by Peso

How many codes at most do you expect?

Peter Larsson
Helsingborg, Sweden

At most it would be 9

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 08:36:58

Would it be ok to always show nine columns for codes?
If there are no codes, NULL is displayed.

Peter Larsson
Helsingborg, Sweden

chipembele
Posting Yak Master

106 Posts

Posted - 2006-12-12 : 08:59:42

quote:
Originally posted by Peso

Would it be ok to always show nine columns for codes?
If there are no codes, NULL is displayed.

Peter Larsson
Helsingborg, Sweden

Yes, that would be fine. I did it through a subquery before and it had nulls which werent a problem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 10:00:28

Something like this?

SELECT		sd.RefNo,
		sd.FirstForename,
		sd.Surname,
		sd.MobileTel,
		sd.RestrictedUseIndicatorID,
		e.CompletionStatusID,
		o.Code,
		0 AS CodeNum
INTO		#Temp
FROM		ProSolution.dbo.StudentDetail AS sd
INNER JOIN	ProSolution.dbo.Enrolment AS e ON e.StudentDetailID = sd.StudentDetailID
INNER JOIN	ProSolution.dbo.Offering AS o ON o.OfferingID = e.OfferingID
WHERE		sd.AcademicYearID = '06/07'
		AND sd.RestrictedUseIndicatorID = '9'
		AND e.CompletionStatusID = '1'

UPDATE	t1
SET	t1.CodeNum = (SELECT COUNT(*) FROM #Temp t2 WHERE t2.RefNo = t1.RefNo AND t2.Code <= t1.Code)
FROM	#Temp t1

SELECT		RefNo,
		FirstForename,
		Surname,
		MobileTel,
		RestrictedUseIndicatorID,
		CompletionStatusID,
		MAX(CASE WHEN CodeNum = 1 THEN Code END) AS Code1,
		MAX(CASE WHEN CodeNum = 2 THEN Code END) AS Code2,
		MAX(CASE WHEN CodeNum = 3 THEN Code END) AS Code3,
		MAX(CASE WHEN CodeNum = 4 THEN Code END) AS Code4,
		MAX(CASE WHEN CodeNum = 5 THEN Code END) AS Code5,
		MAX(CASE WHEN CodeNum = 6 THEN Code END) AS Code6,
		MAX(CASE WHEN CodeNum = 7 THEN Code END) AS Code7,
		MAX(CASE WHEN CodeNum = 8 THEN Code END) AS Code8,
		MAX(CASE WHEN CodeNum = 9 THEN Code END) AS Code9
FROM		#Temp
GROUP BY	RefNo,
		FirstForename,
		Surname,
		MobileTel,
		RestrictedUseIndicatorID,
		CompletionStatusID
ORDER BY	RefNo,
		FirstForename,
		Surname

DROP TABLE #Temp

Peter Larsson
Helsingborg, Sweden

EDIT: Numbers in red

chipembele
Posting Yak Master

106 Posts

Posted - 2006-12-12 : 10:10:10

Excellent. Certainly looks like how it should. Only problem is each code 1-9 has the same course in it so theres 9 of the same course rather than the different ones they would have at each position.

chipembele
Posting Yak Master

106 Posts

Posted - 2006-12-12 : 10:15:25

Actually, i've just edited it a bit. not sure if i've done right but it seems to be right now. This is what i've done

MAX(CASE WHEN CodeNum = 1 THEN Code END) AS Code1,
MAX(CASE WHEN CodeNum = 2 THEN Code END) AS Code2,
MAX(CASE WHEN CodeNum = 3 THEN Code END) AS Code3,
MAX(CASE WHEN CodeNum = 4 THEN Code END) AS Code4,
MAX(CASE WHEN CodeNum = 5 THEN Code END) AS Code5,
MAX(CASE WHEN CodeNum = 6 THEN Code END) AS Code6,
MAX(CASE WHEN CodeNum = 7 THEN Code END) AS Code7,
MAX(CASE WHEN CodeNum = 8 THEN Code END) AS Code8,
MAX(CASE WHEN CodeNum = 9 THEN Code END) AS Code9

If what I've done is correct then our problem appears sorted.

Thanks so much for your help. Very much appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 10:29:35

My bad with Copy&Paste.
Well done!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 10:33:43

Is sorted codes a problem?

Peter Larsson
Helsingborg, Sweden

chipembele
Posting Yak Master

106 Posts

Posted - 2006-12-13 : 02:50:39