Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Vertical Rows to Horizontal


Dynamic Vertical Rows to Horizontal

Author
Message
N.B.
N.B.
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 127
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?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18099
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
N.B.
N.B.
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 127
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.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18099
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.
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
N.B.
N.B.
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 127
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.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18099
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44990 Visits: 39876
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
N.B.
N.B.
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 127
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search