Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    3

    Unanswered: Can you identify this data type?

    Can anyone tell me what one calls the following form of data transfer and where one looks to convert it to a column based format.

    The first four digits describe a parameter, the following digits are it's value.
    It looks like this:
    &&
    0140122 <0140="Gas", 122=how much>
    01393655 <0139="Depth", 3655= how deep>
    01383640 <0138="Bit Depth", 3640= how deep>
    !!
    I need this to look like this

    Gas Depth Bit Depth
    122 3655 3640

    I will later need to send data out in the previous form.

    Thanks in advance for any insights you may share

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is generally called pivoting

    i don't think microsoft access can do this with its pivot query facility

    will there only ever be those three particular columns? gas, depth, bit depth?

    or are there other codes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    3
    yes, there will be about 20 parameters or so(that we will use, there are overa hundred we will not use), some from a provider via tcp or serial port, a few we will generate and add to the "stream". there are some vendors willing to sell a solution, but I need to learn to handle all aspects of this project.
    I've seen "pivot table". Is that what will "strip off" and decode the 4 digit pre-fix, leaving the values?
    Can you advise where one can learn this sort of file transfer?
    You may need to know that these datum are real time from sensors and that I intend to "filter" to a "by the foot" file and a "by the minute" .

    I feel if I am simply nudged in the right direction I can learn enough and perhaps not pester the pros so much.

    Thanks Richard

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    it is generally called pivoting

    i don't think microsoft access can do this with its pivot query facility
    Might you be able to if you can include the MID() function in the join condition to a lookup table Rudy?

    Also to OP - assuming you can change this, you would be well advised to not store your data like this.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I don't think this is a standard format that can easily be converted. Before I feel comfortable making any suggestions, I have a couple of questions.

    1. The 'parameters' and their values, do these relate to anything? Are the number of parameters fixed or variable. Fixed meaning, every object has a Gas, Depth and Bit Depth setting. Variable meaning, one object has a gas setting, another has gas and depth, and a third may have all three.

    2. Is the data in a file? Are the && and !! header and trailer values?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    hi wellsightdata

    Re your PM - to clarify my second point - your data does not currently comply with first normal form i.e. the data in each column is not atomic *. If this is not familiar to you then please read this excellent link that covers the fundamentals of relational database design, paying particular attention to the section on normalisation:
    http://www.tonymarston.net/php-mysql...se-design.html

    * I've just realised that the article doesn't I think, mention atomicity. The point is that each item of data should be about one thing and one thing only. A very simple, snappy phrase that covers this is "one fact in one place". I like this phrase so much I use a variation of it for my location above. You have two facts - the parameter and the value - in one column. These should be in two columns. Everything from then on is very simple indeed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •