You should see it vs. the double UNPIVOT. IMHO much cleaner. Runs faster too.

anthony.green (6/15/2012)

---

Thanks guys, much appreciated. Was racking the brain yesterday on that and for the life of me couldnt get unpivot working.

I like the idea of the values list,...

Jeez Kingston, you've let the cat out of the bag!!

Actually you don't need to do a JOIN of any sort.

create table #studentSchoolHistory (studentID varchar(12),scode int, iSchoolYearcode int)

insert into #studentSchoolHistory values...

SELECT SolutionFor

FROM MyHomework

WHERE Solutionby = 'Lynn' OR Solutionby = 'Sony'

🙂

Lynn Pettis (6/15/2012)

---

Oh, actually a left...

I was going to say the same thing but I think Sony was assuming there might be duplicated rows...

Looks familiar! 😉

I think your friend's suggestion is appropriate.

If you're worried about having 500000 rows in the role/screens table, try only storing the relation if the role is authorized to use the...

This will also work.

SELECT PARSENAME(REPLACE('/Folder/File/Name', '/', '.'), 1)

I would start by putting a WITH(ROWLOCK) hint on the UPDATE statement.

INNER JOIN student with studentSchoolHistory tables to get the first 3 columns. Use a correlated subquery to get the fourth (Mobility Indicator) column.

It appears that there is more than one person stumped on the same homework problem:

http://www.sqlservercentral.com/Forums/Topic1316272-391-1.aspx?Update=1

It appears that there is more than one person stumped on the same homework problem.

http://www.sqlservercentral.com/Forums/Topic1316131-391-1.aspx

Gosh Chris! Did you mean like this?

SELECT APUserID, TypeID, Something

FROM @SampleData

CROSS APPLY (

VALUES ('FirstName', FirstName)

...

Lynn Pettis (6/14/2012)

---

dwain.c (6/14/2012)

---

Sean Lange (6/14/2012)

---

One requirement of a CTE is the previous line must end with a semicolon. As such many people have developed the habit of beginning a...