April 8, 2010 at 10:21 am
I have written an SQL that outputs a whole lot of data into a rather unwieldy format. As it stands now, there are 33 fields that stretch their way across an Excel worksheet once they are exported.
The output looks something like this:
|Part|Description|Branch1 Vendor|...|Branch15 Vendor|Branch1 Cost|...|Branch 15 Cost|
Ideally, I would like the output to look like this:
|Part|Description|Branch1 Vendor|...|Branch15 Vendor|
|Branch1 Cost |...|Branch 15 Cost |
I am not even sure if this is possible, but any help that could be offered would be greatly appreciated!!!:w00t::w00t::w00t:
April 8, 2010 at 11:41 am
Please provide some more information.
What would be great would be some sort of ready to use sample data as described in the first link in my signature.
In your case it would be important to know the rules where to find the split point in a row.
April 8, 2010 at 1:07 pm
Excel has much better formatting capabilities than SQL Server, so you should probably be looking at this as an Excel reporting problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 8, 2010 at 1:08 pm
okay. let me start by saying I am just mining data out of a warehouse full of servers, and do not have the access to create tables, only merge them, so this is a first for me. please be kind :ermm:
create table #mytable
(
prod char primary key,
group text,
description text,
cost, decimal,
supply char,
kbid char,
plan char,
buy char,
facility char
)
insert into #mytable
(prod, group, description, cost, supply, kbid, plan, buy, facility)
select 'chk1', 'v100', 'check valve', 0.995, 'A123', 'A', '1', '2', 'CH' union all
select 'bfv1', 'a200', 'butterfly valve', 2.535, 'B456', 'C', '4', 'a', 'MD' union all
select 'mvr7', 't400', 'mover', 1.759, 'C890', 'B', '5', 'X', 'NE' union all
select 'chk1', 'v100', 'check valve', 0.995, 'AB125', 'C', '1', '1', 'MD' union all
select 'bfv1', 'a200', 'butterfly valve', 2.535, 'J157', 'A', '4', '7', 'NE' union all
select 'mvr7', 't400', 'mover', 1.759, 'C890', 'B', '5', 'X', 'CH' union all
select 'chk1', 'v100', 'check valve', 0.995, 'AB125', 'C', '1', '1', 'F1' union all
select 'bfv1', 'a200', 'butterfly valve', 2.535, 'J157', 'A', '4', '7', 'F1' union all
select 'mvr7', 't400', 'mover', 1.759, 'C890', 'B', '5', 'X', 'F1' union all
THEN... I want the selects to look something like this
select
prod as Product,
group as Group,
description as "Desc",
cost as "Cst",
(select supply from mytable where facility = 'CH') as "CH Supply",
(select supply from mytable where facility = 'MD') as "MD Supply",
(select supply from mytable where facility = 'NE') as "NE Supply",
(select kbid from mytable where facility = 'CH') as "CH KBID",
(select kbid from mytable where facility = 'MD') as "MD KBID",
(select kbid from mytable where facility = 'NE') as "NE KBID"
where
facility = 'F1'
order by
prod
As you can imagine, if data is loaded for 22,000 partcodes and 15 facilities, this table get very large, very quick and you end up with a table that is 34 columns wide, making it very difficult to use. I don't really care if the information for the part, description, group, and cost show up twice, if that is what it takes to get what I am looking for, but I would like to break the single row of data up so that instead of getting a single row returned that has product, group, description, cost, CH supply, MD supply, NE supply, CH KBID, MD KBID, NE KBID, I get 2 rows where the first row has product, group, description, cost, CH supply, MD supply, NE supply and the second row has product, group, description, cost, CH KBID, MD KBID, NE KBID. This is probably ultra confusing, and if that is the case, please let me know. I will be more than happy to provide the original code if it will help to fully explain the mess that I am dealing with.
April 8, 2010 at 1:19 pm
There's a lot of options and different ways to handle this in Excel, or in conjunction with both Excel and SQL Server. For instance many folks find that using SQL Server to make an Excel PivotTable works better for data mining.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 8, 2010 at 1:23 pm
hmink 90250 (4/8/2010)
...As you can imagine, if data is loaded for 22,000 partcodes and 15 facilities, this table get very large, very quick and you end up with a table that is 34 columns wide, making it very difficult to use. I don't really care if the information for the part, description, group, and cost show up twice, if that is what it takes to get what I am looking for, but I would like to break the single row of data up so that instead of getting a single row returned that has product, group, description, cost, CH supply, MD supply, NE supply, CH KBID, MD KBID, NE KBID, I get 2 rows where the first row has product, group, description, cost, CH supply, MD supply, NE supply and the second row has product, group, description, cost, CH KBID, MD KBID, NE KBID. This is probably ultra confusing, and if that is the case, please let me know. I will be more than happy to provide the original code if it will help to fully explain the mess that I am dealing with.
Actually, I think that I know exactly what you are getting at and here's the deal: It's messy to do with SQL Server and hard to maintain if you need changes. But worse, you will end up with Excel sheets that are an order or magnitude harder to do data analysis on.
Seriously, the path you're on is to work very hard to create something that will make your work even harder to do. Excel has a lot of other, better ways to address this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 8, 2010 at 1:26 pm
I was unaware that you could get a pivot table to return text instead of numeric data... if that is possible, then that would be easy, and I wouldn't have to rewrite any of my original code. I guess maybe I should explain why I want this data.. In our system, there are 15 facilities drawing various products from a myriad of supply points. In the past 10 years that this system has been in place, far too many people have had their hands in the very critical planning records, leaving a lot of inconsistent data behind. I am just trying to line the data up in a way that makes it easy for the current set of planners to check their part codes and make sure the facilities are pulling product correctly.
April 8, 2010 at 1:42 pm
hmink 90250 (4/8/2010)
I was unaware that you could get a pivot table to return text instead of numeric data... if that is possible, then that would be easy, and I wouldn't have to rewrite any of my original code. I guess maybe I should explain why I want this data.. In our system, there are 15 facilities drawing various products from a myriad of supply points. In the past 10 years that this system has been in place, far too many people have had their hands in the very critical planning records, leaving a lot of inconsistent data behind. I am just trying to line the data up in a way that makes it easy for the current set of planners to check their part codes and make sure the facilities are pulling product correctly.
Actually, I'll have to check on that myself. What version of Excel are you using?
Even aside from PivotTables, let me list some of the other Excel options available:
0) Wide Screen monitor & Zoom Out: Obvious, but people often overlook it.
1) Freeze Panes: Arrange your columns in the correct order then freeze your column headers to the top rows and your row's identifying columns to you left side. Then you can scroll right and left to control which columns you see.
2) Excel Views/windows: Many do not realize it, but you can make multiple Views, each in their own excel window, of the same worksheet. Column visibility is a View/Window property, so you can easily customize each differently.
3) Outlining: The coolest feature that most Excel user don't know how to use. Outlining allows you to collapse/expand and summarize groups of Rows and/or Columns.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 9, 2010 at 5:19 am
I am using Excel 2007 for its data capacity, but most of the people that will be reviewing the data have anything running the gamut from 1997 - 2003. Apparently, they don't like to upgrade software at this company.
April 14, 2010 at 9:23 am
I meant to post this earlier...
I did manage to find a rather crude, but effective way to do what I wanted using Excel by inserting 2 columns and using the Fill, Series option. Like I said, crude, but effective.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply