Results 1 to 7 of 7

Thread: Concatenating String Column

  1. #1
    Join Date
    Aug 2003
    Posts
    15

    Question Concatenating String Column

    Hi All,

    I am trying to write a select statement which will concatenate all values of a string column and provide me with a result set containing just one row of data containing a concatenation of all values.

    For eg:

    column1
    abc
    def
    hij
    klm
    nop

    is it possible to write a select statement which would return

    result
    abcdefghijklmnop

    as a result?

    TIA
    Ketan

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    create table table1 (column1 varchar(10))
    Go
    insert into table1 select 'abc'
    insert into table1 select 'def'
    insert into table1 select 'hij'
    insert into table1 select 'klm'
    insert into table1 select 'nop'
    Go

    declare @x varchar(8000)
    set @x =''
    select @x=@x+column1 from table1
    print @x

  3. #3
    Join Date
    Aug 2003
    Posts
    15

    Wont work

    The purpose of the question is to design a view. Sorry, missed out on that info.

    So...

    I need to create a view like..

    Create view contact_eg
    As
    Select ....

    which should select a concatenation of all the column values!

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can't do this in a view. Try user defined function.

  5. #5
    Join Date
    Aug 2003
    Posts
    15
    Thanks for your feedback.

    However, I need to have a view as I need to join this view with other views to achieve the final result.

    An off topic question, can one use a user defined function in a view? if one can then the solution can be achieved

  6. #6
    Join Date
    Aug 2003
    Posts
    15

    got it!

    yep! that was a silly question. one can use user defined functions. so all one needs to do is to create a function which will return the concatenated columns and use that in the select statement

    as in:

    Create View concat_eg
    select user_def_func

  7. #7
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

Posting Permissions

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