I really don't think you have tried the solution you are proposing as the columns in the table definition do not match those in the query. Can you explain what...

Does the following do what you want?

SELECT SUM(CASE WHEN School_State_Start = 'Ready' THEN 1 ELSE 0 END) AS Ready

,SUM(CASE WHEN School_State_Start = 'Soon' THEN...

Unfortunately, I have to work with a 3rd party application that has only recently been certified by the supplier to work with SQL Server 2008. The base application probably contains...

The HIERARCHYID datatype was only introduced to T-SQL in 2008. If you have to use an earlier version of SQL Server (2005 or earlier), hierarchyid is not supported.

Dave

I have no idea what you are trying to accomplish with this, but you should be abe to use the statement

CASE [ApprovalID]

when '35CD3B40-DD5B-4231-9FF4-C4549BBAD6CF' then (select FirstName from user where userID...

As Capn Hector said a self join will work. I think he has the join condition slightly wrong and mixed up the empid and supid

WITH emp AS

(

SELECT 1000 AS empid,...

I believe this is Itzik's article http://www.sqlmag.com/content1/topic/hierarchyid/catpath/tsql3 to which WRACK refers in the previous post.

Using Jeff's article http://www.sqlservercentral.com/articles/T-SQL/72503/ a version using the supplied table with hierarchyid is

WITH

cteHierarchy AS

(

SELECT...

I should have issued a disclaimer much the same as Jeff. I hadn't used HIERARCHYID before yesterday - we only use 2005 where I work.

However, based on Jeff's statement...

I think your 'straightforward' pivot query should be something like

select application, [deferred], [fixed], [by design]

from WorkItems

PIVOT

(

COUNT(workitem_id)

FOR resolution_reason IN ([deferred], [fixed], [by design])

) AS pvt

order by application

Dave

I think the solution is not with the select but with your stored procedure usp_AddEmp. You need to insert the child with the appropriate HierarchyId. Possibly something like

-- CREATE STORED...

You could add an extra analytic count of the columns and compare that with the row number

with cte as

(

select testcol

,ROW_NUMBER() over (PARTITION by testcol...

I think that map and spatial data visualization was introduced in SSRS 2008 R2. http://msdn.microsoft.com/en-us/library/ms170438(v=sql.105).aspx#DataVisualization

Dave

Dwain,

I agree with everything you say. I realised you were jesting re the originality of the code. I've never met Tim Berners Lee either.

Like you, I hardly ever trust...

Dwain,

Why re-invent the wheel, I know none of my code has been truly innovative, but has been an adaptation of someone else's ideas. (Oh, there was that time I was...

I realise that this is a 2005 T-SQL forum, but in T-SQL 2012 this can be accomplished by the new LAG() and LEAD() analytic functions http://msdn.microsoft.com/en-us/library/hh231256.aspx and http://msdn.microsoft.com/en-us/library/hh213125.aspx ...