The VALUES clause simplifies it considerately, which can be great for longer lists:

CROSS APPLY (

SELECT MIN(date)

FROM (

...

SQLSACT (1/9/2015)

---

SELECT sku, product_description,

(SELECT plant_nbr

FROM ProductPlants AS B

WHERE B.sku =...

DECLARE @sql varchar(max)

DECLARE @column_list varchar(max) --as returned by first query

SET @column_list = 'age,sex,race' --for example

SET @sql = 'UPDATE tbl2 SET col2 = [' + REPLACE(@column_list, ',', ']+[') + ']'

PRINT...

I suspect recursion would be great for this, but that's not my specialty. This is somewhat akin to the earlier LOJ version, which unfortunately I didn't see until now...

Eric M Russell (1/9/2015)

---

ScottPletcher (1/8/2015)

---

You could also GROUP directly on the CASE expression itself:

SELECT

CASE

WHEN message_text LIKE...

Not necessarily true -- a given index can be empty even if the table is...

You could also GROUP directly on the CASE expression itself:

SELECT

CASE

WHEN message_text LIKE '%ThePartToGroupBy%' THEN '%ThePartToGroupBy%'

...

Might as well have max flexibility on the number of name levels provided:

DECLARE @tablename varchar(500)

DECLARE @sql varchar(8000)

SET @tablename = 'YourTableName'

--SET @tablename = 'server1.db1..YourTableName'

SET @sql = 'SELECT * FROM '...

SELECT

DD1.[Incident_x0020_Date] AS [Incident Date],

1 AS [Order],

DD1.[Int_x002f_Dom] AS [Location],

DD1.[ACTION] AS [Action],

DD1.[Channel] AS [Channel Type],

SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Bribery & Corruption' THEN 1 ELSE 0 END)...

Note that you can also create non-temp tables in the tempdb database. That way the table will be there even if the connection that created it "gets broken" or...

The index is empty, i.e., there are no rows in that index.

SELECT sm.*, tv_lookup.Amount AS tv_Amount, tv_lookup.PercRate AS tv_PercRate, tv_lookup.Code AS tv_Code

FROM #SM sm

OUTER APPLY (

SELECT TOP (1) *

FROM #TestValues tv

...

Jeff Moden (1/8/2015)

---

Now, based on what I just said, one of my first questions on an SQL Server technical interview for DBAs and Developers alike is "How do you get...

I'm almost certain the optimizer is sophisticated enough to implicitly convert the literal value rather than a variable value or a column.

If you're still concerned about it, you could do...

If you primarily use columns "Company" and "Form_Name" to do lookups and/or joins on table "COLUMN_NEXT_ID_RECENT_ITEMS", make sure the table is clustered on those columns. [It might, instead, be...