| Author |
Topic |
|
sqlghost
Starting Member
23 Posts |
 Posted - 2006-12-01 : 00:41:59
|
| I have a table with 3 columns===============Col1, Col2, Col3==============r11, r12, r13R21,r22,r23R31,r32,r33 : : (rows) : I want to create a view that looks as follows========================================================Col1_2_3, Col1_2, Col2_3, col1, col2, col3========================================================R11_r12_r13 r11_r12 r12_r13, r11, r12, r13R21_r22_r23 r21_r22 r22_r23, r21, r22, r23 : :The idea being that col_1_2 appends the strings in cols 1 & 2, col2_3 for cols 2 & 3 and so on...How would I do that in a recursive manner so that it works for tables with any number of columns?Can anyone please help? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-12-01 : 08:50:50
|
| Care to share your business case? I can't image why you would want to do this.Jay White |
 |
|
|
sqlghost
Starting Member
23 Posts |
Posted - 2006-12-01 : 13:55:42
|
| Thanks Madhvinan. I will check out your suggested link. But I would like to create a view since that allows me flexibility since the number of cols might change. any ideas along those lines?Jay - unfortunately cannot share the exact business case (company policy), but it is for statistical analytics purposes. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-12-01 : 15:20:40
|
| If you're talking about a single view definition that returns differring number of columns depending on the data in the underlying tables, I don't think you can (or should) do that. If you're talking about a single statement that would generate code for a view definition, which generate a different definition depending on which base tables the generating code was pointing to, that's possible. Ugly but possible.If your objective is something like the latter, there is an article here dealing with dynamic cross tabs which could give you some ideas.Be One with the OptimizerTG |
|
|
|
sqlghost
Starting Member
23 Posts |
Posted - 2006-12-01 : 15:43:23
|
| TG - thankscan you please post the link again? it did not come through... |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
sqlghost
Starting Member
23 Posts |
Posted - 2006-12-01 : 19:11:17
|
| Thanks TG! |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2006-12-01 : 22:17:47
|
| I'd still know the business requirement for doing this... if we knew that, perhaps we could suggest a more effective alternative.By the way, if each column is only 1 character wide, the 8060 limit per returned row will limit you to only 126 columns (worst case = 126+125+124+123...+3+2+1)... it is a binary buildup of columns you are asking for...--Jeff Moden |
|
|
|
sqlghost
Starting Member
23 Posts |
Posted - 2006-12-02 : 04:50:17
|
| Jeff, Like I mentioned earlier I am not at liberty to discuss the business case. But it is for building statistical models. As you may have guessed our table does not just contain (initial) 3 columns, nor does the data look like the way I posted...The data is mostly numeric (sometimes it is varchars)Of course, the solution need not be a view at all. It might very well be query that returns a data in the format I suggested.Thanks for your help! |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2006-12-02 : 11:27:40
|
Ghost,I actually made an error  in calculating the max number of columns you would be able to handle... it's a whole lot less than you'd think...Take your 3 column example... let's just call the columns A, B, C... according to your post (which is actually missing a possibility), you require the following columns...ABC AB AC BC A B C... that's 7 columns or 2^(# of Cols)-1 just to handle the requirements for 3 columns.For 4 columns, the number of output columns essentially doubles (2^(4)-1 or 15 columns)...ABCD ABC ABD ACD BCD AB AC AD BC BD CD A B C D... same for 5 columns (2^(5)-1 or 31 columns)...ABCDE ABCD ABCE ABDE ACDE BCDE ABC ABD ABE ACD ACE ADE BCD BCE BDE CDE AB AC AD AE BC BD BE CD CE DE A B C D E For a table that contains 13 columns, that would be 2^(13)-1 or 8191 columns... that's well over the 8060 character limit even if the columns ONLY contained a single character each.That means, the limits on this task is only 12 columns in the source table... I'm not seeing much utility in this horizontal approach.You can, however, do such a thing in a vertical fashion to expand the limits a bit... but you are limited by the largest integer data type (BigInt or 2^(63)-1) which will only gives you 62 columns. You could try to use FLOAT but that's an approximate datatype with some bit of error.The other thing that you need to consider is (if you are even thinking of using VARCHAR(MAX) in 2005) that 2^(63)-1 (the limits of BigInt) or 62 source columns will generate 9,223,372,036,854,775,807 rows if done in a vertical fashion. I'm thinking that you and your team aren't going to live long enough to generate all those rows. And, that's just for ONE row in the source table of 62 columns!!!!I suggest that pregenerating ALL the data you need is not something that is, well... statistically feasible to do. You need to find another approach. That approach should include calculating only what you absolutely need. I don't believe that the generation of the columns in the fashion you are asking for will solve what you need. I'm sure that one of us on the forum might be able to suggest an alternative, but then again, we don't know what the business requirement is, so probably not. Although I understand the need for proprietary secrecy in these cases, it does make for a bit of a Catch 22...--Jeff Moden |
|
|
|
sqlghost
Starting Member
23 Posts |
Posted - 2006-12-03 : 23:20:20
|
| Jeff,Thanks for your detailed response.However I think you might have misread my post - I actually was not trying to create every combination of base columns. But more like a hierarchical tree (pyramidal) structure. For the bottom nodes: ABC AB BCA B CSome of the questions you raise are valid....but I suspect not insurmountable. |
|
|
|
|
Creating a hierarchical VIEW