Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Arrays Expand / Collapse
Author
Message
Posted Tuesday, August 12, 2008 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2008 12:45 PM
Points: 1, Visits: 2
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
Post #551277
Posted Wednesday, August 13, 2008 3:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
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
Post #551695
Posted Sunday, August 17, 2008 6:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 36,944, Visits: 31,448
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #554051
Posted Monday, August 18, 2008 7:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
Thanks Jeff !



karthik
Post #554273
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse