Please help in SQL 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)

Please help in SQL Query

Author

Topic

nmaruti
Starting Member

10 Posts

Posted - 2009-02-19 : 19:14:54

I have a table with the following data with two columns Srl# and Name

1 Mary
2 David
3 Jhonson
4 Dextor
5 Jenifer
6 David
7 David
8 Jenifer
9 Jenifer
10 Jenifer
11 Jhonson
12 Jhonson
13 Jhonson
14 Jhonson
15 Mary

I want to a query which outputs the data from above table where if the consecutive Srl#s having same Name will be marked as ^ after 1st occurance. For the above data the output should be

1 Mary
2 David
3 Jhonson
4 Dextor
5 Jenifer
6 David
7 ^
8 Jenifer
9 ^
10 ^
11 Jhonson
12 ^
13 ^
14 ^
15 Mary

Thanks,
Narendra.

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-19 : 19:36:38

Here's an example:

--Create some test values
Create table #names(nameid int, nameval varchar(20))

insert into #names(nameid,nameval)
values(1,'mike'),(2,'Mike'),(3,'Joe'),(4,'John')

--Use a CTE to hold the min value for each name
;with minVals(minid,nameval)
as
(
select min(nameid) as minid,nameval
from #names
group by nameval
)

select case when cast(v.minid as varchar(20)) IS NULL then '^' else cast(n.nameid as varchar(20)) end,n.nameval
from #names n
left join minVals v
on n.nameid=v.minid

I should note here that if this is a large table you might want to do this with a subquery instead of a CTE as the CTE is stored in memory and a large set of data would decrease performance.
Mike
"oh, that monkey is going to pay"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 19:41:15

[code]Select t.[Srl#],(Case When t.[Name] = m.[Name then '^' Else t.[Name]End)
from table t left outer join table m
on m.[Srl#]= t.[Srl#] +1[/code]

jbp_j
Starting Member

24 Posts

Posted - 2009-02-20 : 04:21:57

hi try this one also.

update t1
set t1.name = '^'
from @t as t1
inner join @t as t2 on t1.id = t2.id+1
where t1.name = t2.name
select * from @t

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 06:30:54

I guess he is not looking for update query.

quote:
Originally posted by jbp_j

hi try this one also.

update t1
set t1.name = '^'
from @t as t1
inner join @t as t2 on t1.id = t2.id+1
where t1.name = t2.name
select * from @t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-20 : 07:38:38

quote:
Originally posted by nmaruti

I have a table with the following data with two columns Srl# and Name

1 Mary
2 David
3 Jhonson
4 Dextor
5 Jenifer
6 David
7 David
8 Jenifer
9 Jenifer
10 Jenifer
11 Jhonson
12 Jhonson
13 Jhonson
14 Jhonson
15 Mary

I want to a query which outputs the data from above table where if the consecutive Srl#s having same Name will be marked as ^ after 1st occurance. For the above data the output should be

1 Mary
2 David
3 Jhonson
4 Dextor
5 Jenifer
6 David
7 ^
8 Jenifer
9 ^
10 ^
11 Jhonson
12 ^
13 ^
14 ^
15 Mary

Thanks,
Narendra.

This is formation issue. If you use front end application, do it there

Madhivanan

Failing to plan is Planning to fail

nmaruti
Starting Member

10 Posts

Posted - 2009-02-20 : 11:56:05

quote:

Originally posted by sodeep

Select t.[Srl#],(Case When t.[Name] = m.[Name then '^' Else t.[Name]End) 
from table t left outer join table m
on m.[Srl#]= t.[Srl#] +1

Thanks Sodeep. It works.

nmaruti
Starting Member

10 Posts

Posted - 2009-02-20 : 11:56:47

Thanks Sodeep. The query you have given worked fine for me..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 12:07:27

quote:
Originally posted by nmaruti

Thanks Sodeep. The query you have given worked fine for me..

Good

but did you see reply from Madhi.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 12:07:40

quote:
Originally posted by nmaruti

Thanks Sodeep. The query you have given worked fine for me..

did you see Madhi's suggestion? did you consider doing this at front end?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources