Help need to write a 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)

Help need to write a Query

Author

Topic

S_Lakshmi
Starting Member

22 Posts

Posted - 2008-06-09 : 12:05:17

Hello all,

I need a help to write a query. Here is the table

Declare @Test Table
	(
		EName Varchar(15)
	)

Insert into Ename 
Select 'a' Union all
Select 'a' Union all
Select 'a' Union all
Select 'b' Union all
Select 'c' Union all
Select 'b' Union all
Select 'd' Union all
Select 'g' Union all
Select 'g'.

Now i need a result like

a	0
a	1
a	2
b	0
b	1
c	0
d	0
g	0
g	1

Could any one can help me to wite this query..?

Thanks
Lakshmi.S

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-09 : 12:11:44

[code]
select t.ename,s.number from
(
select ename, count(*) as counting from @test
group by ename
) t cross join master..spt_values s
where s.type='p' and number<t.counting
[/code]

Madhivanan

Failing to plan is Planning to fail