I wouldn't exactly call it mine (:blush:) as the initial links in the article...

You are most welcome. As I said above, it turns...

My first thought, like ChrisM@Work was to use a rCTE for this, showing as always that great minds think alike! 😛

So, to be different, we'll propose a QU approach, which...

For a working solution, you'll need to give us a little more:

1. DDL to CREATE a table

2. Consumable sample data (INSERT/SELECT/UNION ALL SELECT), and

3. A little more information on how...

One point that is worthy of note. While the rCTE nTuples solutions didn't make the winners cut here, it does have advantages in certain cases.

It loses because it generates...

PiMané (9/17/2012)

The query doesn't differentiate a "normal" index column from an "include" index column...

In following statements idx_dupIndexes_03 and idx_dupIndexes_06 are considered duplicate but they're not.

CREATE TABLE dupIndexes (id1 INT, id2...

Forgive my innocent question here but why a FUNCTION at all?

Just replace:

HOST0140.NAME

with:

NAME=RIGHT(HOST0140.NAME, LEN(HOST0140.NAME)-(1+CHARINDEX(', ', HOST0140.NAME))) + ' ' +

LEFT(HOST0140.NAME, CHARINDEX(', ', HOST0140.NAME) - 1)

I think that there are probably faster and better ways, but I think this is one way:

;WITH IndexColumns AS (

SELECT DISTINCT a.object_id,...

How about something like this?

;WITH PriorQueryResults AS (

SELECT Table_Name='Project Phase', [Lookup Table Value]= 'Build'

UNION...

Cadavre - Cool idea! Here's a terser version of yours:

;WITH MyNums AS (

SELECT num=a.num + 10*b.num + 100*c.num + 1000*d.num + 10000*e.num + 100000*f.num

FROM (VALUES (1),(2),(5),(7),(8),(9)) a (num)

CROSS APPLY...

And here's one specially built for ChrisM@Work, who loves cascading CROSS APPLYs:

;WITH SourceNums AS (

SELECT num FROM (VALUES (1),(2),(5),(7),(8),(9)) a (num)

), MyNums AS (

SELECT num=a.num + 10*b.num + 100*c.num +...

Eugene Elutin (9/13/2012)

I like this one, as it's very different approach, but yeah it's still much...

Sean Lange (9/13/2012)

LOL by no means was any disrespect intended good sir. 😀 I of course...

The DDL you posted doesn't seem to fully match your second post's query.

I suggest you post complete DDL and sample data in a readily consumable format (i.e., INSERT/SELECTs) that can...

I think I'll change my board name. 🙂