I also notice that you did not...

LEFT is never SARGable.  LIKE is SARGable if the pattern begins with a constant.

Drew

PIVOTs are not very flexible.  I almost always use a CROSSTAB over a PIVOT, because CROSSTABs are so much more flexible (and the syntax makes a lot more sense).

Drew

Hi Michael, The indexes recommended by SQL Server are not always the best, the index you would need to try is:

CREATE NONCLUSTERED INDEX [IX98_SampleData3]
ON [dbo].[SampleData2] (ACC,[Name],[Ref],[DATE])...

It also requires two scans of the table and is approximately twice as expensive as a result.  It will always perform worse than...

Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma).  It's simpler to handle this using leading commas rather than...

• This reply was modified 6 years, 10 months ago by drew.allen.

Most of these methods don't correctly handle situations where the final value is null (leaving a trailing comma).  It's simpler to handle this using leading commas rather than trailing commas. ...

I believe that this gives you the expected results (although it is different from the results that Des' solution gives).  It only requires a single scan of the table.

J. Drew Allen

Business Intelligence Analyst

Philadelphia, PA