Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2007
    Posts
    6

    Unanswered: Combining multiple rows into one

    I have data that looks like this:

    ID Value
    1 Descr1
    1 Descr2
    1 Descr3

    where Descr could range from 1 to 100 for each ID

    The result set I need is:
    Descr1,Descr2,Desc3...etc.
    Does someone have a query to do this?
    Thank you

  2. #2
    Join Date
    Oct 2006
    Posts
    115
    select case when Descr1=... end as Descr1, case when Descr2=... end as Descr2, case when Descr3=... end as Descr3 from <your table name>

    my first contribution here, quite similar to what I was trying to do in a project, do correct me if it's wrong.

  3. #3
    Join Date
    Feb 2007
    Posts
    6
    I will not be able to use CASE since the values of Descr1 etc. are always different

  4. #4
    Join Date
    Feb 2007
    Posts
    6
    What i mean is that the values in Value column are always different and are unknown. Therefore i will not be able to use CASE

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    this is pretty much what adam is doing, but it's more compact and mysterious the first time you see it:

    http://sqlblindman.googlepages.com/c...limitedstrings

    plus it's from a regular here.

  7. #7
    Join Date
    Feb 2007
    Posts
    6
    Thank you.

    Basically I came up with the following:

    DROP FUNCTION dbo.ConcatDescr
    go

    CREATE FUNCTION dbo.ConcatDescr(@TXRCODE CHAR(8))
    RETURNS VARCHAR(300)
    AS
    BEGIN
    DECLARE @Output VARCHAR(300)
    SET @Output = ''
    SELECT @Output = CASE @Output
    WHEN '' THEN MON_TEXT
    ELSE @Output + ', ' + MON_TEXT
    END
    FROM PCLONG
    WHERE TXRCODE = @TXRCODE
    order by MON_PCH

    RETURN @Output
    END
    GO

    SELECT TXRCODE, dbo.ConcatDescr(TXRCODE)
    FROM PCLONG
    WHERE TXRCODE = '01100008'

    The code above works to concatenate lines into one however it truncates data after 256 characters. I looked in help and it says that varchar can be up to 8000 chars. Is there something I am doing wrong?

    Thank you again.

  8. #8
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    it is because of the length of the variable where u r putting the data....increase it and the Return as well

    RETURNS VARCHAR(300)
    .......
    DECLARE @Output VARCHAR(300)

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your data may also be getting truncated by Query Analyzer. Check the QA options and bump up the maximum character output enough to display your results.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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