April 29, 2014 at 7:23 am
Hey all,
I have a rather complicated report using a stored procedure that uses the;
SELECT @vals = @vals + ' ' +
REPLACE( REPLACE(
then lots of case statements to pivot my row data to column data. It works fine, although it is ugly and everytime I look at it I think what the hell is going on here!
My problem is that the final string that is created and then executed often exceeds the 8000 character limit on certain reports. I have shaved down column names and stuff like that to get it all working again but its obviously not a long term solution.
What are my options moving forward....I dont have a license for a newer version of MSSql which I believe have limitless (as good as) text strings. I've never used OLAP or created a cube so any pointers advice is greatly appreciated.
Thanks
April 29, 2014 at 7:37 am
kangarolf (4/29/2014)
Hey all,I have a rather complicated report using a stored procedure that uses the;
SELECT @vals = @vals + ' ' +
REPLACE( REPLACE(
then lots of case statements to pivot my row data to column data. It works fine, although it is ugly and everytime I look at it I think what the hell is going on here!
My problem is that the final string that is created and then executed often exceeds the 8000 character limit on certain reports. I have shaved down column names and stuff like that to get it all working again but its obviously not a long term solution.
What are my options moving forward....I dont have a license for a newer version of MSSql which I believe have limitless (as good as) text strings. I've never used OLAP or created a cube so any pointers advice is greatly appreciated.
Thanks
It seems you have an issue with something. However, you didn't actually post any details about the issue or what you are trying to do. You sort of indicate that you might be using some dynamic sql here but the very brief snippet you posted does not have any dynamic sql.
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2014 at 7:41 am
What is generating the report?
Far away is close at hand in the images of elsewhere.
Anon.
April 29, 2014 at 8:17 am
Hi there,
I didnt include all the T-SQL as its very lengthy and its really a philosophical question rather than anything about the actual code being used.
I have included the snippet below which is what is used to dynamically generate the required columns from rows.
-- DATA IS SUMMARISED IN A TEMP TABLE THEN PIVOTTED BELOW
-- THE DATA IS LOTS OF CONSTRUCTION ITEMS THAT ARE COMPLETED OR NOT
-- THE QUERY MUST ALSO RETURN VARIOUS BIT SOF INFORMATION ABOUT EACH COLUMN
-- THIS EXTRA INFO IS STUFFED INTO THE COLUMN NAME WHICH IS THEN SPLIT BY THE | CHARACTER TO OBTAIN THE VARIOUS BITS AND PIECES
SET @SUMMARIES = 'SUM(TOTALJOBRATE ELSE 0)[]'
SET @PIVOTSTR = ''
-- JD is the item description and I want to pivot around these
-- AREA is soemthing like lounge, hallway etc...JD (item descriptions) can be the same in different AREAs, I need them to be GROUPED within AREAs
-- ' + AREA + '|' + TASK + '|' + JD + '|' + CAST( CAST(JOBRATE AS DECIMAL(10,2)) AS VARCHAR(20) ) ) This big ugly thing gives me the column data I need to display in the report, the AREA NAME, TASK TYPE, DESCRIPTION and the average job rate
SELECT @PIVOTSTR = @PIVOTSTR + ', ' +
replace( replace( @SUMMARIES, '(' ,'(CASE WHEN JD =''' + JD + ''' AND AREA = ''' + AREA + ''' THEN ') , ')[', ' END) as [' + AREA + '|' + TASK + '|' + JD + '|' + CAST( CAST(JOBRATE AS DECIMAL(10,2)) AS VARCHAR(20) ) )
FROM #ITEMLIST
--PRINT @VALS
EXEC ( 'SELECT TID, TNAME, PN, A' + @PIVOTSTR + ' FROM #PROPERTYLIST WHERE JOBRATE > 0 GROUP BY PN, A, TID, TNAME ORDER BY TID, A, LEN(PN), PN')
As I have no control over the number of potential columns, the @PIVOTSTR string can quickly grow to over 8000 characters in some instances.
If I cant find a 'better' way to do this I may create the SQL statement in my application, using an initial query to get the column headings and creating the pivot and executing it, this could also be made DB agnostic which is always nice!
I was really just looking for alternative ways of creating dynamic pivots which dont involve creating long concatenated strings.
Thanks
Rolf
April 29, 2014 at 8:37 am
Take a look at the links in my signature about cross tabs, specifically the second one about dynamic cross tabs. I am pretty sure this is what you are looking for. Of course you will have to make some adjustments since you are using 2000.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2014 at 8:52 am
You should find the approximate length of your rows that are being stored in @PIVOTSTR to prevent them of overflowing the length. A safe approximate could be 86 (number of static chars that I could count ) + twice the length of AREA + twice the length of JD + the length of TASK. Divide 8000 by the result of the previous row and use as many variables as needed to get all the columns desired.
It's something similar to what Jeff explains on this article: http://www.sqlservercentral.com/articles/T-SQL/72129/
I hope that helps.
April 30, 2014 at 2:09 am
Hi Sean,
I read the dynamic cross tab post, very interesting. My issue is that the possible columns for each CASE statement (in your post dates) are completely unknown and variable so I cant use a tally table straight off as in your case.
Also I would run straight into the same issue using the tally table of the main dynamic part of the code running over 8000 characters as it is generated, just as it is now.
I guess if I use the incremental field at least I can do it in sections and use multiple varchar variables.
Thanks
Rolf
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply