Can you do a cross join with a static list of columns?

Steve MunLeeuw


[quoted text, click to view]

Ahh, sorry, should have been more precise, cross-join in the sql select
statement.

I use a table valued function with my static matrix column headers, then use
a CROSS JOIN to it

For example:

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[fn_GetAssessmentSeries]') and xtype in (N'FN', N'IF',
N'TF'))

drop function [dbo].[fn_GetAssessmentSeries]

GO



CREATE FUNCTION fn_GetAssessmentSeries()

RETURNS @AssessmentSeries TABLE

(

SeriesName varchar(250),

SeriesSort int,

ChartSort int,

FilterFromChart int,

stType varchar(100)

)AS

/******************************************************************************

** File:

** Name: fn_GetAssessmentSeries

** Desc: Creates a table of series values. Not all series contain data,
cross joining

** to this table allows us to create matrix queries with no missing columns.

**

** Parameters:

** Input Output

** ---------- -----------

**

** Change History

*******************************************************************************

** Author: Date: Description:

** -------- -------- -------------------------------------------

** Steve MunLeeuw07/06/2006 Created for Assessment reports

*******************************************************************************/

BEGIN

INSERT INTO @AssessmentSeries

SELECT 'Students Tested', 0, null, 0, 'Count'

UNION SELECT 'Percent Tested', 1, null, 0, 'Percent'

UNION SELECT 'Mean Scaled Score', 2, null, 0, 'Score'

UNION SELECT 'Far Below Basic', 3, 2, 1, 'Percent'

UNION SELECT 'Below Basic', 4, 1, 1, 'Percent'

UNION SELECT 'Basic', 5, 0, 1, 'Percent'

UNION SELECT 'Proficient', 6, 3, 1, 'Percent'

UNION SELECT 'Advanced', 7, 4, 1, 'Percent'

UNION SELECT 'Below Proficient', 8, null, 0, 'Percent'

UNION SELECT 'At or above Proficient', 9, null, 0, 'Percent'



RETURN

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO


Here's part of my select clause:
Stat.SeriesName,

Stat.SeriesName,

Stat.SeriesSort,

Stat.ChartSort,

Stat.FilterFromChart,

Stat.stType

FROM

vw_rpt_Assessments v

INNER JOIN StudentGroupSet sgs

ON v.StudentGroupSetID = sgs.studentGroupSetID

INNER JOIN Assessment a

ON v.AssessmentID = a.AssessmentId

INNER JOIN GradeLevel gl

ON v.GradeLevelID = gl.GradeLevelID

CROSS JOIN fn_GetAssessmentSeries() AS Stat



[quoted text, click to view]

Did you understand the cross join suggestion? It's a way to add columns that
will always show up. Also the "union" command is a way to add data to a
query as well.
--
"Everyone knows something you don't know"


[quoted text, click to view]