Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: how to concat/pivot rows to column?

    hello,

    I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column.
    For example:
    select letter from alphabet_table
    a
    b
    c
    d
    e
    ...
    26 rows returned.

    Other than a cursor, how would I write a query to return the following:
    row1: abcdefghijkl...

    thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    Originally posted by Pat Phelan
    There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

    -PatP

    PatP, thanks for your reply. After posting I realize I should have included more information.
    Here's more specifics:
    CREATE TABLE [elements] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [name] [varchar] (50) NOT NULL ,
    [description] [varchar] (50) NULL ,
    [code] [varchar] (5000) NOT NULL ,
    [ord] [int] NOT NULL
    ) ON [PRIMARY]
    GO

    elements.code contains html tags, such as table, tr, td. I am using a stored procedure to build html code based on an input parameter. The parameter matches the 'name' column.
    so to build a table, i would select the code and order by the ord column. the result is similar to the following:
    <table width="100%" border="0">
    <tr>
    <td>
    </td>
    <td>
    </td>
    <td>
    </td>
    </tr>
    </table>
    (10 rows).
    I would like to query the table based on the parameter passed to return the same results, except in one record:
    <table width="100%" border="0"><tr><td></td><td></td><td></td></tr></table>
    (1 row).

    hope this helps clear it up

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That helps a bunch. The biggest problem that I see is that you can't allow your html table definition to exceed 4000 characters if you use 16 bit characters (aka UTF-8), or 8000 characters if you use 8 bit (OEM) characters. This could be a real problem for complex pages.

    With that said, I'd start with:
    PHP Code:
    CREATE FUNCTION dbo.tableDef(@name AS VARCHAR(50RETURNS VARCHAR(8000) AS BEGIN
       
    DECLARE
          @
    c VARCHAR(5000)
    ,     @
    r VARCHAR(8000)

       
    SELECT @''
       
    DECLARE z1 CURSOR FOR SELECT [code]
          
    FROM [elements]
          
    WHERE  name = @name
          ORDER BY ord

       OPEN z1
       FETCH z1 INTO 
    @c

       
    WHILE = @@fetch_status
          BEGIN
             SET 
    @= @+ @c
             FETCH z1 INTO 
    @c
          END

       CLOSE z1
       DEALLOCATE z1

       
    RETURN @r
    END 
    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh yeah, usage would help, wouldn't it ??? Sorry!
    PHP Code:
    SELECT [name], dbo.tableDef([name])
       
    FROM [elements]
       
    GROUP BY [name
    -PatP

  6. #6
    Join Date
    Apr 2004
    Posts
    3
    Originally posted by Pat Phelan
    Oh yeah, usage would help, wouldn't it ??? Sorry!
    PHP Code:
    SELECT [name], dbo.tableDef([name])
       
    FROM [elements]
       
    GROUP BY [name
    -PatP

    many thanks, Pat. i was hoping there was a 'simpler' method of reaching this goal. sometimes i wish i could rewrite ms's implementation of the ansi select to include special tricks.
    like: select + * from blah would concat results.

    i'll let you know how it works, i'm not too worried about the 4/8k character limit, i can always have a couple of columns.

    thanks again.

Posting Permissions

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