Also, the caps are annoying. I...

Jeff Moden (10/12/2009)

---

Garadin (10/12/2009)

---

[Edit] Previously suggested a cast method for datecol that as Michael pointed out could prevent index usage. Removing the suggestion so as not to steer...

Michael Valentine Jones (10/12/2009)

---

I just tested both methods against a large table (44,000,000 rows) with a datetime column that had an index. See sample queries below.

The first did an...

DECLARE @A varchar(100)

SET @A = 'BMW 320 48 month'

SELECT @A

SELECT LEFT(RTRIM(REPLACE(REPLACE(@A,'Month',''),'mnths','')),

LEN(RTRIM(REPLACE(REPLACE(@A,'Month',''),'mnths',''))) - 2) CarModel,

RIGHT(RTRIM(REPLACE(REPLACE(@A,'Month',''),'mnths','')),2) ContractMonths

Replace @A with the name of your column.

saivko (10/12/2009)

---

Thanks so very much Arun! I'm not used to using sub-tables, very nice to learn from simple examples.

Seth Phelabaum, thanks also. I just wanted the...

WHERE LEFT(LastName,1) BETWEEN 'A' AND 'M'

Michael Valentine Jones (10/12/2009)

---

That is not a good method, since it means that datecol will be cast it an int and back to datetime for every row, and the query...

[Edit] Previously suggested a cast method for datecol that as Michael pointed out could prevent index seeks. Removing the suggestion so as not to steer people who don't...

Arun's solution is how you would accomplish leaving colD unaggregated and in the query. However, it seems (from your requested output) like you only want 1 of colD, not...

Jack Corbett (10/9/2009)

---

I'm not sure what else to call it when I reference my own blog. I feel kind of dirty doing...

PP-564103 (10/9/2009)

---

I would suggest to read the article as suggested by Garadin before using this, but crosstab query would look like this,

...

BTW, I should mention that I could write...

PIVOT syntax is somewhat confusing. Take a look at the article in my signature about cross tab reports. IMO, it uses much simpler syntax and is every bit...

"pimp my blog". Nice. :w00t:

Take a look at the article on Tally tables in my signature. It will explain how to use this table to split strings of this nature.

You can do it with dynamic SQL if...