| Author |
Topic |
|
eurob
Posting Yak Master
100 Posts |
 Posted - 2005-07-20 : 16:55:21
|
| My table contains a client id and a type field that can contain various values. Is there a way to span the values in the type field horizontally, instead of vertically ?select select clientid,typeid from mytable group by clientid,calltypeid order by clientid results in this:client**type--------------10.......A110.......A2I'd like to have it like this:Client**type1*type2-----------------------10.........A1.....A2robert |
|
|
mk_garg20
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-07-20 : 18:43:02
|
| if you get results like thisclient type10 A1,A2,A3will it solve your purpose?mk_garg |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-21 : 01:44:03
|
| If possible do this in your presentation layerMadhivananFailing to plan is Planning to fail |
|
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2005-07-21 : 08:17:36
|
quote:
Originally posted by mk_garg20
if you get results like thisclient type10 A1,A2,A3will it solve your purpose?mk_garg
Yes, that would work, so it would be like10 A1,A2,A320 B5,B7,B930 etc....It would be nice if the comma could be left out.I hope it is possible.Thanksrobert |
|
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2005-07-21 : 08:22:10
|
quote:
Originally posted by madhivanan
If possible do this in your presentation layerMadhivananFailing to plan is Planning to fail
Could you give a small example of how that would work ?Thank you.robert |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2005-07-21 : 09:42:16
|
| I tried that, it's a nice method, but it is way too slow. I also realize that I have given incorrect explanation. I need this :clientid.......type1......type2......type310 120 500 3320 18 20 37So the cells are the sum per type per client.robert |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-07-21 : 09:57:40
|
| how are you presenting this information? Using what client application?- Jeff |
|
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2005-07-21 : 10:02:26
|
quote:
Originally posted by jsmith8858
how are you presenting this information? Using what client application?- Jeff
I'm gonna use it in crystal reports, I would like to use a stored procedure. But still I would just like to see it in the sql query output window.robert |
|
|
|
eurob
Posting Yak Master
100 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-07-21 : 10:45:28
|
| How many types are possible? How do you decide which is "type 1" versus "type 2" and so on?The easiest way to do this to let SQL return the raw data in the its standard row/column format and then let Crystal do all formatting.For example, on your crystal report you might:1) create a group for "Client"2) Determine how many columns you can possibly have (this must be fixed, of course, since you are formatting for a page). Call this "n"3) Create a formula that executes for each row in the detail section and assigns the current Type to Type1, Type2, ... TypeN shared variables (or perhaps an array?).4) Display the contents of the Type1, Type2, ... TypeN variables in different text boxes at the client footer.5) Suppress the detail section and also the client header section. (since you only want 1 line per client). So you'd only display the fully formed client footer.You must have some knowledge of crystal reports formulas and using shared variables and such, but it is pretty easy to do once you mess around with it. And it will be much more efficient than forcing SQL Server to do the work. - Jeff |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-07-21 : 10:46:38
|
| Update -- if you just need a standard cross tab (and not assining Type1, Type2, .. etc... ) then Crystal has this feature built-in. You don't need to do anything in SQL. Just add a cross tab to your report, assign the columns, and off you go.- Jeff |
|
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2005-07-21 : 11:31:15
|
| Thanks a lot for the input. I'll give it a shot just using crystal.robert |
|
|
|
mk_garg20
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-07-21 : 18:39:18
|
| it will work in crystal reports.cheersmk_garg |
|
|
|
|
Horizontal display of column results