Complicated Cross-Tab Query

I have the following table and data:

tblDepartments: (each department can only have a maximum of 3 sections attached to it)

Columns: DepartmentName , SectionName

Row1: dep1, sec1.0
Row2: dep1, sec1.1
Row3: dep1, sec1.2

Row 4: dep2, sec2.0

Row 5: dep3, sec3.0
Row 6: dep3, sec3.1

I need to derive the following table from tblDepartments :

Columns: DepartmentName, SectionName1, SectionName2,
SectionName3

Row1: dep1, sec1.0, sec1.1, sec1.2
Row2: dep2, sec2.0, '', ''
Row3: dep3, sec3.0, sec3.1, ''

Any ideas?

SELECT DepartmentName,[1],[2],[3]

FROM(

SELECT DepartmentName,SectionName,(SELECT rFROM(SELECT SectionName,row_number()OVER(ORDERBY SectionName)AS rFROM tblDepartments d2WHERE d2.DepartmentName=d.DepartmentName) t1WHERE t1.SectionName=d.SectionName)As col

FROM tblDepartments d

) t2

PIVOT

(

MAX(SectionName)

FOR colIN([1],[2],[3]))AS pvt

ORDERBY DepartmentName

|||

Motley wrote:

SELECT DepartmentName,[1],[2],[3]

FROM(

SELECT DepartmentName,SectionName,(SELECT rFROM(SELECT SectionName,row_number()OVER(ORDERBY SectionName)AS rFROM tblDepartments d2WHERE d2.DepartmentName=d.DepartmentName) t1WHERE t1.SectionName=d.SectionName)As col

FROM tblDepartments d

) t2

PIVOT

(

MAX(SectionName)

FOR colIN([1],[2],[3]))AS pvt

ORDERBY DepartmentName

It looks like PIVOT is only for SQl Server 2005.

We haven't upgraded to that yet.|||Ah. So is row_number. Still doable, just much more difficult.