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.
| Author |
Topic |
|
caravanpunk
Starting Member
20 Posts |
 Posted - 2012-02-03 : 18:18:23
|
| Hello any help would be much appreciated!!create table #temp_Person (recID int identity(1,1),[Name] varchar(100),birthdate datetime)Insert into #temp_Person values ('Test Person','01/01/1990') Insert into #temp_Person values ('Tst PPO','01/01/2000')Insert into #temp_Person values ('Unknown Person','01/01/1980')Insert into #temp_Person values ('Guest P','01/01/1970')Insert into #temp_Person values ('Un. Friend','01/01/1960')select [Name],convert(varchar(12),Birthdate,101) from #temp_personhow to generate the above result to look like belowTest Person,01/01/1990,Tst PPO,01/01/2000,Unknown Person,01/01/1980,Guest P,01/01/1970,Un. Friend,01/01/1960All in One Line!! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-03 : 18:31:55
|
| Are you trying to get it as one long comma-separated string, or are you trying to get each value in a separate column? |
 |
|
|
caravanpunk
Starting Member
20 Posts |
Posted - 2012-02-03 : 18:35:33
|
| Yes, i am trying to get it as one long comma-separated string'Test Person' +','+'01/01/1990'+','+Tst PPO+'01/01/2000'+',' and so on |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-03 : 18:40:08
|
| [code]SELECT STUFF ( ( SELECT ',' + [Name] + ',' + CONVERT(varchar(10),birthdate,103) AS [text()] FROM #temp_Person FOR XML PATH ('') ) ,1,1,'');[/code]If you want the dates to be US style (mm/dd/yyyy, change 103 to 101 in the CONVERT function |
|
|
|
caravanpunk
Starting Member
20 Posts |
Posted - 2012-02-03 : 18:42:40
|
| Wow, this fast!!! Thank you very much :) |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-03 : 18:44:10
|
| You are quite welcome .)Please see my edit in my reply:"If you want the dates to be US style (mm/dd/yyyy, change 103 to 101 in the CONVERT function" |
|
|
|
caravanpunk
Starting Member
20 Posts |
Posted - 2012-02-03 : 18:46:30
|
| Yes i got that, used convert date function as 101.Thank you |
|
|
|
|
|
|
|
|
Query Help