Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Dynamic Vertical Rows to Horizontal Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, June 28, 2013 4:38 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, September 21, 2016 10:38 AM Points: 24, Visits: 124
 I read through the following topic already, and it has moved me MUCH closer to the solution that I'm seeking than I was getting otherwise, but I can't seem to find the answer to my questionStarting Point: Vertical to HorizontalMy situation is somewhat similar in that I have a unique identifier than can have, at a max, 60 rows tied to it - however, there are often quite a few less, but knowing the upper boundary is useful, I think.I'm trying to figure out an easier way than taking the following block of code and having to copy-paste 60 times in order to account for the maximum number of rows I'm trying to "pivot" out:`SELECT MK, HK, CK, MAX(CASE WHEN N=1 THEN MD ELSE NULL END) AS MD1, MAX(CASE WHEN N=1 THEN BN ELSE NULL END) AS BN1, MAX(CASE WHEN N=2 THEN MD ELSE NULL END) AS MD2, MAX(CASE WHEN N=2 THEN BN ELSE NULL END) AS BN2, MAX(CASE WHEN N=3 THEN MD ELSE NULL END) AS MD3, MAX(CASE WHEN N=3 THEN BN ELSE NULL END) AS BN3FROM TMP_URECROSS APPLY (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) X(N)WHERE RN=NGROUP BY MK, HK, CKORDER BY MK, HK, CK`And perhaps copy-pasting 60 times really is the best way, but it would seem that I should be able establish a variable that has a range of 1 - 60, and then loop the above statement for every value between 1 and 60, but I'm not sure, and more to the point, I'm not sure how to do so.Am I just barking up the wrong tree?
Post #1468734
 Posted Friday, June 28, 2013 4:44 PM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 1:48 PM Points: 8,233, Visits: 17,809
 There's a better way, you need to check for Dynamic SQL. Check the article Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs and come back if you need more help. Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1468737
 Posted Monday, July 1, 2013 12:34 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, September 21, 2016 10:38 AM Points: 24, Visits: 124
 Luis Cazares (6/28/2013)There's a better way, you need to check for Dynamic SQL. Check the article Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs and come back if you need more help.In the thread I linked to, there was an example using PIVOT to accomplish this goal, with a similar comment to search out examples of Dynamic SQL & PIVOT.The problem with PIVOT - and we've tested using the example given - is that it doesn't automatically collapse data together, it maintains the placement of nulls if there doesn't happen to be a record in that "slot" for a given customer. Doesn't seem like a big deal, until you realize that the table this will ultimately fill will be 123 columns wide (not what I'd prefer, but I don't have any other options).The CROSS APPLY, because it's using a UNION, takes the next available value and tacks it on and there's no nulls between values.Last, but not least, PIVOT took longer than the CROSS APPLY when it was run against the full set of live data on just the three columns in the sample I provided.
Post #1469189
 Posted Monday, July 1, 2013 1:45 PM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 1:48 PM Points: 8,233, Visits: 17,809
 I'm not sure if I can be of great help. My problem is that I can't see what you see.I gave you a link to an article that explains very clearly how to do what you need (it has a first part that you should read if you don't understand fully of what the article talks about).I can give you some code, but I can't test it and I'm not sure if it will work.In order to help you more, please read the article linked in my signature to help you post DDL, sample data and expected results based on the sample data.`DECLARE @SQL1 varchar( 40) = '', @SQL2 varchar( 8000) = '', @SQL3 varchar( 60) = '' ;WITH Numbers(RN) AS( SELECT DISTINCT RN FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3)TMP_URE(RN) --This line should use your table.)SELECT @SQL2 = @SQL2 + ' ,MAX(CASE WHEN N=' + CAST( RN AS varchar(2)) + ' THEN MD ELSE NULL END) AS MD' + CAST( RN AS varchar(2)) + ', MAX(CASE WHEN N=' + CAST( RN AS varchar(2)) + ' THEN BN ELSE NULL END) AS BN' + CAST( RN AS varchar(2))FROM NumbersORDER BY RNSET @SQL1 = 'SELECT MK ,HK ,CK'SET @SQL3 = 'FROM TMP_UREGROUP BY MK, HK, CKORDER BY MK, HK, CK'PRINT @SQL1 + @SQL2 + @SQL3EXEC( @SQL1 + @SQL2 + @SQL3)` Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1469207
 Posted Monday, July 1, 2013 4:14 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, September 21, 2016 10:38 AM Points: 24, Visits: 124
 I ended up going the Copy/Paste route and iterating every possible number in order to get this working - it's ugly, but it does work.I'm sorry about not providing sample data - generally I can come up with something that's a rough approximation, but the particular table that I'm doing this on has too much uniqueness to it. The best thing I could come up with is to take the original example of a store with 3 phone numbers and make it a store than can have up to 60 phone numbers.That said, it only takes a 1m 34s to return 1,141,485 rows, so it's not that bad.I did try your solution, and the printed SQL looked correct, but when it tried to execute, it couldn't find the CTE named TMP_URE (to use the example quoted in the sample; I replaced with the actual name in my attempt), so I can only guess that it can't find it once it reaches that point of the select for some reason.
Post #1469233
 Posted Monday, July 1, 2013 4:50 PM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 1:48 PM Points: 8,233, Visits: 17,809
 TMP_URE is the name that you supplied in your code. I assumed that was your table. You should change it for the dynamic code to run. Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1469237
 Posted Monday, July 1, 2013 4:58 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:52 PM Points: 42,083, Visits: 39,479
 N.B. (7/1/2013)I ended up going the Copy/Paste route and iterating every possible number in order to get this working - it's ugly, but it does work.The article that you were provided a link for on dynamic crosstabs could have saved you a lot of time. It has a technique for how to have the code write code instead of doing all that CPR (Copy/Past/Replace).Here it is again. You really should read it because the methods used have application in a whole lot of places.http://www.sqlservercentral.com/articles/Crosstab/65048/ --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #1469238
 Posted Tuesday, July 2, 2013 12:56 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, September 21, 2016 10:38 AM Points: 24, Visits: 124
 Jeff Moden (7/1/2013)N.B. (7/1/2013)I ended up going the Copy/Paste route and iterating every possible number in order to get this working - it's ugly, but it does work.The article that you were provided a link for on dynamic crosstabs could have saved you a lot of time. It has a technique for how to have the code write code instead of doing all that CPR (Copy/Past/Replace).Here it is again. You really should read it because the methods used have application in a whole lot of places.http://www.sqlservercentral.com/articles/Crosstab/65048/Hi Jeff,I did read through it, but I was having difficulty applying it to the situation I'm in - the thing is, there really is nothing dynamic about this - there are no dates, there are no parameters that may get passed to the select - it just IS. It runs on a weekly basis to update a table that we can then use in other reports to provide a more compact way of retrieving the data.The "dynamic" part is that instead of having a huge chunk of code for two columns that get repeated 60 times, I was trying to find a solution that would essentially do a do/while loop - in other words, while i < 61, do this, i+1, repeat.I've re-read the article in question, as well as using Luis' solution as a base, and while the SQL that it generate in the PRINT statement looks correct, it doesn't actually execute correctly, because, as I said, it's unable to find the primary CTE after it's used in the CTE to generate the numbers used later in the script.
Post #1469719

 Permissions