Format output of a query to a specific layout 1

[Pereayel]

Hi all,

I'm new to this forumn and need your help. Below is the query that I am running and under it is the resulting table.

SELECT DISTINCT school.schoolID, field.[name],
degree. [name] AS degree, degree.degreeID,
field.fieldID
FROM school_field INNER JOIN
school_field_degree ON school_field.schoolID = school_field_degree.schoolID AND
school_field.fieldID = school_field_degree.fieldID INNER JOIN
school ON school_field.schoolID = school.schoolID INNER JOIN
degree ON school_field_degree.degreeID = degree.degreeID INNER JOIN
field ON school_field.fieldID = field.fieldID
WHERE school.schoolID = 136 AND school.siteID = 7
ORDER by school.schoolID
---------------------------------------------------------
result
----------------------------------------------------------
136 Early Childhood Education Bachelors 2 48
136 Education Bachelors 2 51
136 Education Masters 3 51
136 Elementary Education Bachelors 2 52

----------------------------------------------------------
Myquestion is how can I format the result to look like the one below? Thanks


136 Early Childhood Education Bachelors
136 Education Bachelors Masters
136 Elementary Education Bachelors

 

[vongrunt]

Bachelors Masters in two separated columns... or one?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[gmmastros]

I suggest you create a user defined function that returns the degree. Something like this...

Create Function fn_GetDegreesForSchool
	(
	@SchoolId Integer
	)
Returns VarChar(1000)
AS
Begin
	Declare @Output VarChar(100)
	Set @Output = ''

	Select @Output = @Output + Name + ' '
	From   Degree
           Inner join School_Field_Degree
               On Degree.DegreeId = School_Field_Degree.DegreeId
    Order By Degree.Name

	Set @Output = RTrim(@Output)

	Return @Output
End

Then you can use it like so...

Select SchoolId,
       dbo.fn_GetDegreesForSchool(SchoolId) As Degree
From   School

With a little work, you should be able to integrate this in to the query above.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Pereayel]

Bachelors Masters in two separated columns

 

[vongrunt]

OK then... George, now try again .

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[gmmastros]

Seperate columns can be problematic.

How many different degrees are available for each school? Will this ever change?



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Pereayel]

There are 4 degrees available for each school and will remain as is. They are Associates, Bachelors, Masters, and Doctoral

 

[gmmastros]

You may need to modify this a little, but with some tweaking, you should be able to get it to work.

SELECT     DISTINCT school.schoolID, field.[name], 
           degree. [name] AS degree, degree.degreeID, 
           field.fieldID
FROM       school_field INNER JOIN
           school_field_degree ON school_field.schoolID = school_field_degree.schoolID AND 
           school_field.fieldID = school_field_degree.fieldID INNER JOIN
           school ON school_field.schoolID = school.schoolID INNER JOIN
           degree ON school_field_degree.degreeID = degree.degreeID INNER JOIN
           field ON school_field.fieldID = field.fieldID
           [!]Inner Join (
             Select SchoolId,
             		Min(Associates.Name) As Associates,
             		Min(Bachelors.Name) As Bachelors,
             		Min(Masters.Name) As Masters,
             		Min(Doctoral.Name) As Doctoral
             From	School_Field_Degree
             		Left Join Degree As Associates
             			On School_Field_Degree.DegreeId = Associates.DegreeId
             			And Associates.Name = 'Associates'
             		Left Join Degree As Bachelors
             			On School_Field_Degree.DegreeId = Bachelors.DegreeId
             			And Bachelors.Name = 'Bachelors'
             		Left Join Degree As Masters
             			On School_Field_Degree.DegreeId = Masters.DegreeId
             			And Masters.Name = 'Masters'
             		Left Join Degree As Doctoral
             			On School_Field_Degree.DegreeId = Doctoral.DegreeId
             			And Doctoral.Name = 'Doctoral'
             ) As DegreeList On School.SchoolId = DegreeList.SchoolId[/!]
Group By SchoolId
WHERE school.schoolID = 136 AND school.siteID = 7
ORDER by school.schoolID

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Sorry, I forgot to mention that you will need to modify the select list to include...

DegreeList.Associates, DegreeList.Bachelors, DegreeList.Masters, DegreeList.Doctoral



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Pereayel]

I am now getting the following error even when I include the field in the GROUP BY clause.

Server: Msg 8118, Level 16, State 1, Procedure t_getDegreeFieldBySchool1, Line 7
Column 'School_Field_Degree.schoolID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.


Pereayel

 

[Pereayel]

b

 

[gmmastros]

Sorry. It's hard when you don't have the tables to test on.

Change:
) As DegreeList On School.SchoolId = DegreeList.SchoolId


To:

Group By SchoolId
) As DegreeList On School.SchoolId = DegreeList.SchoolId

Basically, your adding a group by condition to the subquery.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom