Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    74

    Unanswered: SQL Server - Multiple Rows into Columns

    Hello!

    I am trying to figure out if there is a way to convert (unlimited numberof) rows into columns in SQL Server? Can this be done using a SQL query and without creating any extra tables or procedures?

    Please let me know.

    Thanks much.

    -PV

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm not really sure I understand what you're asking...

    BOL:

    Remarks
    SQL Server can have as many as two billion tables per database and 1,024 columns per table. The number of rows and total size of the table are limited only by the available storage. The maximum number of bytes per row is 8,060. If you create tables with varchar, nvarchar, or varbinary columns in which the total defined width exceeds 8,060 bytes, the table is created, but a warning message appears. Trying to insert more than 8,060 bytes into such a row or to update a row so that its total row size exceeds 8,060 produces an error message and the statement fails.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2002
    Posts
    74
    I have written a perl script that displays the following results:

    line num line value
    1 444
    1 545
    1 878
    2 989
    2 342
    3 213
    3 989
    3 234

    I would it to display like:
    line num line value1 line value2 line value3
    1 444 545 878
    2 989 342
    3 213 989 234

    Here, the number of line values for each line num are not fixed.

  4. #4
    Join Date
    Jun 2011
    Posts
    1

    Question

    Trying to revive an ancient thread here. I could write the above perl script in 10 minutes, but what I want is to do the same in a SQL query. Is this possible?

    In particular I'm using MS Access at the moment.

    I'm pretty sure it can be done but I'm not a SQL guy.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Flattening out the data is typically a job for the display layer. There is the PIVOT clause, but that comes with a number of restrictions, and requires a little bit of hard-coding of column names.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Actually, I have been able to do this without hard-coding column names. I've run my data into a temporary table so that I can dynamically create a string with the list of column names. Then I have used that string in the specification of the PIVOT query. So I am able to get a flexible functionality without hard coding.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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