| Author |
Topic |
|
eurob
Posting Yak Master
100 Posts |
 Posted - 2006-02-28 : 16:21:02
|
| I have this sql:select client,function,items,errors from mytablethat will show like thisclient1 function1,2,1client2 function2,4,2client1 function2,7,3I would like to have it like this:----------------function1--------------function2 --------------items---errors----------items---errorsclient1---------2-------1---------------7--------3client2---------4-------2---------------0--------0I read the article about how to dynamically create pivot output (http://sqlteam.com/item.asp?ItemID=2955) , but how would I do the subcolumns (items,errors) ?robert |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-28 : 18:10:20
|
| [code]select client, max(case when func = 'function1' then item else 0 end) as item1, max(case when func = 'function1' then error else 0 end) as error1, max(case when func = 'function2' then item else 0 end) as item2, max(case when func = 'function2' then error else 0 end) as error2from mytablegroup by clientResult :client item1 error1 item2 error2 ---------- ----------- ----------- ----------- ----------- client1 2 1 7 3client2 0 0 4 2[/code]----------------------------------'KH' |
 |
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2006-03-01 : 08:09:59
|
| What I need is a column with 2 subcolumns, like in my example.robert |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-01 : 08:11:19
|
quote:
Originally posted by eurob
What I need is a column with 2 subcolumns, like in my example.robert
What do you mean by this ?----------------------------------'KH' |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-01 : 08:16:15
|
quote:
Originally posted by eurob
What I need is a column with 2 subcolumns, like in my example.robert
Where do you want to show the data?Show those columns in your Front End applicationMadhivananFailing to plan is Planning to fail |
|
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2006-03-01 : 08:34:37
|
| I was hoping my first example would clarify. I use this data to populate an excel sheet.So what I want is to regroup a column into two sub columns as in my example, client1 has function1 that consists of 2 items and 1 error.client2 has function1 that consists of 4 items and 2 errors etc.robert |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-01 : 08:40:56
|
| Excel has pivot tables built-in! Use Excel to do this! Just return summarized, grouped data using SQL, and let Excel pivot it. It can create headings, "sub-columns", totals, groups, filters, pages, anything you need. Not only that, but it is easier to write (no complicated T-SQL, not fancy stored procs, no dynamic SQL), shorter, quicker, clearer and so on.use SQL to return the *data*. Use Excel to *format* the data.Makes sense, right? Don't make things more complicated than they need to be. |
|
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2006-03-01 : 08:59:52
|
| I did look into that but I couldn't figure out how to put two sub columns under one column but I agree that formatting shouldn't have to be done in sql. It's just that I would want to present all the data as the user want to see it, without the user having to do any formatting.robert |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-01 : 09:03:48
|
Jeff, I think you have to start new post in your blog that explains where to format data so that we dont need to repeat it again and again and simply point to your blog post  MadhivananFailing to plan is Planning to fail |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-01 : 09:52:38
|
quote:
Originally posted by eurob
I did look into that but I couldn't figure out how to put two sub columns under one column but I agree that formatting shouldn't have to be done in sql. It's just that I would want to present all the data as the user want to see it, without the user having to do any formatting.robert
Drag function to the top, set it as a column.drag client to the left, set it as a row.Drag items and errors to the values part in the middle. Each will get it's own column.You need to play around with it. It's very easy to use, the great part is that end-users can create any totals or subtotals or filters or groups they want just by dragging and dropping. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-01 : 09:53:13
|
quote:
Originally posted by madhivanan
Jeff, I think you have to start new post in your blog that explains where to format data so that we dont need to repeat it again and again and simply point to your blog post MadhivananFailing to plan is Planning to fail
I know. It's one of the ones I've begun to write many times but never quite was able to finish it. Maybe soon! |
|
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2006-03-01 : 10:44:34
|
| When I drag the items and errors to the middle (data area), they are displayed vertically next to client vs horizontally next to eachother under the function column. I found out that in Crystal Reports you have more control over how to display those columns by using the crosstab option and play with the style option under the summarizing fields. I now am able to export it to Excel in the exact format that I wanted.robert |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-01 : 13:15:02
|
| You're welcome. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-02 : 01:56:38
|
| >>I know. It's one of the ones I've begun to write many times but never quite was able to finish it. Maybe soon!I am expecting it MadhivananFailing to plan is Planning to fail |
|
|
|
Julie19
Starting Member
32 Posts |
Posted - 2012-06-13 : 14:52:43
|
| Hello eurob,Did you accomplish ypur requirement?I have a similar requirementbut instead of functions I have datesInstead of items and errors I have Daily and Attain.Please share your knowledge I would really aapreciate itThanks a lot |
|
|
|
|
Pivot question subcolumns