Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Alternative to manual pivots Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 7:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:28 AM
Points: 142, Visits: 139
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



Post #1565906
Posted Tuesday, April 29, 2014 7:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 13,069, Visits: 11,908
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1565918
Posted Tuesday, April 29, 2014 7:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 7,048, Visits: 6,797
What is generating the report?


Far away is close at hand in the images of elsewhere.

Anon.

Post #1565924
Posted Tuesday, April 29, 2014 8:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:28 AM
Points: 142, Visits: 139
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



Post #1565948
Posted Tuesday, April 29, 2014 8:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 13,069, Visits: 11,908
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1565968
Posted Tuesday, April 29, 2014 8:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 3,333, Visits: 7,193
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.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1565984
Posted Wednesday, April 30, 2014 2:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:28 AM
Points: 142, Visits: 139
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



Post #1566257
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse