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

Dynamic Vertical Rows to Horizontal Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 4:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 8:54 AM
Points: 8, Visits: 35
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?
Post #1468734
Posted Friday, June 28, 2013 4:44 PM


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 @ 9:38 AM
Points: 3,627, Visits: 8,134
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1468737
Posted Monday, July 1, 2013 12:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 8:54 AM
Points: 8, Visits: 35
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


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 @ 9:38 AM
Points: 3,627, Visits: 8,134
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)




Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1469207
Posted Monday, July 1, 2013 4:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 8:54 AM
Points: 8, Visits: 35
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


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 @ 9:38 AM
Points: 3,627, Visits: 8,134
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1469237
Posted Monday, July 1, 2013 4:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1469238
Posted Tuesday, July 2, 2013 12:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 8:54 AM
Points: 8, Visits: 35
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse