Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    15

    Unanswered: 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 chars

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: grouping and concatenating

    Originally 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

  3. #3
    Join Date
    Jan 2003
    Posts
    15
    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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, SQL Server can't do what you want. According to SQL Maximum Capacity Specifications, 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

  5. #5
    Join Date
    Jan 2003
    Posts
    15
    Ok Pat thank you
    i will just go for the c# solution

Posting Permissions

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