CELKO (1/13/2011)

---

SQL uses tables and not matrice. You also seem to want to format data for display in the dataabse, which violated the whoel idea of a tiered archieture.

My thought...

Both versions will cause a table scan since the search condition is not SARGable.

Maybe it's more efficient to shred the data and query the new table with proper indexing.

It depends...

Almost understood.

In your initial post you stated

Referencing my previous post, how do you get G1...

The temp table has been used to get a code block for demonstration purposes.

Assuming you're last statement will end with a semicolon, you could simply use

WITH CTE AS

(

SELECT *

...

Just as a side note:

Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server (2008 and up).

How would your expected output look like if all the changes for one...

Expanding Waynes code:

DECLARE @test-2 TABLE (id INT, val VARCHAR(100));

INSERT INTO @test-2

SELECT 1,'jack,25,A67,91J,67,99' UNION ALL

SELECT 2,'jill,5,8,V3' UNION ALL

SELECT 3,'john,7,D4' UNION ALL

SELECT 4,'bill,66,84';

WITH CTE AS

(

SELECT *

FROM

@test-2

...

At the beginning of your post you said "based on a set of data"; but at the end "Any help in terms of what the table structure ... should look...

WayneS (1/13/2011)

---

You're in the LA area? I thought you were in...

Any of the Threadizens interested in a performance tuning project in the Los Angeles area? Drop me a PM.

Do you have any question? You posted some code. Why?

I guess the issue is related to this thread.

But it could be something completely different, too...

lduvall (1/12/2011)

---

Awesome! Yes, both of these help me tremendously! Thank you so much!

I've been wanting to learn about the STUFF syntax, too. I'm going to dive into both of...

Well, you DO have something to aggregate...

Attached please find tow code snippets:

the...

Instead of using PIVOT I'd rather go with CrossTab and DynamicCrossTab, since the code structure of PIVOT is kinda weird and it doesn't perform better than the CrossTab methods either.

I'd...