June 28, 2013 at 4:38 pm
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 question
Starting Point: Vertical to Horizontal
My 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 BN3
FROM TMP_URE
CROSS APPLY (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) X(N)
WHERE RN=N
GROUP BY MK, HK, CK
ORDER 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?
July 1, 2013 at 12:34 pm
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[/url] 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.
July 1, 2013 at 1:45 pm
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 Numbers
ORDER BY RN
SET @SQL1 = 'SELECT MK
,HK
,CK'
SET @SQL3 = '
FROM TMP_URE
GROUP BY MK, HK, CK
ORDER BY MK, HK, CK'
PRINT @SQL1 + @SQL2 + @SQL3
EXEC( @SQL1 + @SQL2 + @SQL3)
July 1, 2013 at 4:14 pm
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.
July 1, 2013 at 4:50 pm
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.
July 1, 2013 at 4:58 pm
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
Change is inevitable... Change for the better is not.
July 2, 2013 at 12:56 pm
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.
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply