Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select multiple rows in one column with comma

Author  Topic 

anaze
Starting Member

6 Posts

Posted - 2013-07-26 : 15:48:00
CREATE TABLE [dbo].[x1](
[nomer] [varchar](15) NOT NULL,
[tgl] [datetime] NOT NULL,
CONSTRAINT [pk_x1] PRIMARY KEY CLUSTERED
(
[nomer] ASC,
[tgl] asc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into x1 values ('1','2013-07-30')
insert into x1 values ('1','2013-07-29')
insert into x1 values ('1','2013-07-28')

select * from x1
Result :
nomer tanggal
1 2013-07-28 00:00:00.000
1 2013-07-29 00:00:00.000
1 2013-07-30 00:00:00.000

I Want to make :
nomer tanggal
1 28,29,30

Any Idea...?
Please help me

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-26 : 15:58:38
You can use the XML PATH method like shown below
SELECT
x1.nomer,
STUFF(x2.tanggal,1,1,'') AS tanggal
FROM
(SELECT DISTINCT nomer FROM x1) AS x1
CROSS APPLY
(
SELECT ','+CAST(DAY([tgl]) AS VARCHAR(2)) FROM
x1 x2 WHERE x2.nomer = x1.nomer
FOR XML PATH('')
) x2(tanggal);


And this is a blatant and shameless attempt to plug a blog:

------------------------------------------------------------------------------------------------------
SQL Server SSB
http://myshallowsqlblog.wordpress.com/the-lowdown-on-that-xml-path-thingie/
Go to Top of Page

anaze
Starting Member

6 Posts

Posted - 2013-07-26 : 16:26:18
Oh nice...
Thanks a lot Mr. James K
Go to Top of Page
   

- Advertisement -