Rows To Columns -Transpose or Pivot or?

[RC]

I'm not quite getting a handle on how to transpose, or pivot the
CLASS_BUILDER table in example #1 to get the desired results for the
SCHEDULES table shown in example # 2. The real table has 12 more data
elements than are shown in the examples, such as Gender, Birth Date,
Ethnicity, etc, which are all one per record like Grade and will need to be
kept with the record as well. It is as though the Sect_ID field is the one
that needs to be transposed, or pivoted. The Sect_ID records can vary as
little as one per student to many more than the four Sect_IDs shown in
example # 2.

EXAMPLE # 1:
tblCLASS_BUILDER
Stut_ID Status Name Grade School Sect_ID
123 A Smith John 03 4050 2302-01
123 A Smith John 03 4050 2701-02
123 A Smith John 03 4050 4102-04
456 A Jones David 05 4050 2705-03
456 A Jones David 05 4050 2302-02
456 A Jones David 05 4050 1703-05
456 A Jones David 05 4050 4502-01
789 A Cook Jane 10 3050 1702-02
789 A Cook Jane 10 3050 4102-03


EXAMPLE # 2:
tblSCHEDULES
Stu_ID Status Name Grade School Sect_ID1 Sect_ID2 Sect_ID3
Sect_ID4
123 A Smith John 03 4050 2302-01 2701-02 4102-04
456 A Jones David 05 4050 2705-03 2302-06 1703-05
4502-01
789 A Cook Jane 10 3050 1702-02 4102-03

I wasn't sure if I should have posted this one here, yet I will greatly
appreciate all the help given.

Thanks,
RC

 

[vbasean]

I think Karl Dewey answered a similar question that may help you

http://www.microsoft.com/office/com...&p=1&tid=65defbad-8513-483b-a8cc-e987395b8a30

 

[RC]

Thanks for the quick response. Since the recommended solution required a
Crosstab Query I thought I would try that first to see if I would be getting
one of those "Too many Crosstab header" messages, and I did, and recalled the
numerous posts that have already been made about this type of message. Also,
please excuse my inexperience here, I'm sure I'll need some more explantion
on Karl's solution to make sure I'm applying it correctly --- not sure if I'm
substituting in my table and field names correctly into the statements he
provided. I'm still hoping that this one can be made to work.

I have been able to create a "denormalized" table (from one of Roger
Carlson's solutions, I think, that contains a sub routine that calls others),
and it works fine as far as transposing the rows of Sect_IDs for each Stu_ID
into columns so that there is only one row of data for each Stu_ID in the new
"denormalized" table. The problem I'm having here is being able to query the
table containing the student demographics with the "denormalized" table so as
to get all the data into one table. I've tried a LEFT JOIN and get data
pulled from only one of the two tables in the query, and then with a RIGHT
JOIN the data is pulled only from the other table in the query. In the query,
I'm using both, the table from which the "denormalized" table was created and
the "denormalized" table.

Still needing help.

Thanks again,

 

[KARL DEWEY]

Try these --
SELECT Q.Stut_ID, Q.Status, Q.Name, Q.Grade, Q.School, Q.Sect_ID, (SELECT
COUNT(*) FROM [tblCLASS_BUILDER] Q1 WHERE Q1.[Stut_ID] = Q.[Stut_ID]
AND Q1.[Sect_ID] <= Q.[Sect_ID]) AS Rank INTO tblCLASS_BUILDER_2
FROM tblCLASS_BUILDER AS Q
ORDER BY Q.Stut_ID, Q.Sect_ID;

TRANSFORM First(tblCLASS_BUILDER_2.Sect_ID) AS FirstOfSect_ID
SELECT tblCLASS_BUILDER_2.[Stut_ID], tblCLASS_BUILDER_2.Status,
tblCLASS_BUILDER_2.Name, tblCLASS_BUILDER_2.Grade, tblCLASS_BUILDER_2.School
FROM tblCLASS_BUILDER_2
GROUP BY tblCLASS_BUILDER_2.[Stut_ID], tblCLASS_BUILDER_2.Status,
tblCLASS_BUILDER_2.Name, tblCLASS_BUILDER_2.Grade, tblCLASS_BUILDER_2.School
PIVOT tblCLASS_BUILDER_2.Rank;

 

[RC]

Thanks Karl, it worked great!
And, thanks to vbasean too for providing the link to your solution
--
RC

Try these --
SELECT Q.Stut_ID, Q.Status, Q.Name, Q.Grade, Q.School, Q.Sect_ID, (SELECT
COUNT(*) FROM [tblCLASS_BUILDER] Q1 WHERE Q1.[Stut_ID] = Q.[Stut_ID]
AND Q1.[Sect_ID] <= Q.[Sect_ID]) AS Rank INTO tblCLASS_BUILDER_2
FROM tblCLASS_BUILDER AS Q
ORDER BY Q.Stut_ID, Q.Sect_ID;

TRANSFORM First(tblCLASS_BUILDER_2.Sect_ID) AS FirstOfSect_ID
SELECT tblCLASS_BUILDER_2.[Stut_ID], tblCLASS_BUILDER_2.Status,
tblCLASS_BUILDER_2.Name, tblCLASS_BUILDER_2.Grade, tblCLASS_BUILDER_2.School
FROM tblCLASS_BUILDER_2
GROUP BY tblCLASS_BUILDER_2.[Stut_ID], tblCLASS_BUILDER_2.Status,
tblCLASS_BUILDER_2.Name, tblCLASS_BUILDER_2.Grade, tblCLASS_BUILDER_2.School
PIVOT tblCLASS_BUILDER_2.Rank;

--
KARL DEWEY
Build a little - Test a little

Thanks for the quick response. Since the recommended solution required a
Crosstab Query I thought I would try that first to see if I would be getting
one of those "Too many Crosstab header" messages, and I did, and recalled the
numerous posts that have already been made about this type of message. Also,
please excuse my inexperience here, I'm sure I'll need some more explantion
on Karl's solution to make sure I'm applying it correctly --- not sure if I'm
substituting in my table and field names correctly into the statements he
provided. I'm still hoping that this one can be made to work.

I have been able to create a "denormalized" table (from one of Roger
Carlson's solutions, I think, that contains a sub routine that calls others),
and it works fine as far as transposing the rows of Sect_IDs for each Stu_ID
into columns so that there is only one row of data for each Stu_ID in the new
"denormalized" table. The problem I'm having here is being able to query the
table containing the student demographics with the "denormalized" table so as
to get all the data into one table. I've tried a LEFT JOIN and get data
pulled from only one of the two tables in the query, and then with a RIGHT
JOIN the data is pulled only from the other table in the query. In the query,
I'm using both, the table from which the "denormalized" table was created and
the "denormalized" table.

Still needing help.

Thanks again,