Itzik - You are the man. Betweeen you & Joe Celko, I get to keep my job!

Shalom!

Tom
Spring Lake, MI

tom11/3/2005 7:11:44 AM

This article was very useful. Saved me a lot of trial and error. Thank you!

Anonymous User 7/28/2005 1:29:41 PM

Thank you! It helped me a great deal!

Anonymous User 5/11/2005 11:24:26 AM

Try AGS Crosstab Extended Stored Procedure. It does all teh work and results are far quicker than any group by column set based solution
http:///www.ag-software.com

Anonymous User 5/9/2005 6:45:17 AM

TIP: If your table or field names have spaces in them, use the QUOTENAME() function to delimit the parameters to this stored proc, whenever they are used.

David5/6/2005 3:13:39 AM

Great Logic and Generic, ideal for less columns.

But there was a limitation when I tried with many cross tab columns (final result columns). I had to generate croiss tab columns as products and had more than 100 and few columns before the dynamic cross tab columns. The @sql concatenation stopped at one point, even though I had set the @sql varchar length high, looks like there is a limit to the number of concatenation.

Well, another point - when the query is built dynamic and fired through an SP, I think there would be no execution plan for better performance.

If the number of columns are high, I would suggest the sql be built in the front end. This way the performance was better, when I tried for more than 50 cross tab columns using SP and in Front End(.Net) to generate SQL

- Krushna Kumar

Krushna Kumar4/22/2005 2:29:47 PM

Very, very cool! Thank you!

Anonymous User 3/23/2005 11:17:00 AM

Itsik,

Inna Tzipris -February 23, 2004 wrote to you asking:
How can I insert the stored procedure's output into a table? I don't know how many colomns I'll get and also not an order of columns. So, I can't use insert into statement.

I too need to output the data to a table so that I can use it with Crystal Reports.

How do you do this?

Thanks,

Jim

Jim2/18/2005 11:48:44 AM

REPLACE basically replaces each occurrence of a substring within a string, with another substring. The expression REPLACE(@key, '-',') replaces each occurrence of a dash in @key with an empty string; in other words, removes all dashes from @key.

Itzik Ben-Gan


BG_SQL 12/16/2004 10:22:53 AM

Good job

Anonymous User 12/15/2004 8:13:52 AM