dwain.c (6/22/2012)

---

After multiple runs, I am starting to see an improvement using your modification over the original! Great work Chris!

When I posted the article, Jeff took a shot at...

dwain.c (6/22/2012)

---

Note that removing the check column as you have done does get you about a 10% speed improvement.

If you were right though, does that mean my version is 3125x...

dwain.c (6/22/2012)

---

I see you also removed CHARINDEX. However I can't verify your results. I get this running with n<=2 in the recursive leg:

-- Original

(115681 row(s) affected)

SQL Server...

Yours:

SELECT 1, Tuples = CAST(Prod AS VARCHAR(8000)), value

FROM @t

UNION ALL

SELECT n.n+1, t.Prod + ',' + n.Tuples,...

dwain.c (6/21/2012)

---

Wow Chris! That's...

jeffem (6/21/2012)

---

... if order of results creates uniqueness (for example, "1 2 3" is distinct from "2 3 1"). If order of results does NOT create uniqueness, then you would...

jeffem (6/21/2012)

---

ChrisM@Work (6/21/2012)

---

David Webb-200187 (6/19/2012)

---

Hmmmm....

Let's say you had 50 rows, all with a quantity of 1. The target quantity was 5.

How would you want that brought back? All...

David Webb-200187 (6/19/2012)

---

Hmmmm....

Let's say you had 50 rows, all with a quantity of 1. The target quantity was 5.

How would you want that brought back? All the possible...

farooq.hbs (6/21/2012)

---

Hey

My Metric Values returning two times Lyk

IsNUll(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Hard Savings' then m.value End),0) as HardSavingFY11,

IsNull(Sum(Case When Datepart(year,mdate)= 2012...

You don't need dynamic SQL for this:

SELECT NewColumn

= CAST(Col1 AS VARCHAR(10)) + ' - '

+ CAST(Col2 AS VARCHAR(10)) + ' - '

+ CAST(Col3 AS VARCHAR(10))...

Since GENERAL_COMMODITY_CD table has a different cardinality to your result set, having multiple rows per BOOKING_ID, you will need to preaggregate it, either as a CROSS APPLY or as a...

pwalter83 (6/20/2012)

---

yeah, I get only one row per BOOKING_ID and...

Are you seeing one row per BOOKING_ID in your output?

- here's where adequate sample data would have helped 🙂

pwalter83 (6/20/2012)

---

... I did the following but somehow it doesnt work. Could you please tell what I may be doing wrong:

---------------------------------

CROSS APPLY (

SELECT

=

STUFF(

(SELECT ', ' + mbc.GENERAL_COMMODITY_CD...

Thanks Paul: try this...

SELECT

MB.BOOKING_ID,

BOOKING_NUM,

--------------------------------------------------------

a.[BOOKING EQUIPMENT],

--------------------------------------------------------

GENERAL_COMMODITY_CD + ',' AS GENERAL_COMMODITY_CD -- qualify this column with a table alias so others know which table it's from

FROM MG_BOOKING MB

LEFT JOIN (

SELECT

BOOKING_ID,...