Arrays

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Arrays

Arrays

Rate Topic

Display Mode

Topic Options

Author

Message

gofigurefw

Posted Tuesday, August 12, 2008 11:49 AM

Forum 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

karthik M

Posted Wednesday, August 13, 2008 3:21 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, June 17, 2013 8:34 AM
Points: 2,008, Visits: 2,472

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

Jeff Moden

Posted Sunday, August 17, 2008 6:53 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 33,113, Visits: 27,041

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #554051

karthik M

Posted Monday, August 18, 2008 7:03 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, June 17, 2013 8:34 AM
Points: 2,008, Visits: 2,472

Thanks Jeff !

karthik

Post #554273

« Prev Topic | Next Topic »

Permissions