Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2008
    Posts
    49

    Unanswered: columnar to row-based data

    I'm currently working on a text file that I will load in my query. The data of the text file is like a columnar style and I must arrange them in the usual row-style.

    example: contents of the textfile

    CARPETS
    PLAZA CARPET
    323-931-9999
    PAYMENT METHODS
    VISA

    And I'll must arrange them to row-based table

    field1 | field2 | field3 | field4 | field5
    CARPET | PLAZA CARPET | 323-931-9999 | PAYMENT METHODS | VISA


    Is this possible with MS SQL Server's query?

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    ur storing the data as
    CARPETS
    PLAZA CARPET
    323-931-9999
    PAYMENT METHODS
    VISA
    like this in a table in ms sql server.

  3. #3
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by bklr
    ur storing the data as
    CARPETS
    PLAZA CARPET
    323-931-9999
    PAYMENT METHODS
    VISA
    like this in a table in ms sql server.
    Yeah, and I would like to fix them in a row. Is that possible?

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    u can convert column values in to rows by using pivot function in 2005 and above
    and in 2000 by dyanamic cross tab
    see this link it may useful to u
    Dynamic Crosstab with multiple PIVOT Columns - Madhivanan

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Depending on the size, complexity, and other factors you may want to do this pivot before you import the file into SQL Server. Without more information about what you're doing, I'd recommend using gawk.exe and tab characters as column delimiters and newlines as row delimiters.

    -PatP

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How do you know when one record ends?
    Maybe you can get away with a field delimiter of a carriage return and a record terminator of two carriage returns or crlf, etc....

    Can you provide more than one sample row - upload it in an actual text file if that's easier
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2003
    Posts
    33
    i found 2 ways to do this, one more efficient than the other:

    http://www.dbforums.com/microsoft-sq...ml#post6410712
    Last edited by mozkill; 07-14-09 at 14:58.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mozkill, nice of you to publish your findings on an unrelated problem, but how would you apply ~either~ of your techniques to this problem please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2003
    Posts
    33
    no problem. first, you convert the text file in the original post like so (using grep or DOS find/replace) :

    Code:
     
    1,what,CARPETS
    1,loc,PLAZA CARPET
    1,phone,323-931-9999
    1,method,PAYMENT METHODS
    1,card,VISA
    2,what,CARPETS
    2,loc,PLAZA CARPET
    2,phone,323-931-9999
    2,method,PAYMENT METHODS
    2,card,VISA
    Then you can import that into a database table (or use a text driver), and then use the method I described in my thread to PIVOT the table.

    Doing it on the fly in pure SQL would probably require a stored procedure I think. Sorry for re-awakening an old thread without giving a full answer.
    Last edited by mozkill; 07-14-09 at 16:52.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Show me a sample file that contains 3 to 5 rows worth of OUTPUT data. I have an idea for a simpler solution, but I need some test data to prove it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Better yet, for the following data:
    Code:
    CARPETS
    PLAZA CARPET
    323-931-9999
    PAYMENT METHODS
    VISA
    FURNITURE
    FRED'S FURNITURE FARM
    222-222-2222
    PAYMENT METHODS
    MASTERCARD
    ELECTRONICS
    ED'S ECLECTIC
    333-333-3333
    PAYMENT METHODS
    CASH
    I would use the following gawk script:
    Code:
    		{ ar[NR%5] = $0 }
    0 == NR%5	{ printf("%s\t%s\t%s\t%s\t%s\n" \
    ,		ar[1], ar[2], ar[3], ar[4], ar[0]) }
    ...to produce:
    Code:
    CARPETS	PLAZA CARPET	323-931-9999	PAYMENT METHODS	VISA
    FURNITURE	FRED'S FURNITURE FARM	222-222-2222	PAYMENT METHODS	MASTERCARD
    ELECTRONICS	ED'S ECLECTIC	333-333-3333	PAYMENT METHODS	CASH
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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