Hmmm... All those ORs in the ON clauses looks pretty messy. Here's an alternate way but I haven't tested to see if it performs better:

DECLARE @T1 TABLE (comp_name...

ChrisM@Work (2/26/2013)

---

mister.magoo (2/26/2013)

---

ChrisM@Work (2/26/2013)

---

dwain.c (2/26/2013)

---

ChrisM@Work (2/26/2013)

---

peter-757102 (2/26/2013)

---

Using a binary comparisons is a good speedup for specific cases and good to be aware of and certainly worth mentioning in bold ...

ChrisM@Work (2/26/2013)

---

peter-757102 (2/26/2013)

---

Using a binary comparisons is a good speedup for specific cases and good to be aware of and certainly worth mentioning in bold with its benefits. What...

Ahhhh! Now I've got it without the fudge factor.

DECLARE @TimeAdd VARCHAR(20) = '41:41:41.041'

SELECT GETDATE()

,DATEADD(hour

,ABS(LEFT(@TimeAdd, 2))

...

Strike that last post. The issue seems to be here:

SELECT GETDATE(), DATEDIFF(millisecond, 0, CAST('00:'+RIGHT('41:41:41.041', 9) AS TIME))

Where DATEDIFF should be returning 2501041 instead of 2501040.

Jeff Moden (2/22/2013)

---

dwain.c (2/21/2013)

---

The only reason I went to DATETIME2 was thinking that the extra precision would take care of the 1 millisecond issue that arises when DATETIME is used...

ajames 36824 (2/22/2013)

---

Well it looks like it says that for each question in the QuestionID column, return...

Clarity is a beautiful thing.

SELECT ClientID, DOV, Form

,[Alcohol1-001]=MAX(CASE QuestionID WHEN 'Alcohol1-001' THEN CAST(Response AS VARCHAR(3)) ELSE '' END )

,[Alcohol1-002]=MAX(CASE QuestionID WHEN 'Alcohol2-002'...

mister.magoo (2/22/2013)

---

ChrisM@Work (2/22/2013)

---

L' Eomot Inversé (2/22/2013)

---

mister.magoo (2/22/2013)

---

And here is the code (with notes removed for brevity) and the changes underlined.

Sorry if I have made a mistake, but I think this...

Have you tried the solution I suggested?

I'm thinking it will do what you need.

I hope I didn't confuse you by putting Table2 into the CTE.

I like Sean's solution and got to wondering whether a subquery would work as well:

SELECT t.*, isnull((

select top 1 ItemCode as ItemCode

...

ChrisM@Work (2/22/2013)

---

I'm not so sure either - the tables might look like this:

DROP TABLE #Table1

SELECT

*

INTO #Table1

FROM (

SELECT Column1 = 'microsoft outlook 2007,microsoft 2007 outlook,microsoft out look 2007,microsoft 2007 office,microsoft...

You can also EXEC the @sql returned by this:

DECLARE @sql nvarchar(max) = ''

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

BEGIN TRY

...

Is it possible...

I think I get the question but not sure, so here's a shot in the dark:

DECLARE @Table1 TABLE (software_name_raw VARCHAR(100))

INSERT INTO @Table1

SELECT 'microsoft outlook 2007'

UNION ALL SELECT 'microsoft 2007 outlook'

UNION...