T-sql code needed - 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)

T-sql code needed

Author

Topic

goodsolution
Starting Member

38 Posts

Posted - 2009-05-08 : 11:37:33

Hi all,
I have an input table called "TABLE1", it is having values as follows
Col1
NameA
NameA
NameA
NameB
NameB
NameB
NameB
NameC
NameC
I want to display the o/p in "TABLE2" as shown below

id Col1
1 NameA
1 NameA
1 NameA
2 NameB
2 NameB
2 NameB
2 NameB
3 NameC
3 NameC

Note: I am having nearly million records in my table

-Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-08 : 11:55:18

DECLARE @Table1 TABLE (Col1 char(5))
DECLARE @Grp int
DECLARE @col1 char(5)
SET @Grp = 1
INSERT INTO @Table1

SELECT 'NameA' UNION ALL
SELECT 'NameA' UNION ALL
SELECT 'NameA' UNION ALL
SELECT 'NameB' UNION ALL
SELECT 'NameB' UNION ALL
SELECT 'NameB' UNION ALL
SELECT 'NameB' UNION ALL
SELECT 'NameC' UNION ALL
SELECT 'NameC'

select dense_rank() over(order by col1) ,col1
from @table1

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources