I would like the resultant column to be comma separated. Here is sample ddl
set nocount on
go
create table z_my_tbl_del
( i int,
n char(8)
)
go
insert z_my_tbl_del values(1,'john')
insert z_my_tbl_del values(2,'mary')
insert z_my_tbl_del values(3,'luke')
insert z_my_tbl_del values(4,'trisha')
insert z_my_tbl_del values(5,'sam')
go
select * from z_my_tbl_del
go
drop table z_my_tbl_del
go
The resultant column should be all in one line as as opposed to separate
lines.
n
======
john,mary,luke,trisha,samThe right way is to do display formatting in the front end. But if you
like doing things wrong and destroying 1NF, use a cursor. Someone else
will show you a proprietary way of doing .|||See if this helps:
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"sqlster" wrote:
> I would like the resultant column to be comma separated. Here is sample dd
l
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'john')
> insert z_my_tbl_del values(2,'mary')
> insert z_my_tbl_del values(3,'luke')
> insert z_my_tbl_del values(4,'trisha')
> insert z_my_tbl_del values(5,'sam')
> go
> select * from z_my_tbl_del
> go
> drop table z_my_tbl_del
> go
> The resultant column should be all in one line as as opposed to separate
> lines.
> n
> ======
> john,mary,luke,trisha,sam|||Somehow I get the feeling that comma-separated values are really *in* this
year.
These things do not belog on the data layer!
But, that's fashion - useless but neat...
Look at this:
http://milambda.blogspot.com/2005/0...s-as-array.html
And what's bad about it? One of the things is the mangled informational
value - the actual relationship between the values 'john', 'mary', 'luke',
'trisha' and 'sam' gets distorted. Are these values cummulative or
alternative? What do they have in common? Did they have anything in common
before? Can they have anything in common ever again?
Anyway, use with care...
ML|||i suggest the following :
SELECT TOP 1 z_my_tbl_del_1.n + ',' + z_my_tbl_del_2.n + ',' +
z_my_tbl_del_3.n + ',' + z_my_tbl_del_4.n + ',' + z_my_tbl_del_5.n AS nn
FROM z_my_tbl_del z_my_tbl_del_1 INNER JOIN
z_my_tbl_del z_my_tbl_del_2 ON z_my_tbl_del_1.i <>
z_my_tbl_del_2.i INNER JOIN
z_my_tbl_del z_my_tbl_del_3 ON z_my_tbl_del_2.i <>
z_my_tbl_del_3.i AND z_my_tbl_del_1.i <> z_my_tbl_del_3.i INNER JOIN
z_my_tbl_del z_my_tbl_del_4 ON z_my_tbl_del_3.i <>
z_my_tbl_del_4.i AND z_my_tbl_del_1.i <> z_my_tbl_del_4.i AND
z_my_tbl_del_2.i <> z_my_tbl_del_4.i INNER JOIN
z_my_tbl_del z_my_tbl_del_5 ON z_my_tbl_del_1.i <>
z_my_tbl_del_5.i AND z_my_tbl_del_2.i <> z_my_tbl_del_5.i AND
z_my_tbl_del_3.i <> z_my_tbl_del_5.i AND
z_my_tbl_del_4.i <> z_my_tbl_del_5.i
Try it!!!
mario
"sqlster" wrote:
> I would like the resultant column to be comma separated. Here is sample dd
l
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'john')
> insert z_my_tbl_del values(2,'mary')
> insert z_my_tbl_del values(3,'luke')
> insert z_my_tbl_del values(4,'trisha')
> insert z_my_tbl_del values(5,'sam')
> go
> select * from z_my_tbl_del
> go
> drop table z_my_tbl_del
> go
> The resultant column should be all in one line as as opposed to separate
> lines.
> n
> ======
> john,mary,luke,trisha,sam
No comments:
Post a Comment