Printable View
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
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
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!
You can't do this in a view. Try user defined function.
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
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