So, to make sure I'm clear with what you want.

With this sample data: -

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment;

END;

SELECT [Sample_ID], [Rep_ID], [Result]

INTO #testEnvironment

FROM (VALUES(1, 1, 'O152'),(1, 2, 'O2'),(1, 3,...

Steve JP (2/28/2013)

---

Using the above sample from Cadavre to load the data here is an alternative. Admittedly you will need to prejudge the possible number of options and pad out...

I guess something like this: -

DECLARE @sql NVARCHAR(MAX);

SELECT @sql='SELECT '+CHAR(13)+CHAR(10)+

STUFF((SELECT ','+CHAR(13)+CHAR(10)+'NULLIF(MAX(CASE WHEN [name] = '+CHAR(39)+[name]+CHAR(39)+

' THEN [length] ELSE '+CHAR(39)+CHAR(39)+' END),'+CHAR(39)+CHAR(39)+

...

Borrowing slightly from Jeff, I think I'd do it like this: -

SELECT StringDate, LongDate, ConvertedDateTime

FROM #TestTable

CROSS APPLY (SELECT RTRIM(LTRIM(REPLACE(REPLACE(StringDate COLLATE LATIN1_GENERAL_BIN, ',', ''), dow, '')))

...

Using this sample data: -

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment;

END;

SELECT ID, NAME

INTO #testEnvironment

FROM (VALUES(1,'a'),(1,'b'),(1,'c'),(1,'d'),(2,'e'),(2,'f'),

(3,'g'),(3,'h'),(3,'i'),(4,'j'),(5,'K'),(5,'L'),

...

Read up on XACT_ABORT. XACT_ABORT is by default set to OFF in most instances for code in SQL Server, but inside a trigger it is set to ON by default.

So, using this sample data: -

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment;

END;

SELECT Name, Color, IDX

INTO #testEnvironment

FROM (VALUES('item1','Black',1),

('item2','Green',2),

...

Anything stopping you just doing this?

MERGE #OriginalData original

USING #UpdatedData changed ON original.MemberID = changed.MemberID AND original.LocationID = changed.LocationID

WHEN MATCHED AND EXISTS (SELECT original.MemberID, original.LocationID, original.FirstName, original.LastName, original.Birthdate, original.AreaCode, original.PhoneNumber

...

LutzM (2/23/2013)

---

I'd use the cte approach. I'm confident it can be compressed a little further but this would be my starting point:

WITH cte_originalquery AS

(

SELECT 1 AS Number UNION ALL

SELECT 2...

2005/2008 it would be something like this: -

SELECT a.principal_id, a.name, OBJECT_NAME(b.major_id) AS [object], b.[permission_name], b.state_desc

FROM sys.database_principals a

LEFT OUTER JOIN sys.database_permissions b ON a.principal_id = b.grantee_principal_id

WHERE a.name = 'public';

You'd need to...

Guess you're after this: -

SELECT a.ID, Color, Date1, Standing, Name, b.Date2

FROM VW_Tbl1 a

INNER JOIN (SELECT ID, MAX(ENTRY_TIMESTAMP) AS Date2

...

Nope, you call up and shout "Urgent! Body Help Now!".

Then...

No.

thbaig1 (2/19/2013)

---

I want to use profiler...

Lowell (2/19/2013)

---

awesome solution Cadavre!

only thing i would do is clean up the presentation with REPLICATE to get the zeros, and maybe just a simple not like '%[^0-9]%' ; what do...

Yes. . . did you execute the code I produced? So long as you sort on the computed column "useful_faux_number" it...