August 12, 2008 at 11:49 am
I am kinda new to querying.
Scenario:
I have two columns. (Item-Column1) and (Position-Column2).
The Item column has many different duplicate records.
I am trying to insert value in multiples of 10, in the Position column as it searches for each duplicate record (in column1).
I remember doing this in college, but I need a little reminder... Thanks for any help!
Example is below.
Example:
~~~~~~~~~~
Current:
Column1 Column2
-------- --------
ABC {null}
ABC {null}
XYZ {null}
ABC {null}
XYZ {null}
XYZ {null}
ABC {null}
HIJ {null}
ABC {null}
==================================
After:
Column1 Column2
-------- --------
ABC 10
ABC 20
ABC 30
ABC 40
ABC 50
XYZ 10
XYZ 20
XYZ 30
HIJ 10
August 13, 2008 at 3:21 am
Hey,
Try this code.
Create table #name
(
Name varchar(5),
Value int null
)
go
insert into #name (Name)
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'ABC'
union all
select 'XYZ'
union all
select 'XYZ'
union all
select 'XYZ'
union all
select 'XYZ'
union all
select 'MNO'
union all
select 'PQR'
union all
select 'PQR'
go
select Name,count(Name) as Cnt
into #Cnt
from #name
group by Name
Select Name,N*10
from #Cnt,Tally
where N <= Cnt
karthik
August 17, 2008 at 6:53 pm
You can get by without creating the extra temp table...
SELECT d.Name,t.N*10 AS DeciCount
FROM (SELECT Name,COUNT(*) AS TheCount
FROM #Name
GROUP BY Name) d
INNER JOIN dbo.Tally t
ON t.N <= d.TheCount
GoFigureFW... if you don't know what a Tally table is, please see the following link...
http://www.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2008 at 7:03 am
Thanks Jeff !
karthik
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply