Transformation of data into columns

Hi,

I have the data from a flattened spreadsheet in a table in the
following form:

f1                             f2                              f3
period to:	               Scheme1                   Scheme2
31/01/2005                Net                           Gross
28/02/2005                Net                           Gross
31/03/2005                Net                           Gross
30/04/2005                Net                           Gross
31/05/2005                Net                           Gross
30/06/2005                Net                           Gross
31/07/2005                Net                           Gross
31/08/2005                Net                           Gross
30/09/2005                Net                           Gross
31/10/2005                Net                           Gross
30/11/2005                Net                           Gross
31/12/2005                Net                           Gross

f1, f2 and f3 are the column names, the rest is data.
And there are 120 other columns of similar data to columns f2 and f3.
What I want to do is join this table to another table using the scheme
name.
The problem that I have is that in the table above the scheme name is
not the name of the column, it is just part of the data.
And I have no idea up front which scheme each of the 'f' column names
will map to i.e. whether f2 will equal Scheme1 etc.
Is there an idiomatic way of transforming this data into a usable
state?

Many thanks,
Frank.
0
Frank
3/12/2010 12:47:47 PM
sqlserver.programming 1873 articles. 0 followers. Follow

7 Replies
555 Views

Similar Articles

[PageSpeed] 17

hi Frank,

On 12.03.2010 13:47, Frank wrote:
> f1, f2 and f3 are the column names, the rest is data.
So 'period to:', 'Scheme1' and 'Scheme2' is data?

> The problem that I have is that in the table above the scheme name is
> not the name of the column, it is just part of the data.
This is the normal way a JOIN works. So where is the problem?

SELECT *
FROM theAboveTable A
INNER JOIN otherTable B
ON A.f2 = B.schemeField

> And I have no idea up front which scheme each of the 'f' column names
> will map to i.e. whether f2 will equal Scheme1 etc.
What do you mean?

> Is there an idiomatic way of transforming this data into a usable
> state?
Maybe, your explanation is quite mysterious...


mfG
--> stefan <--
0
Stefan
3/12/2010 12:58:40 PM
Hi Stefan,

> Maybe, your explanation is quite mysterious...

Yes, apologies for that. Let me explain further...

Yes, 'period to:', 'Scheme1' and 'Scheme2' is data. 'f1', 'f2', 'f3'
etc are column names.
The problem that I have is that I do not know which column will hold
the value 'Scheme1'.
It could be any one of the columns from f2 through to f120.
And the only reason that I want to get to 'Scheme1' is to find out the
values in the rows underneath it (i.e. Net, Gross and dates etc).

I was hoping that by turning the scheme name into a column (or even a
row under a single column) I could then query it more easily to find
the other values.

Regards,
Frank.
0
Frank
3/12/2010 1:32:11 PM
hi Frank,

On 12.03.2010 14:32, Frank wrote:
> Yes, 'period to:', 'Scheme1' and 'Scheme2' is data. 'f1', 'f2', 'f3'
> etc are column names.
> The problem that I have is that I do not know which column will hold
> the value 'Scheme1'.
If your importing from Excel, use the header option:

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                 'Excel 8.0;Database=C:\yourExcel.xls;HDR=Yes',
                 'SELECT * FROM [yourSheet$]');


mfG
--> stefan <--

0
Stefan
3/12/2010 2:00:28 PM
Hi Stefan,

Unfortunately, it's a multi-sheet file being flattened by a macro to
a .csv file first before being imported using SSIS.
It may be possible to save the header within the macro, but I was
hoping not to have to touch that part of the development.
Once again, thanks for your help.

Regards,
Frank.
0
Frank
3/12/2010 2:53:44 PM
Couple of questions - are you always guaranteed that the first row of the 
spreadsheet will always have f1, f2, f3, ... values in them?  And are you 
guaranteed that the second row of the spreadsheet will always have "period 
to:", Scheme1, Scheme2, etc. values in them?

Assuming the answers to the first two questions above are yes, this can be 
done in pure T-SQL with  a combination of BULK INSERT, FOR XML, dynamic SQL 
and a couple of staging tables.  But T-SQL alone may not be the *best* tool 
for the job.

What tools are you allowed to use for this task?  Is SSIS an option?  Just 
T-SQL?  bcp?  Also how much control do you have over the exported file 
format (the .csv file)?  Could you change it to a tab-delimited file format, 
for instance?  Or are you stuck with .csv?

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

"Frank" <francis.moore@gmail.com> wrote in message 
news:c37f3364-5e59-4452-976a-bbbeb4181ef2@g19g2000yqe.googlegroups.com...
> Hi,
>
> I have the data from a flattened spreadsheet in a table in the
> following form:
>
> f1                             f2                              f3
> period to:                Scheme1                   Scheme2
> 31/01/2005                Net                           Gross
> 28/02/2005                Net                           Gross
> 31/03/2005                Net                           Gross
> 30/04/2005                Net                           Gross
> 31/05/2005                Net                           Gross
> 30/06/2005                Net                           Gross
> 31/07/2005                Net                           Gross
> 31/08/2005                Net                           Gross
> 30/09/2005                Net                           Gross
> 31/10/2005                Net                           Gross
> 30/11/2005                Net                           Gross
> 31/12/2005                Net                           Gross
>
> f1, f2 and f3 are the column names, the rest is data.
> And there are 120 other columns of similar data to columns f2 and f3.
> What I want to do is join this table to another table using the scheme
> name.
> The problem that I have is that in the table above the scheme name is
> not the name of the column, it is just part of the data.
> And I have no idea up front which scheme each of the 'f' column names
> will map to i.e. whether f2 will equal Scheme1 etc.
> Is there an idiomatic way of transforming this data into a usable
> state?
>
> Many thanks,
> Frank. 

0
Michael
3/14/2010 5:21:46 AM
Hi Michael,

Thanks for the response.
I managed to get this sorted late Friday night.
In the end, a colleague of mine showed me how to use a while loop to
traverse through the f3-f120 columns with some dynamic SQL to pull out
the values in each column. I was working along these lines before I
posted the question, but just had some doubts that this was the best
way to go.

Thanks again,
Frank.
0
Frank
3/15/2010 9:15:14 AM
Hi Frank,

I imagine there's better ways to do it, but it's a little hard to say 
without more information.  I suspect pure T-SQL probably isn't the best tool 
for the job in this case, but again not quite enough information to make the 
call.

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

"Frank" <francis.moore@gmail.com> wrote in message 
news:3f1815b7-ee5b-4e40-8376-a15a5a7a434d@q23g2000yqd.googlegroups.com...
> Hi Michael,
>
> Thanks for the response.
> I managed to get this sorted late Friday night.
> In the end, a colleague of mine showed me how to use a while loop to
> traverse through the f3-f120 columns with some dynamic SQL to pull out
> the values in each column. I was working along these lines before I
> posted the question, but just had some doubts that this was the best
> way to go.
>
> Thanks again,
> Frank. 

0
Michael
3/16/2010 1:05:20 AM
Reply:

Similar Artilces:

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

Column Headings #11
Can you add seperate column headings (A, B, C, ...) into one spreadsheet? I'm attempting to alter the column sizes half-way through the spreadsheet w/out affecting the upper column sizes... Coolumn width belongs to the entire column and cannot be altered in separate sections of that column. Gord Dibben Excel MVP On Tue, 8 Mar 2005 15:51:01 -0800, spencer4hire <spencer4hire@discussions.microsoft.com> wrote: >Can you add seperate column headings (A, B, C, ...) into one spreadsheet? >I'm attempting to alter the column sizes half-way through the spreadsheet >w/ou...

WM_QUERYENDSESSION and saving data through a worker thread
Hello I have an application that uses a worker thread to save/load data. I'm wondering what is the best reaction to WM_QUERYENDSESSION in my case. I have to possible scenarios: 1. When WM_QUERYENDSESSION comes fire the thread and wait for it to end. Only then return TRUE from WM_QUERYENDSESSION handler. The problem is that I will get nusty dialog that my application "is not responding". 2. When WM_QUERYENDSESSION comes fire the thread and return FALSE from the handler. When thread is done force application to end. But this way I will probably prevent Windows from closing,...

Copying data from one chart to another
I have many graphs - all plotting on similar scales but using different data. Is there any way I can simply copy one set of data from one graph and paste it into another graph so that I can avoind going through all the hassle plotting each curve again? I want to have graphs showing different combinations of the same data and have hundreds of curves to plot so this could be a huge timesaver... Cheers. -- Alan_Partridge ------------------------------------------------------------------------ Alan_Partridge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29295 V...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

Excel 2003 Copy/Paste filtered column
I have a filtered column on my spreadsheet. I have copied the column, changed the figures and then tried to paste it back on to the filtered column. It is not copying over the original filtered column but rather over cells that have been filtered out. The worksheet/cells are not protected. What could the problem be? Kind Regards Heather That's the way pasting works. It'll hit the visible and hidden cells. Heather wrote: > > I have a filtered column on my spreadsheet. I have copied the column, > changed the figures and then tried to paste it back on to the filter...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

how to automatically suppress space before after column break?
Having Spacing Before and After on some of the styles, I seem to be unable to have the space before at the beginning of a column automatically dismissed when applying a column break. I have tried a couple of options under compatibility, but to no avail. This in on Word 2003. The No HTML function + No Space Before after column break do not solve the problem. Can you help please? Tools | Options | Compatibility: Suppress Space Before after a hard page or column break. If this isn't working, then check to make sure you don't have an empty paragraph before the first text pa...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Add data to cell w/o loosing initial data
I would like to know if there is a way to add data to data without retyping. For example I have a colum of 18015555555 and I want to add [rfax:(cell #)@/fn=(phone number)] So I would like to add the brackets - copy from a cell - @/fn= and not loose the data already in the spread sheet. Example 2. Add [rfax:company name@fn/=(saved data here) then close bracket. So I want to add data to cells without loosing the data already in the cells. I have about 600 of them to do and I really don't want to do each one by hand. Please let me know if anyone knows how to accomplish this. Tha...

Removing filters from data
Hi. I have recorded a macro to remove filters from data lasts in advance of performing other actions. However if the data is unfiltered the macro falls over with the message Run time error '1004' ShowAllData method of Worksheet class failed. I think I need some sort of if error continue code or something to check filtering first. I would be grateful if someone could point me in the right direction please. Hi Philip Try If Activesheet.Filtermode Then ActiveSheet.ShowAllData -- Jacob "Philip J Smith" wrote: > Hi. > > I have re...

Looking up and matching data
I have two sets of data with the same information but not in the same order and am trying to match the data. In each data set I have 10 pools containing 100 loans. Each pool has a unique ID and each loan within the applicable pool has an ID of 1 to 100. I need to look up the Pool ID, then look up the loan ID so that I can extract the property type information from a third column. The Pool ID and property type is text but the loan ID is a number. I am struggling to put together the right combination of formulas to give the property type for each loan within each pool. Any suggestion...

Selecting a column with an integer
Sub ColumnSelection() ' Selecting a column with an integer ' Please show me how to eliminate the use of Cells(1, 1) Dim r As Integer Dim c As String Dim numericcolumn As Integer Dim alphabetcolumn As String numericcolumn = 4 ' in practice 4 is the resultant of an equation alphabetcolumn = "=CHAR(" & numericcolumn + 64 & ")" Cells(1, 1) = alphabetcolumn ' I like to eliminate the use of Cells(1, 1) c = Cells(1, 1).Value ' I like to eliminate the use of Cells(1, 1) Cell...

invisible listbox data
I have developed software in Acces XP that is distributed to two different locations and I have noticed some odd behaviour at one of the locations. Sometimes the data in listboxes or combo boxes is invisible. The data IS PRESENT because you can select and use the records as before (although you can't see which ones you're selecting). The combo boxes are poplulated and have the correct dropdown length for the records one would expect. The listboxes have scroll bars where one would expect a scrollbar and multiselcetion is possible where apropriate. I have played around with th...

Moving certain data to different sheet
I need to move data that meets a certain criteria, to another sheet within a workbook. For instance, if a column of data is for a certain ZIP code area, I need it to automatically copy to a sheet for that city. Say, 40202 would go to the Louisville, KY sheet. Because Louisville has multiple ZIPs, I would need only the data that begins with 402 to go to that sheet. Lexington KY's data, which begins with ZIP code 405, would go to its own sheet. Macro? Formula? Thanks! This can definitely not be created with a formula. I suggest that you make use of the macros. Rgrds, Kris...

BP Req Mgmt Lookup should show additional columns
When doing a lookup I should be able to configure the columns that I would like to see visible on the lookup. For example, when looking up an item only item number and item description are visible fields. I would like to configure the lookup to show additional fields, like the vendor name. ...

Prevent copy and paste in one column
I am having trouble trying to prevent copying and pasting in one specific column. The code refers to the specific range, but yet it prevents copying and pasting on the whole worksheet. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Columns("H:H")) Is Nothing Then Application.CellDragAndDrop = False Application.CutCopyMode = False Else Application.CellDragAndDrop = True End If End Sub Works for me in Excel 2003 Gord Dibben MS Excel MVP On Fri, 30 Apr 2010 11:42:01...