Query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query

Author

Topic

Mageshkumar
Starting Member

29 Posts

Posted - 2008-07-18 : 07:09:21

Hi i am getting datas in my table like this format:

Name Date

N1 7-21-2008
N1 7-22-2008
N2 7-21-2008
N2 7-25-2008
N2 7-26-2008
N3 7-24-2008

But i want to show my table like this:

Name Dates

N1 7-21-2008, 7-22-2008
N2 7-21-2008, 7-25-2008, 7-26-2008
N3 7-24-2008

How to do that?,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-18 : 07:11:58

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

Mageshkumar
Starting Member

29 Posts

Posted - 2008-07-18 : 07:31:34

But For me I am getting error as

"Conversion failed when converting datetime from character string."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 07:53:46

quote:
Originally posted by Mageshkumar

But For me I am getting error as

"Conversion failed when converting datetime from character string."

run this first and then try query

SET DATEFORMAT mdy

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-18 : 08:21:35

chk this...,

create procedure get_date
as
begin
if object_id ('new_table') is not null
drop table new_table
if object_id ('temp1') is not null
drop table temp1
create table new_table (name varchar(100),date datetime )
DECLARE
@l_date VARCHAR(100) ,
@l_count int,
@l_val int,
@l_name varchar(100)

select distinct name, 0 as checked into temp1 from your_table_name

select @l_count =count(name) from temp1

set @l_val=1

while (@l_val<=@l_count)
begin

set @l_val=@l_val+1
select top 1 @l_name= name from temp1 where checked =0

SELECT @l_date = COALESCE(@l_date,' ') +date + ', '
FROM your_table_name where name=@l_name

insert into new_table
select @l_name as name ,left(@l_date,len(@l_date)-1) as date

update temp1
set checked =1
where name=@l_name

set @l_date=''
end

select * from new_table

end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 08:25:25

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Mageshkumar
Starting Member

29 Posts

Posted - 2008-07-18 : 08:57:58

quote:
Originally posted by visakh16

quote:
Originally posted by Mageshkumar

But For me I am getting error as

"Conversion failed when converting datetime from character string."

run this first and then try query

SET DATEFORMAT mdy

Where I want to give this line?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-18 : 09:38:50

[code]DECLARE @Sample TABLE (Name VARCHAR(2), Date DATETIME)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 'N1', '7-21-2008' UNION ALL
SELECT 'N1', '7-22-2008' UNION ALL
SELECT 'N2', '7-21-2008' UNION ALL
SELECT 'N2', '7-25-2008' UNION ALL
SELECT 'N2', '7-26-2008' UNION ALL
SELECT 'N3', '7-24-2008'

SELECT w.Name,
STUFF(g.c, 1, 2, '') AS Dates
FROM (
SELECT Name
FROM @Sample
GROUP BY Name
) AS w
CROSS APPLY (
SELECT ', ' + CONVERT(VARCHAR(10), e.Date, 105)
FROM @Sample AS e
WHERE e.Name = w.Name
ORDER BY e.Date
FOR XML PATH('')
) AS g(c)[/code]

E 12°55'05.25"
N 56°04'39.16"

Mageshkumar
Starting Member

29 Posts

Posted - 2008-07-18 : 10:00:00

Thank U Peso
Thanks Lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources