Use ARRAY for CASE Statements II
-
Hello,
Thanks again for offering to help with this problem. Before I get into details, let me summarize what is it I am trying to do. I am trying to create a query that will accomodate a cross - tab report, the results of which will be display through an Access 2003 ADP GUI report. In MS Access, such a report would probably have an Access Cross Tab query as its record source. The same type of report could be achieved in MS Excel with a pivot table. I know that SQL Server Analysis Services can accomodate such reports, but we don't Analysis Services in place yet.
I thought it might be helpful to show a picture of what the report actually looks like in the Design view and Print Preview: http://www.valverde.edu/home/policy/ReportLayout.htm . The design layout will show additional groupings of the data, and will show the layout of the fields that are used in the query. As you can see in the Design View of the report, The idea is to show the SPercentage of each StanNum for each Teacher. The StanNums (and StanDescriptions) are grouped by their StanID. The StanID's are grouped by their Strand. The Strands are grouped by their Domain.
In the query for the report, I have hard coded three TeacherID's in the CASE statements, and have hard coded Location2 + Grade = 37102 to match the location2 and Grade level of the TeacherID's. The inner query uses concatenation in the CASE statements, then the outer query parses the strings to create distinct fields that the report needs, while keeping the mapped correctly. I don't know if this was the best way to represent the relationship between a teacher's name and their SPercentages, but it works.
The query does what I want it to do. The problem is, I need to avoid hard coding the TeacherID's in the CASE statements of the inner query. The TeacherID's that are in tblMMrptStanResultsGrade arrive in the table from another table called Teacher_Data_Main. The query that I am using as the record source of the report is as follows:
***********************************************************************
CREATE View MM_rptTeacherGroupingTest_vw
AS
Select
Location2,
Grade,
Max(Right(T1,Reverse(CharIndex(' ',Reverse(T1),1)))) as T1Percent,
Max(Left(T1,Len(T1) - CharIndex(' ',Reverse(T1),1))) as T1Name,
Max(Right(T2,Reverse(CharIndex(' ',Reverse(T2),1)))) as T2Percent,
Max(Left(T2,Len(T2) - CharIndex(' ',Reverse(T2),1))) as T2Name,
Max(Right(T3,Reverse(CharIndex(' ',Reverse(T3),1)))) as T3Percent,
Max(Left(T3,Len(T3) - CharIndex(' ',Reverse(T3),1)))as T3Name,
StanID,
Domain,
Strand,
StanNum,
StanDesc
From
(
Select
Location2,
Grade,
Domain,
Strand,
StanNum,
StanDesc,
Case When TeacherID = 10371 Then TFirstName + ' ' + TLastname + ' ' + Cast(SPercentage as Varchar(4)) End as T1,
Case When TeacherID = 121371 Then TFirstName + ' ' + TLastname + ' ' + Cast(SPercentage as Varchar(4)) End as T2,
Case When TeacherID = 94371 Then TFirstName + ' ' + TLastname + ' ' + Cast(SPercentage as Varchar(4)) End as T3,
StanID
From tblMMrptStanResultsGrade
Where Location2 + Grade = 37102
)as Inline
Group By Location2, Grade, StanID, Domain, Strand, StanNum, StanDesc
****************************************************************
The following is a sample of the data that the above query returns.
Location2 Grade T1Percent T1Name T2Percent T2Name T3Percent T3Name StanID Domain Strand StanNum StanDesc 371 02 59 Nora Bruce 63 Carla Jenkins 80 Diann Gainer E020101001 Reading Word Analysis, Fluency, and Systematic Vocabulary Development 1.1 Recognize and use knowledge of spelling patterns (e.g., diphthongs, special vowel spelling) when reading. 371 02 81 Nora Bruce 63 Carla Jenkins 74 Diann Gainer E020101005 Reading Word Analysis, Fluency, and Systematic Vocabulary Development 1.5 Identify and correctly use regular plurals (e.g., -s, -es, -ies) and irregular plurals (e.g., fly/flies, wife/wives). 371 02 54 Nora Bruce 58 Carla Jenkins 86 Diann Gainer E020101007 Reading Word Analysis, Fluency, and Systematic Vocabulary Development 1.7 Understand and explain common antonyms and synonyms. 371 02 59 Nora Bruce 88 Carla Jenkins 90 Diann Gainer E020101008 Reading Word Analysis, Fluency, and Systematic Vocabulary Development 1.8 Use knowledge of individual words in unknown compound words to predict their meaning. 371 02 65 Nora Bruce 64 Carla Jenkins 78 Diann Gainer E020101009 Reading Word Analysis, Fluency, and Systematic Vocabulary Development 1.9 Know the meaning of the simple prefixes and suffixes (e.g., over-, un-, -ing, -ly). 371 02 59 Nora Bruce 63 Carla Jenkins 67 Diann Gainer E020101010 Reading Word Analysis, Fluency, and Systematic Vocabulary Development 1.10 Identify simple multiple-meaning words. 371 02 34 Nora Bruce 17 Carla Jenkins 62 Diann Gainer E020102002 Reading Reading Comprehension 2.2 State the purpose in reading (i.e., tell what information is sought). 371 02 44 Nora Bruce 42 Carla Jenkins 68 Diann Gainer E020102005 Reading Reading Comprehension 2.5 Restate facts and details in the text to clarify and organize ideas. 371 02 38 Nora Bruce 75 Carla Jenkins 73 Diann Gainer E020102006 Reading Reading Comprehension 2.6 Recognize cause-and-effect relationships in a text. 371 02 69 Nora Bruce 75 Carla Jenkins 79 Diann Gainer E020102007 Reading Reading Comprehension 2.7 Interpret information from diagrams, charts, and graphs. 371 02 34 Nora Bruce 50 Carla Jenkins 83 Diann Gainer E020103001 Reading Literary Response and Analysis 3.1 Compare and contrast plots, settings, and characters presented by different authors. 371 02 40 Nora Bruce 58 Carla Jenkins 83 Diann Gainer E020201001 Writing Writing Strategies 1.1 Group related ideas and maintain a consistent focus. 371 02 23 Nora Bruce 17 Carla Jenkins 59 Diann Gainer E020201003 Writing Writing Strategies 1.3 Understand the purposes of various references materials (e.g., dictionary, thesaurus, atlas). 371 02 31 Nora Bruce 63 Carla Jenkins 51 Diann Gainer E020201004 Writing Writing Strategies 1.4 Revise original drafts to improve sequence and provide more descriptive detail. 371 02 57 Nora Bruce 36 Carla Jenkins 68 Diann Gainer E020301001 Written and Oral English Language Conventions Written and Oral English Language Conventions 1.1 Distinguish between complete and incomplete sentences. 371 02 NULL NULL NULL NULL 90 Diann Gainer E020301002 Written and Oral English Language Conventions Written and Oral English Language Conventions 1.2 Recognize and use the correct word order in written sentences. 371 02 49 Nora Bruce 58 Carla Jenkins 53 Diann Gainer E020301003 Written and Oral English Language Conventions Written and Oral English Language Conventions 1.3 Identify and correctly use various parts of speech, including nouns and verbs, in writing and speaking. 371 02 45 Nora Bruce 38 Carla Jenkins 56 Diann Gainer E020301006 Written and Oral English Language Conventions Written and Oral English Language Conventions 1.6 Capitalize all proper nouns, words at the beginning of sentences and greetings, months and days of the week, and titles and initials of people. 371 02 81 Nora Bruce 83 Carla Jenkins 85 Diann Gainer E020301008 Written and Oral English Language Conventions Written and Oral English Language Conventions 1.8 Spell basic short-vowel, long-vowel, r-controlled, and consonant-blend patterns correctly. 371 02 NULL NULL NULL NULL 71 Diann Gainer E020401002 Listening and Speaking Listening and Speaking Strategies 1.2 Ask for clarification and explanation of stories and ideas. 371 02 52 Nora Bruce 38 Carla Jenkins 87 Diann Gainer E020401003 Listening and Speaking Listening and Speaking Strategies 1.3 Paraphrase information that has been shared orally by others. The following is the schema of the table (tblMMrptStanResultsGrade) that the query pulls the data from. This table exists just for the report, and has several indexes on it (other than the Primary Key shown in the schema below). This table is not in production now, but once it is, it will be populated once a week (or whenever needed) with new data from the results of a Proc. The other indexes are a UNIQUE index on TeacherID and StanID WITH IGNORE_DUP_KEY, and an INDEX on Location2 and Grade:
CREATE TABLE [tblMMrptStanResultsGrade] (
[RecID] [int] IDENTITY (1, 1) Primary Key NOT NULL ,
[LocationDesc] [nvarchar] (124) NOT NULL ,
[Location2] [nvarchar] (3) NOT NULL ,
[Grade] [nvarchar] (2) NOT NULL ,
[TLastName] [nvarchar] (20) NOT NULL ,
[TFirstName] [nvarchar] (20) NOT NULL ,
[TeacherID] [nvarchar] (6) NOT NULL ,
[Strand] [nvarchar] (100) NOT NULL ,
[StanNum] [nvarchar] (10) NULL ,
[StanDesc] [nvarchar] (255) NOT NULL ,
[StanID] nvarchar] (20) NOT NULL ,
[InsertDate] [smalldatetime] NOT NULL] DEFAULT (getdate()),
[SPercentage] [smallint] NOT NULL ,
[Domain] [nvarchar] (100) NOT NULL ,
A sample of the data from the above table (Where Location2 = 371, and Grade = 02, and TeacherID IN(10371, 121371, 94371)) looks like this:
RecID LocationDesc Location2 Grade TLastName TFirstName TeacherID Strand StanNum StanDesc StanID InsertDate SPercentage Domain 49 Victoriano Elementary School 371 02 Bruce Nora 10371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.1 Recognize and use knowledge of spelling patterns (e.g., diphthongs, special vowel spelling) when reading. E020101001 2004-03-22 10:12:00 59 Reading 50 Victoriano Elementary School 371 02 Bruce Nora 10371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.5 Identify and correctly use regular plurals (e.g., -s, -es, -ies) and irregular plurals (e.g., fly/flies, wife/wives). E020101005 2004-03-22 10:12:00 81 Reading 51 Victoriano Elementary School 371 02 Bruce Nora 10371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.7 Understand and explain common antonyms and synonyms. E020101007 2004-03-22 10:12:00 54 Reading 52 Victoriano Elementary School 371 02 Bruce Nora 10371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.8 Use knowledge of individual words in unknown compound words to predict their meaning. E020101008 2004-03-22 10:12:00 59 Reading 53 Victoriano Elementary School 371 02 Bruce Nora 10371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.9 Know the meaning of the simple prefixes and suffixes (e.g., over-, un-, -ing, -ly). E020101009 2004-03-22 10:12:00 65 Reading 54 Victoriano Elementary School 371 02 Bruce Nora 10371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.10 Identify simple multiple-meaning words. E020101010 2004-03-22 10:12:00 59 Reading 55 Victoriano Elementary School 371 02 Bruce Nora 10371 Reading Comprehension 2.2 State the purpose in reading (i.e., tell what information is sought). E020102002 2004-03-22 10:12:00 34 Reading 56 Victoriano Elementary School 371 02 Bruce Nora 10371 Reading Comprehension 2.5 Restate facts and details in the text to clarify and organize ideas. E020102005 2004-03-22 10:12:00 44 Reading 57 Victoriano Elementary School 371 02 Bruce Nora 10371 Reading Comprehension 2.6 Recognize cause-and-effect relationships in a text. E020102006 2004-03-22 10:12:00 38 Reading 58 Victoriano Elementary School 371 02 Bruce Nora 10371 Reading Comprehension 2.7 Interpret information from diagrams, charts, and graphs. E020102007 2004-03-22 10:12:00 69 Reading 59 Victoriano Elementary School 371 02 Bruce Nora 10371 Literary Response and Analysis 3.1 Compare and contrast plots, settings, and characters presented by different authors. E020103001 2004-03-22 10:12:00 34 Reading 60 Victoriano Elementary School 371 02 Bruce Nora 10371 Writing Strategies 1.1 Group related ideas and maintain a consistent focus. E020201001 2004-03-22 10:12:00 40 Writing 61 Victoriano Elementary School 371 02 Bruce Nora 10371 Writing Strategies 1.3 Understand the purposes of various references materials (e.g., dictionary, thesaurus, atlas). E020201003 2004-03-22 10:12:00 23 Writing 62 Victoriano Elementary School 371 02 Bruce Nora 10371 Writing Strategies 1.4 Revise original drafts to improve sequence and provide more descriptive detail. E020201004 2004-03-22 10:12:00 31 Writing 63 Victoriano Elementary School 371 02 Bruce Nora 10371 Written and Oral English Language Conventions 1.1 Distinguish between complete and incomplete sentences. E020301001 2004-03-22 10:12:00 57 Written and Oral English Language Conventions 64 Victoriano Elementary School 371 02 Bruce Nora 10371 Written and Oral English Language Conventions 1.3 Identify and correctly use various parts of speech, including nouns and verbs, in writing and speaking. E020301003 2004-03-22 10:12:00 49 Written and Oral English Language Conventions 65 Victoriano Elementary School 371 02 Bruce Nora 10371 Written and Oral English Language Conventions 1.6 Capitalize all proper nouns, words at the beginning of sentences and greetings, months and days of the week, and titles and initials of people. E020301006 2004-03-22 10:12:00 45 Written and Oral English Language Conventions 66 Victoriano Elementary School 371 02 Bruce Nora 10371 Written and Oral English Language Conventions 1.8 Spell basic short-vowel, long-vowel, r-controlled, and consonant-blend patterns correctly. E020301008 2004-03-22 10:12:00 81 Written and Oral English Language Conventions 67 Victoriano Elementary School 371 02 Bruce Nora 10371 Listening and Speaking Strategies 1.3 Paraphrase information that has been shared orally by others. E020401003 2004-03-22 10:12:00 52 Listening and Speaking 68 Victoriano Elementary School 371 02 Bruce Nora 10371 1.0 NULL Students understand the relationship between numbers, quantities, and place value in whole numbers up to 1,000 M020101000 2004-03-22 10:12:00 85 Number Sense 69 Victoriano Elementary School 371 02 Bruce Nora 10371 1.0 1.1 Count, read, write whole numbers to 1,000; identify the place value for each digit. M020101001 2004-03-22 10:12:00 78 Number Sense 70 Victoriano Elementary School 371 02 Bruce Nora 10371 1.0 1.2 Use words, models, expanded form to represent numbers to 1,000 M020101002 2004-03-22 10:12:00 63 Number Sense 71 Victoriano Elementary School 371 02 Bruce Nora 10371 2.0 2.1 Understand and use inverse relationship between addition and subtraction to solve problems and check solutions M020102001 2004-03-22 10:12:00 31 Number Sense 72 Victoriano Elementary School 371 02 Bruce Nora 10371 2.0 2.2 Find the sum or difference of two whole numbers to 3-digits M020102002 2004-03-22 10:12:00 55 Number Sense 73 Victoriano Elementary School 371 02 Bruce Nora 10371 2.0 2.3 Use mental arithmetic to find sum or difference of 2-digit numbers M020102003 2004-03-22 10:12:00 67 Number Sense 74 Victoriano Elementary School 371 02 Bruce Nora 10371 5.0 5.1 Solve problems using combination of coins and bills M020105001 2004-03-22 10:12:00 65 Number Sense 75 Victoriano Elementary School 371 02 Bruce Nora 10371 1.0 NULL Students model, represent, and interpret number relationships to create and solve problems involving addition and subtraction M020201000 2004-03-22 10:12:00 56 Algebra and Functions 76 Victoriano Elementary School 371 02 Bruce Nora 10371 1.0 1.2 Relate problem situations to number sentences involving addition and subtraction. M020201002 2004-03-22 10:12:00 89 Algebra and Functions 77 Victoriano Elementary School 371 02 Bruce Nora 10371 1.0 1.4 Ask and answer simple questions related to data representations. M020401004 2004-03-22 10:12:00 35 Statistics, Data Analysis and Probability 78 Victoriano Elementary School 371 02 Bruce Nora 10371 2.0 2.1 Recognize, describe, and extend patterns and determine a next term in linear patterns M020402001 2004-03-22 10:12:00 54 Statistics, Data Analysis and Probability 261 Victoriano Elementary School 371 01 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.4 Distinguish initial, media, and final sounds in single-syllable words. E010101004 2004-03-22 10:12:00 75 Reading 262 Victoriano Elementary School 371 01 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.9 Segment single syllable words into their components (e.g., /c/a/t/ = cat; /s/p/l/a/t/ = splat; /r/I/c/h/ = rich). E010101009 2004-03-22 10:12:00 75 Reading 263 Victoriano Elementary School 371 01 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.10 Generate the sounds form all the letters and letter patterns, including consonant blends and long- and short-vowel patterns (I.e., phonograms), and blend those sounds into recognizable words. E010101010 2004-03-22 10:12:00 100 Reading 264 Victoriano Elementary School 371 01 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.11 Read common, irregular sight words (e.g., the, have, said, come, give, of). E010101011 2004-03-22 10:12:00 75 Reading 265 Victoriano Elementary School 371 01 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.14 Read inflectional; forms (e.g., -s, -ed, -ing) and root words (e.g., look, looked, looking). E010101014 2004-03-22 10:12:00 75 Reading 266 Victoriano Elementary School 371 01 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.15 Read common word families (e.g., -ite, -ate). E010101015 2004-03-22 10:12:00 75 Reading 267 Victoriano Elementary School 371 01 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.17 Classify grade-appropriate categories of words (e.g., concrete collections of animals, foods, toys). E010101017 2004-03-22 10:12:00 75 Reading 268 Victoriano Elementary School 371 01 Jenkins Carla 121371 Reading Comprehension 2.2 Respond to who, what, where, and how questions. E010102002 2004-03-22 10:12:00 50 Reading 269 Victoriano Elementary School 371 01 Jenkins Carla 121371 Reading Comprehension 2.4 Use context to resolve ambiguities about word and sentence meanings. E010102004 2004-03-22 10:12:00 100 Reading 270 Victoriano Elementary School 371 01 Jenkins Carla 121371 Reading Comprehension 2.5 Confirm predictions about what will happen next in a text by identifying key words (I.e., signpost words). E010102005 2004-03-22 10:12:00 50 Reading 271 Victoriano Elementary School 371 01 Jenkins Carla 121371 Reading Comprehension 2.6 Relate prior knowledge to textual information. E010102006 2004-03-22 10:12:00 100 Reading 272 Victoriano Elementary School 371 01 Jenkins Carla 121371 Reading Comprehension 2.7 Retell the central ideas of simple expository or narrative passages. E010102007 2004-03-22 10:12:00 75 Reading 273 Victoriano Elementary School 371 01 Jenkins Carla 121371 Written and Oral English Language Conventions 1.1 Write and speak in complete, coherent sentences. E010301001 2004-03-22 10:12:00 88 Written and Oral English Language Conventions 274 Victoriano Elementary School 371 01 Jenkins Carla 121371 Written and Oral English Language Conventions 1.3 Identify and correctly use contractions (e.g., isn't, aren't, can't, won't) and singular possessive pronoun (e.g., my/ mine, his/ her, hers, your/ s) in writing and speaking. E010301003 2004-03-22 10:12:00 38 Written and Oral English Language Conventions 275 Victoriano Elementary School 371 01 Jenkins Carla 121371 Written and Oral English Language Conventions 1.4 Distinguish between declarative, exclamatory, and interrogative sentences. E010301004 2004-03-22 10:12:00 50 Written and Oral English Language Conventions 276 Victoriano Elementary School 371 01 Jenkins Carla 121371 Written and Oral English Language Conventions 1.5 Use a period, exclamation point, or question mark at the end of sentences. E010301005 2004-03-22 10:12:00 50 Written and Oral English Language Conventions 277 Victoriano Elementary School 371 01 Jenkins Carla 121371 Written and Oral English Language Conventions 1.6 Use knowledge of the basic rules of punctuation and capitalization when writing. E010301006 2004-03-22 10:12:00 50 Written and Oral English Language Conventions 278 Victoriano Elementary School 371 01 Jenkins Carla 121371 Written and Oral English Language Conventions 1.7 Capitalize the first word of a sentence, names of people, and the pronoun I. E010301007 2004-03-22 10:12:00 50 Written and Oral English Language Conventions 279 Victoriano Elementary School 371 01 Jenkins Carla 121371 Written and Oral English Language Conventions 1.8 Spell three- and four-letter short-vowel words and grade-level-appropriate sight words correctly. E010301008 2004-03-22 10:12:00 94 Written and Oral English Language Conventions 280 Victoriano Elementary School 371 01 Jenkins Carla 121371 Listening and Speaking Strategies 1.1 Listen attentively. E010401001 2004-03-22 10:12:00 25 Listening and Speaking 281 Victoriano Elementary School 371 02 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.1 Recognize and use knowledge of spelling patterns (e.g., diphthongs, special vowel spelling) when reading. E020101001 2004-03-22 10:12:00 63 Reading 282 Victoriano Elementary School 371 02 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.5 Identify and correctly use regular plurals (e.g., -s, -es, -ies) and irregular plurals (e.g., fly/flies, wife/wives). E020101005 2004-03-22 10:12:00 63 Reading 283 Victoriano Elementary School 371 02 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.7 Understand and explain common antonyms and synonyms. E020101007 2004-03-22 10:12:00 58 Reading 284 Victoriano Elementary School 371 02 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.8 Use knowledge of individual words in unknown compound words to predict their meaning. E020101008 2004-03-22 10:12:00 88 Reading 285 Victoriano Elementary School 371 02 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.9 Know the meaning of the simple prefixes and suffixes (e.g., over-, un-, -ing, -ly). E020101009 2004-03-22 10:12:00 64 Reading 286 Victoriano Elementary School 371 02 Jenkins Carla 121371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.10 Identify simple multiple-meaning words. E020101010 2004-03-22 10:12:00 63 Reading 287 Victoriano Elementary School 371 02 Jenkins Carla 121371 Reading Comprehension 2.2 State the purpose in reading (i.e., tell what information is sought). E020102002 2004-03-22 10:12:00 17 Reading 288 Victoriano Elementary School 371 02 Jenkins Carla 121371 Reading Comprehension 2.5 Restate facts and details in the text to clarify and organize ideas. E020102005 2004-03-22 10:12:00 42 Reading 289 Victoriano Elementary School 371 02 Jenkins Carla 121371 Reading Comprehension 2.6 Recognize cause-and-effect relationships in a text. E020102006 2004-03-22 10:12:00 75 Reading 290 Victoriano Elementary School 371 02 Jenkins Carla 121371 Reading Comprehension 2.7 Interpret information from diagrams, charts, and graphs. E020102007 2004-03-22 10:12:00 75 Reading 291 Victoriano Elementary School 371 02 Jenkins Carla 121371 Literary Response and Analysis 3.1 Compare and contrast plots, settings, and characters presented by different authors. E020103001 2004-03-22 10:12:00 50 Reading 292 Victoriano Elementary School 371 02 Jenkins Carla 121371 Writing Strategies 1.1 Group related ideas and maintain a consistent focus. E020201001 2004-03-22 10:12:00 58 Writing 293 Victoriano Elementary School 371 02 Jenkins Carla 121371 Writing Strategies 1.3 Understand the purposes of various references materials (e.g., dictionary, thesaurus, atlas). E020201003 2004-03-22 10:12:00 17 Writing 294 Victoriano Elementary School 371 02 Jenkins Carla 121371 Writing Strategies 1.4 Revise original drafts to improve sequence and provide more descriptive detail. E020201004 2004-03-22 10:12:00 63 Writing 295 Victoriano Elementary School 371 02 Jenkins Carla 121371 Written and Oral English Language Conventions 1.1 Distinguish between complete and incomplete sentences. E020301001 2004-03-22 10:12:00 36 Written and Oral English Language Conventions 296 Victoriano Elementary School 371 02 Jenkins Carla 121371 Written and Oral English Language Conventions 1.3 Identify and correctly use various parts of speech, including nouns and verbs, in writing and speaking. E020301003 2004-03-22 10:12:00 58 Written and Oral English Language Conventions 297 Victoriano Elementary School 371 02 Jenkins Carla 121371 Written and Oral English Language Conventions 1.6 Capitalize all proper nouns, words at the beginning of sentences and greetings, months and days of the week, and titles and initials of people. E020301006 2004-03-22 10:12:00 38 Written and Oral English Language Conventions 298 Victoriano Elementary School 371 02 Jenkins Carla 121371 Written and Oral English Language Conventions 1.8 Spell basic short-vowel, long-vowel, r-controlled, and consonant-blend patterns correctly. E020301008 2004-03-22 10:12:00 83 Written and Oral English Language Conventions 299 Victoriano Elementary School 371 02 Jenkins Carla 121371 Listening and Speaking Strategies 1.3 Paraphrase information that has been shared orally by others. E020401003 2004-03-22 10:12:00 38 Listening and Speaking 300 Victoriano Elementary School 371 01 Jenkins Carla 121371 1.0 1.0 Students understand and use numbers up to 100 M010101000 2004-03-22 10:12:00 100 Number Sense 301 Victoriano Elementary School 371 01 Jenkins Carla 121371 1.0 1.1 Count, read, and write whole numbers to 100. M010101001 2004-03-22 10:12:00 85 Number Sense 302 Victoriano Elementary School 371 01 Jenkins Carla 121371 1.0 1.2 Compare and order whole numbers to 100 by using the symbols for less than, equal to, or greater than (<, =, > 
. M010101002 2004-03-22 10:12:00 67 Number Sense 303 Victoriano Elementary School 371 01 Jenkins Carla 121371 1.0 1.3 Represent equivalent forms of the same number through the use of physical models, diagrams, and number expressions (to 20) (e.g., 8 may be represented as 4 + 4, 5 + 3, 2 + 2 + 2, 10 - 2, 11 - 3). M010101003 2004-03-22 10:12:00 100 Number Sense 304 Victoriano Elementary School 371 01 Jenkins Carla 121371 1.0 1.4 Count and group object in ones and tens (e.g., three groups of 10 and 4 equals 34, or 30 + 4). M010101004 2004-03-22 10:12:00 100 Number Sense 305 Victoriano Elementary School 371 01 Jenkins Carla 121371 2.0 2.1 Know the addition facts (sums to 20) and the corresponding subtraction facts and commit them to memory. M010102001 2004-03-22 10:12:00 70 Number Sense 306 Victoriano Elementary School 371 01 Jenkins Carla 121371 2.0 2.3 Identify one more than, one less than, 10 more than, and 10 less than a given number. M010102003 2004-03-22 10:12:00 100 Number Sense 307 Victoriano Elementary School 371 01 Jenkins Carla 121371 2.0 2.5 Show the meaning of addition (putting together, increasing) and subtraction (taking away, comparing, finding the difference). M010102005 2004-03-22 10:12:00 67 Number Sense 308 Victoriano Elementary School 371 01 Jenkins Carla 121371 2.0 2.6 Solve addition and subtraction problems with one-and two-digit numbers (e.g., 5+58=_). M010102006 2004-03-22 10:12:00 83 Number Sense 309 Victoriano Elementary School 371 01 Jenkins Carla 121371 4.0 4.2 Recognize fractions of a whole and parts of a group (e.g., one-fourth of a pie, two-thirds of 15 balls). M010104002 2004-03-22 10:12:00 100 Number Sense 310 Victoriano Elementary School 371 01 Jenkins Carla 121371 1.0 1.1 Write and solve number sentences from problem situations that express relationships involving addition and subtraction. M010201001 2004-03-22 10:12:00 91 Algebra and Functions 311 Victoriano Elementary School 371 01 Jenkins Carla 121371 1.0 1.2 Understand the meaning of the symbols +, -, =. M010201002 2004-03-22 10:12:00 75 Algebra and Functions 312 Victoriano Elementary School 371 01 Jenkins Carla 121371 2.0 2.2 Classify familiar plane and solid objects by common attributes, such as color, position, shape, size, roundness, or number of corners, and explain which attributes are being used for classification. M010302002 2004-03-22 10:12:00 0 Measurement and Geometry 313 Victoriano Elementary School 371 01 Jenkins Carla 121371 2.0 2.4 Arrange and describe objects in space by proximity, position, and direction. M010302004 2004-03-22 10:12:00 100 Measurement and Geometry 314 Victoriano Elementary School 371 01 Jenkins Carla 121371 1.0 1.2 Represent and compare data by using pictures, bar graphs, tally charts, and picture graphs. M010401002 2004-03-22 10:12:00 0 Statistics, Data Analysis and Probability 315 Victoriano Elementary School 371 02 Jenkins Carla 121371 1.0 NULL Students understand the relationship between numbers, quantities, and place value in whole numbers up to 1,000 M020101000 2004-03-22 10:12:00 76 Number Sense 316 Victoriano Elementary School 371 02 Jenkins Carla 121371 1.0 1.1 Count, read, write whole numbers to 1,000; identify the place value for each digit. M020101001 2004-03-22 10:12:00 92 Number Sense 317 Victoriano Elementary School 371 02 Jenkins Carla 121371 1.0 1.2 Use words, models, expanded form to represent numbers to 1,000 M020101002 2004-03-22 10:12:00 72 Number Sense 318 Victoriano Elementary School 371 02 Jenkins Carla 121371 1.0 1.3 Order and compare whole numbers to 1,000 by using symbols <, =, > M020101003 2004-03-22 10:12:00 60 Number Sense 319 Victoriano Elementary School 371 02 Jenkins Carla 121371 2.0 2.1 Understand and use inverse relationship between addition and subtraction to solve problems and check solutions M020102001 2004-03-22 10:12:00 0 Number Sense 320 Victoriano Elementary School 371 02 Jenkins Carla 121371 2.0 2.2 Find the sum or difference of two whole numbers to 3-digits M020102002 2004-03-22 10:12:00 72 Number Sense 321 Victoriano Elementary School 371 02 Jenkins Carla 121371 2.0 2.3 Use mental arithmetic to find sum or difference of 2-digit numbers M020102003 2004-03-22 10:12:00 50 Number Sense 322 Victoriano Elementary School 371 02 Jenkins Carla 121371 5.0 5.1 Solve problems using combination of coins and bills M020105001 2004-03-22 10:12:00 50 Number Sense 323 Victoriano Elementary School 371 02 Jenkins Carla 121371 5.0 5.2 Know and use decimal notation and dollar and cents symbols for money M020105002 2004-03-22 10:12:00 20 Number Sense 324 Victoriano Elementary School 371 02 Jenkins Carla 121371 1.0 NULL Students model, represent, and interpret number relationships to create and solve problems involving addition and subtraction M020201000 2004-03-22 10:12:00 24 Algebra and Functions 325 Victoriano Elementary School 371 02 Jenkins Carla 121371 1.0 1.2 Relate problem situations to number sentences involving addition and subtraction. M020201002 2004-03-22 10:12:00 38 Algebra and Functions 326 Victoriano Elementary School 371 02 Jenkins Carla 121371 1.0 1.4 Tell time to nearest quarter hour and know relationships of time (minutes in hour, days in month, weeks in year) M020301004 2004-03-22 10:12:00 30 Measurement and Geometry 327 Victoriano Elementary School 371 02 Jenkins Carla 121371 1.0 1.5 Determine duration of intervals of time in hours. M020301005 2004-03-22 10:12:00 20 Measurement and Geometry 328 Victoriano Elementary School 371 02 Jenkins Carla 121371 1.0 1.4 Ask and answer simple questions related to data representations. M020401004 2004-03-22 10:12:00 19 Statistics, Data Analysis and Probability 329 Victoriano Elementary School 371 02 Jenkins Carla 121371 2.0 2.1 Recognize, describe, and extend patterns and determine a next term in linear patterns M020402001 2004-03-22 10:12:00 23 Statistics, Data Analysis and Probability 467 Victoriano Elementary School 371 02 Gainer Diann 94371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.1 Recognize and use knowledge of spelling patterns (e.g., diphthongs, special vowel spelling) when reading. E020101001 2004-03-22 10:12:00 80 Reading 468 Victoriano Elementary School 371 02 Gainer Diann 94371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.5 Identify and correctly use regular plurals (e.g., -s, -es, -ies) and irregular plurals (e.g., fly/flies, wife/wives). E020101005 2004-03-22 10:12:00 74 Reading 469 Victoriano Elementary School 371 02 Gainer Diann 94371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.7 Understand and explain common antonyms and synonyms. E020101007 2004-03-22 10:12:00 86 Reading 470 Victoriano Elementary School 371 02 Gainer Diann 94371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.8 Use knowledge of individual words in unknown compound words to predict their meaning. E020101008 2004-03-22 10:12:00 90 Reading 471 Victoriano Elementary School 371 02 Gainer Diann 94371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.9 Know the meaning of the simple prefixes and suffixes (e.g., over-, un-, -ing, -ly). E020101009 2004-03-22 10:12:00 78 Reading 472 Victoriano Elementary School 371 02 Gainer Diann 94371 Word Analysis, Fluency, and Systematic Vocabulary Development 1.10 Identify simple multiple-meaning words. E020101010 2004-03-22 10:12:00 67 Reading 473 Victoriano Elementary School 371 02 Gainer Diann 94371 Reading Comprehension 2.2 State the purpose in reading (i.e., tell what information is sought). E020102002 2004-03-22 10:12:00 62 Reading 474 Victoriano Elementary School 371 02 Gainer Diann 94371 Reading Comprehension 2.5 Restate facts and details in the text to clarify and organize ideas. E020102005 2004-03-22 10:12:00 68 Reading 475 Victoriano Elementary School 371 02 Gainer Diann 94371 Reading Comprehension 2.6 Recognize cause-and-effect relationships in a text. E020102006 2004-03-22 10:12:00 73 Reading 476 Victoriano Elementary School 371 02 Gainer Diann 94371 Reading Comprehension 2.7 Interpret information from diagrams, charts, and graphs. E020102007 2004-03-22 10:12:00 79 Reading 477 Victoriano Elementary School 371 02 Gainer Diann 94371 Literary Response and Analysis 3.1 Compare and contrast plots, settings, and characters presented by different authors. E020103001 2004-03-22 10:12:00 83 Reading 478 Victoriano Elementary School 371 02 Gainer Diann 94371 Writing Strategies 1.1 Group related ideas and maintain a consistent focus. E020201001 2004-03-22 10:12:00 83 Writing I hope that I have presented the right information for the problem I am trying to resolve. The bottom line is, I need to achieve the results I need for the report, and set the CASE statements up in MM_rptTeacherGroupingTest_vw so that I don't have to hard code the TeacherID's. Maybe I can achieve the results I want for the report by doing something else. If so, I would be interested in knowing what I can do.
Thank you again for your help!
CSDunn
-
The main way I've accomplished this is through stored procedures that build dynamic SQL statements. Build a temp table or table var with an IDENTITY column and a unique list of Teacher IDs needed for current query, move through the resulting table row by row and add to your dynamic sql each step as you go.
It seems I've seen it done using self joins but I think that only works with a fixed number of pivot columns.
Post back or email me if you'd like samples.
-
Greg,
Yes, please provide samples.
Thank you for your help!
CSDunn
-
Very thorough data...the first one one this site that I would say is almost too thorough. Very very nice...
Abstracting it out, the key is:
Create a temp table (#Event in the example below) and populate it with all the rows you want in the finished pivot table. Be sure at least one column is a key value that can be used to update the temp table (BillEventID in the example)
Next, Create another temp table and put a row in it for each COLUMN you want in the finished pivot table (#Month in the example). Make sure the criteria for each column is represented in the table (minBillDateID and maxBillDateID in example)
Next, use a loop and dynamic SQL to alter the #Event table and add a column for each row in the #Month column. In the same loop, update the created #Event column based on the criteria for that column (Select..where BillEventID =...and BillDateID between minBillDateID and maxBillDateID). When the loop is complete select #Event and you've got your pivot table:
------------------------------------------------------------------------------
if object_ID('tempdb..##Event') is not null drop table ##Event
create Table ##Event (BillEventID int NOt Null Primary Key, BillTypeID int, Name varchar(255), URL varchar(2000), isBillable bit)
if object_ID('tempdb..#Month') is not null drop table #Month
create Table #Month (MonthID int identity Not Null Primary Key, eYear int, eMonth int, ColName sysname, minBILLDateID int, maxBillDateID int)
Insert #Month (eYear, eMonth, ColName, minBillDateID, maxBillDateID)
select...
Insert ##Event (BillEventID, Name, URL, BillTypeID, isBillable)
Select....
declare @ColName sysname,
@MonthID int,
@vcMin varchar(25),
@vcmax varchar(25),
@Select varchar(8000),
@Group varchar(8000)
select @ColName = '',
@MonthID = 0,
@Select = ''
While 1 = 1
Begin
select top 1
@ColName = ColName,
@MonthID = MonthID,
@vcMin = minBILLDateID,
@vcmax = maxBILLDateID,
@Select = @Select + ', sum([' + ColName + ']) as [' + ColName + ']'
--@Select = @Select + ', [' + ColName + ']'
From #Month
Where MonthID > @MonthID
order by MonthID
if @@RowCount = 0 Break
exec( 'Alter Table ##Event add [' + @ColName + '] int')
exec( 'Update e
Set [' + @ColName + '] = isnull(Total, 0)
From ##Event e
JOIN
(
Select BillEventID, sum(Total) Total
From Operator.dbo.BillDetail bd (nolock)
Where bd.BillDateID between ''' + @vcMin + ''' and ''' + @vcMax + '''
group by BillEventID
 
e1 on e.BillEventID = e1.BillEventID')exec( 'Update ##Event
Set [' + @ColName + '] = 0 Where [' + @ColName + '] is null')
END
exec('Select ' + @Select + '
From ##Event e (nolock)
Group by BillEventID
')
Signature is NULL
-
Here's a simpler example:
if object_ID('tempdb..#Test') is not null drop table #Test
Create Table #Test
([id] [char] (22) ,
[ques_name] [varchar] (50) ,
[ques_val] [varchar] (100) )
Insert #Test Values ('1','date','10/14/2004')
Insert #Test Values ('1','country','usa')
Insert #Test Values ('1','zip','20191')
Insert #Test Values ('1','email','test@yahoo.com')
Insert #Test Values ('1','name','test')
Insert #Test Values ('2','date','10/14/2005')
Insert #Test Values ('2','country','usa2')
Insert #Test Values ('2','zip','20192')
Insert #Test Values ('2','email','test@yahoo.com2')
Insert #Test Values ('2','name','test2')
-------------------------------------------------------------
if object_ID('tempdb..#Column') is not null drop table #Column
create Table #Column (ColName sysname)
if object_ID('tempdb..#Row') is not null drop table #Row
create Table #Row ([ID] char(22))
declare @ColName sysname
Insert #Column(ColName)
Select distinct ques_name
From #Test
Insert #Row ([ID])
Select distinct [id]
from #Test
While 1 = 1
BEGIN
Select top 1
@ColName = ColName
From #Column
Where ColName > isnull(@ColName, '')
order by ColName
if @@RowCount = 0 Break
exec('Alter Table #Row add ' + @ColName + ' varchar(100)')
exec('Update r
set ' + @ColName + ' = ques_val
From #Row r
JOIN #Test t on r.[ID] = t.[ID]
where t.ques_name = ''' + @ColName + '''')
END
select * from #Row
Signature is NULL
-
here is another example: I follow it with some tips.
--Make a temp table for the example
CREATE TABLE #Rate ( From_City varchar(50) , To_City varchar(50) , Cost int )
--Insert some data INSERT INTO #rate Values('Minneapolis', 'Los Angeles', 189) INSERT INTO #rate Values('Minneapolis', 'San Francisco', 239) INSERT INTO #rate Values('Saint Paul', 'Fresno', 289) INSERT INTO #rate Values('Fresno', 'Los Angeles', 89)--Make a temp table to act as a cursor (without actually using one) Create Table #tmpCity (ID INT IDENTITY, City Varchar(255)) INSERT #tmpCity (city)
--Fill 'cursor' table with data... SELECT DISTINCT to_city city FROM #RATE --Then create the dynamic part of your sql: SET NOCOUNT ON DECLARE @ctr INT , @max INT , @val Varchar(255) , @sql Varchar(8000)
--Initialize the variables
SET @ctr = 0 SET @sql = '' SET @max = (SELECT MAX(ID) FROM #tmpCity)
--Now build the loop
WHILE @ctr < @max BEGIN SET @ctr = @ctr + 1 SET @val = (SELECT city FROM #tmpCity WHERE ID = @ctr) SET @sql = @sql + 'SUM(CASE to_city WHEN ''' + REPLACE(@val , '''', '''''') + ''' THEN cost ELSE 0 END) AS [' + @val + '] ' IF @ctr < @max SET @sql = @sql + ', ' SET @sql = @sql + CHAR(10) END
--Then add in the 'rest' of the SQL statement...
SET @sql = 'SELECT from_city,' + CHAR(10) + @sql + 'FROM #RATE ' + CHAR(10) + 'GROUP BY from_city'
-- run the dynamic SQL statement... SELECT @sql EXEC(@sql)
--Finally DROP TABLE #tmpCity DROP TABLE #Rate
This code runs -as is- in ISQL. It creates a temp table, builds some dynamic sql, displays the sql, then executes it.
1) In real practice I've found that the SQL statements can easily fill the @sql variable. EXEC allows you to concatenate multiple variables at run time and thereby exceed the 8000 (4000 if unicode) character limit. The simplest way I've done it is to write the 'SELECT' statement into @SqlSelect, the 'Case' statements into @SqlCase, and the rest into @SqlFrom. Then your exec line looks like:
EXEC (@sqlSelect + @sqlCase + @sqlFrom)
2) Don't forget to initialize the @sql variables. '' + 'xxx' = 'xxx' but NULL + 'xxx' = NULL!
3) Since your Teacher IDs can be considered Numerics by SQLServer you may have to CAST or CONVERT them within your dynamic SQL if you see 'ERROR CONVERTING ...' messages.
4) Side note: You can also define a whole bunch of sql variables and as each one fills up assign the sql to a new var, then concatenate at the end. It's a lot uglier code and you have to still guess how many variables it would take. I've required up to 30,000 characters for a particularly ugly crosstab. For that on I defined 8 variables @sql1 through @sql8 in addition to @sql. as @sql fills up you assign the sql to each progressive var...ugly but lets you write very large statements.
Hope this helps. sorry for my delayed response!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply