select query

Question

select query

znkin

Old Hand

Points: 382
More actions
August 3, 2004 at 3:39 am

#88365

I have a select query which is returning records as
CustGrp Custid
100        331
100        233
100        332
101       322
102       423
102       873
Now with every CustGrp value I want to append a sequence of numbers with it which will be the sequence of Custid's for
that CustGrp for example the new recordset should look like this
CustGrp Custid
100/1    331
100/2    233
100/3    332
101/1    322
102/1    423
102/2    873
That is if CustGrp has 3 records of 100 then each should be shown as 100/1,100/2,100/3 and if a custgrp like 101 has just
1 record then just display 101/1
How can I achieve something like this
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Hello,

one way to do this is to select the records with your original query into a temp table with identity column, ordered as necessary - precisely as they should appear in the final resultset (by CustGrp at least, that's obligatory). Then you can select from the temp table. What the new query does is that for each row it finds - based on the identity column value for current record and for the first record with the same CustGrp - how many records with the same CustGrp are there before the current one. That's why the data have to be ordered already when inserting into the temp table; otherwise you get sheer nonsense.

CREATE TABLE #tmp (id_counter int IDENTITY(1,1), CustGrp varchar(10), CustId int)

INSERT INTO #tmp values ('100',331)

INSERT INTO #tmp values ('100',233)

INSERT INTO #tmp values ('100',332)

INSERT INTO #tmp values ('101',322)

INSERT INTO #tmp values ('102',423)

INSERT INTO #tmp values ('102',873)

/* your query will insert the data - this was just for testing */

SELECT

CustGrp + '/' + CONVERT(varchar(10), id_counter - ((select min(id_counter) from #tmp where CustGrp = t.CustGrp)-1)) as CustGrp,

CustId

FROM #tmp t

DROP TABLE #tmp

HTH, Vladan

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 2

Hope this will also fit to the situation.

create table mytest (Custid integer, CustGrp integer)

go

insert into mytest(custgrp, custid)

select 100, 331

union

select 100,233

union

select 100,332

union

select 101,322

union

select 102,423

union

select 102,873

go

Select

A1.*,

cast(a1.custgrp as varchar(10))+'/'+

cast((SELECT COUNT(custid) FROM mytest AS A2 WHERE A1.custid >= A2.custid AND A2.custgrp=A1.custgrp) as varchar(10))

FROM

mytest AS A1

order by

a1.custgrp

Thanks,

Ganesh

Vladan SSC-Insane Points: 22072 More actions · Answer 3

That's right, Ganesh's query is better in that it doesn't use temporary tables, but a plain select statement and I'd recommend it before the one I mentioned - if it is applicable.

However, in case that CustGrp+CustId combination is not unique in your query (same CustId appearing several times with the same CustGrp), or in case you need the records ordered by some other criteria (like your original example suggests - CustId order is 331, 233, 332, not 233,331,332), the use of temporary table may become necessary... or you'll need to add other columns to the query to make each row unique, so that it can be ordered and counted correctly.