I would do it in three steps:

step 1: build an intermediate table with preaggregated values

step 2: do the quirky update

step 3: display the pivoted data using crossTab

Something like this (side...

Based on your description and ready to use sample data it was really easy to work on and to match your desired result.

This is one of the very best formats...

Something like this? (The trick is to assign a number to each row per Region and use this new column to group the data, thus eliminating the null values).

SELECT

MAX(ISNULL(pvt.[Region...

poratips (3/30/2010)

---

Hi Lutz,

Thanks so much for your help!

When i alter to add columns as datatime datatype and update my both the columns, can you show me how to make date...

Hi Chandru,

select @l_identity=@@identity

will only return the last row and not all rows. You'd need to use the OUTPUT clause together with an intermediate table. See the code snippet below.

Please note...

I'd love to agree but, unfortunately, the...

Right now we're just guessing regarding her(his?) identity.

As long as there is no proof that we're talking about the same person I'm not going to make such an assumption.

It might...

Just being curious: How close is that particular behavior of that specific person to be qualified as being a Troll? (disclaimer: limited to that particular thread due to the lack...

Did you try to search for "delete duplicate rows" on this site (using the search box on the upper right corner of this site).

You probably will find this link as...

Without seeing two rows (one that passed and one that failed) it's almost impossible to say what's causing the error...

Ughh!

That's going to be quite a bit of work...

Do you have any chance to either use SS2K5 or above or at least get the Date values numbered for each...

So, on what version do you want to use...

That's...

ALTER TABLE [dbo].[SSTrackTime] ADD Start_datetime DATETIME

ALTER TABLE [dbo].[SSTrackTime] ADD Stop_datetime DATETIME

GO

UPDATE [dbo].[SSTrackTime]

SET

start_datetime = CASE WHEN ISDATE(START)=1 THEN START ELSE CAST(START AS XML).value('xs:dateTime(.[1])', 'datetime') END,

stop_datetime =...

instead of

CASE WHEN client_ind = 'Y'

THEN picklist1ms AS CaseType

ELSE '' AS CaseType

END

it needs to be

CASE WHEN client_ind = 'Y'

THEN picklist1ms

ELSE ''

END AS CaseType

Edit: ... just like...