GilaMonster (2/27/2013)

---

In this case it's simply the smallest index that can be used to satisfy the query.

Selection has to do with which index can filter the largest number of the...

dwain.c (2/8/2013)

---

vinu512 (2/7/2013)

---

I worked it out. This is the query that would get you the results you want:

--Creating Table

Create Table Ex1

(

Id Int Identity(1,1),

AllLevels Varchar(MAX)

)

--Inserting...

hi Chris, it definitely runs like a Rocket. It would be really nice if you could explain the following code :

SELECT df = LEFT(b.AllLevels, CHARINDEX('»',b.AllLevels,1)-1)

UNION ALL

SELECT df = SUBSTRING(b.AllLevels,N+1,...

demonfox (2/7/2013)

---

Abu Dina (2/7/2013)

---

Fair enough. I think I'm trying to be too clever when I should be concentrating on getting the rest of the project done first! This part works...

+1

Please post the result set that is expected from the above data.

I hope this is what you require from the above data:

Declare @sql As Varchar(MAX)

Select @sql = 'Select paymentID, ' + STUFF((Select Distinct ',SUM(Case When...

So if the incoming data doesn't have a forename then what do you insert into the table??..Do you Insert NULL??....

I think you should read upon Coalesce.

If you still cant...

Even Better.......if you create a Non Clustered index On the Tally table then you get the results even fatser.

Create NonClustered Index NCLIX_Tally On Tally(n)

Select Id,

...

You can switch the Cross Apply in the query with an Outer Apply, that would reduce the time by more than 70%, as follows :

Select Id,

...

I worked it out. This is the query that would get you the results you want:

--Creating Table

Create Table Ex1

(

Id Int Identity(1,1),

AllLevels Varchar(MAX)

)

--Inserting Sample Data...

What do you mean by cleaning tables??

You can't do that using the case.....The case would perform the Update operation. To avoid the Update as a whole you need to wrap the Update statement in an If...Then....

All the above methods would get you the required data. This is just an extension to them.

Incase you have more than three attribute Types which may change, then you can...

I tried the query on random sample data consisting of 1164000 rows.

Dwain, your query is the clear winner. Here are the results for all the three queries :

Dwain's...