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!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|||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|||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:CREATE FUNCTION dbo.tableDef(@.name AS VARCHAR(50) RETURNS VARCHAR(8000) AS BEGIN
DECLARE
@.c VARCHAR(5000)
, @.r VARCHAR(8000)

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

OPEN z1
FETCH z1 INTO @.c

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

CLOSE z1
DEALLOCATE z1

RETURN @.r
END-PatP|||Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP|||Originally posted by Pat Phelan
Oh yeah, usage would help, wouldn't it ? Sorry!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.