December 9, 2015 at 1:15 am
Hello all,
I have a task today but i don't know how to do this :
imagine this result of a query :
dt completeName
2015-11-10 other people
20151-11-30 me
2015-12-01 candidat 1
2015-12-01 candidat 2
... and so on
query are below
declare @tbl table(dt datetime, completeName varchar(100))
INSERT INTO @tbl values('20151130', 'Me'), ('20151110', 'other people'), ('20151201', 'candidat 1'), ('20151201', 'candidat 2')
SELECT * FROM @tbl order by dt
as you can see it's simple, the result is well execute and insert into a word document as a merge field.
well, that's work well done 🙂
But my boss wants if the date are the same i must to display only once the date (field dt) ..
In fact the result should be :
dt completeName
-----------------------------------
2015-11-10 other people
2015-11-30 Me
2015-12-01 candidat 1
candidat 2
.....
date 2015 12 01 is already present i must to delete it ..
i think i can do a cursor for to do this and store the result into a temporary table .. but perhpas there is some other solution ?
Thanks for all
christophe
December 9, 2015 at 1:36 am
Something like this maybe?
declare @tbl table(dt datetime, completeName varchar(100))
INSERT INTO @tbl values('20151130', 'Me'), ('20151110', 'other people'), ('20151201', 'candidat 1'), ('20151201', 'candidat 2')
SELECT * FROM @tbl order by dt ;
SELECT t1.dt,
stuff( (SELECT ', ' + completeName
FROM @tbl t2
WHERE t2.dt=t1.dt
ORDER BY t1.dt
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,1,'') AS NameList
FROM @tbl t1
GROUP BY t1.dt;
December 9, 2015 at 5:49 am
Or may be quite opposite
declare @tbl table(dt datetime, completeName varchar(100))
INSERT INTO @tbl values('20151130', 'Me'), ('20151110', 'other people'), ('20151201', 'candidat 1'), ('20151201', 'candidat 2')
SELECT * FROM @tbl order by dt ;
select dt_to_show=case rn when 1 then dt end, completeName
from(
select *, rn= row_number() over(partition by dt order by completeName)
FROM @tbl t1
) t
order by dt, completeName
December 10, 2015 at 1:32 am
Hello Right there with Babe, SSCommitted
thanks for your time your solution are different but work now it's my boss who must to decide witch solution keept
@sscommitted i don't know if the boss will be agree to keept the user on the same line but why not
@with babe i think your solution will be keept
here is it my solution, don't smile i'm not an expert 🙂
of course date are sample
--
declare @tbl table(id int, dt datetime, completeName varchar(100), updated int)
INSERT INTO @tbl values(15, '20151130', 'Me',0), (125,'20151110', 'other people',0), (10,'20151201', 'candidat 1',0), (7,'20151201', 'candidat 2',0)
SELECT * FROM @tbl order by dt, completeName
--
declare @tblResult table(dt datetime, completeName varchar(80))
declare @cnt int = 0
declare @dateCompare datetime
declare @name varchar(100)
declare @id int
-- IF OTHER CODE ADD = to the while
WHILE(@cnt < (SELECT COUNT(*) FROM @tbl))
BEGIN
SELECT top 1 @id = ID, @dateCompare=dt, @name=completeName
FROM @tbl where updated = 0 order by dt, completeName
--
IF ( (SELECT count(dt) FROM @tblResult where dt = @dateCompare) > 0)
BEGIN
INSERT INTO @tblResult(dt, completeName)
SELECT null, @name
END
ELSE
BEGIN
INSERT INTO @tblResult(dt, completeName)
SELECT @dateCompare,@name
END
-- update the field update by the id
UPDATE @tbl set updated = 1 where id= (SELECT top 1 ID FROM @tbl where updated = 0 order by dt, completeName)
SET @cnt = @cnt +1
END
-- display result
SELECT CONVERT(varchar(10), dt, 103) as Date, completeName as candidate
FROM @tblResult
December 10, 2015 at 3:33 am
Well..., in short it is not the way how SQL code should be created. Really. This code is slow and dangerous.
First, you shouldn't do this row by row thing. It's a very last resort for very special cases. If nevertheless you need to process the rows one by one, use CURSOR.
Next, this 'update' trick takes no care of any other processes that may work with the same table at the same time. It's OK here because you are updating a table variable which is local. If it would be regular DB table imagine two persons run this report at the same time. And again, you just needn't this 'update' trick when using CURSOR or much better set-oriented sql as in the above posts.
December 11, 2015 at 1:47 am
Hello,
Thanks for your explantion and your time you spend here ..
You know my level in sql is equal to my level in English :> bad ..
it's your query who is place in my code, but when I ask a question in a forum c#, sql or other I'm not waiting a reply without search..
Thanks for all guys
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply