grouping and concatenating

i have a the following table:

CREATE TABLE [dbo].[tIndex] (
[indexID] [int] IDENTITY (1, 1) NOT NULL ,
[wordID] [int] NULL ,
[wordPos] [int] NULL ,
[paraID] [int] NULL
) ON [PRIMARY]
GO

for each wordID i have many paraID and for each wordID,paraID i have many wordPos

i will use the following convention:
paraID=p
wordID=w
wordPos=wp

i want to concatenate the columns to get the following format:

row1: w1 p1,NB1,wp1,wp2,w3... | p2,NB2,wp4,wp5,wp6... | ...
row2: w2 ...
row3: w3 ...

where NB1 is the number of wp having w1 and p1

Note: the length of a row may exceed 8000 charsOriginally posted by samham
Note: the length of a row may exceed 8000 chars If any column exceeds 8000 characters (which is what I think you are trying to say), then you have no choice... You must build those columns on the client.

-PatP|||the total rows of the table is 15 million

i am trying to copy the table content to a text file in the format i described

I put the note about the 8000 chars to say that 1 row cannot be contained in a varchar(8000) variable in case concatenating using a varchar(8000) is a solution

i am using c# as my programming language so my last option is to do this by c# code by selecting wordID and then for each rowID select the paraID and then for each wordID,paraID select the wordPos

but i was wondering if this can be done by sql and then send the result directly to a textfile|||Sorry, SQL Server can't do what you want. According to SQL Maximum Capacity Specifications (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp), the maximum row size for SQL Server is 8060 bytes. The maximum single string is 8000 bytes.

In your case, your last resort is the only one that might work.

-PatP|||Ok Pat thank you
i will just go for the c# solutionsql