Reassign Sequential Code

Say I have this table:

if OBJECT_ID('tempdb..#SampleData') is not null
 drop table #SampleData
 
;with samples
as
(
   select 'ABC' as CID, 1 Code union all
   select 'ABC', 2 union all
   select 'ABC', 2 union all
   select 'XYZ', 1 union all
   select 'XYZ', 3 union all
   select 'XYZ', 3 union all
   select 'XYZ', 3
) 
select
*
into #SampleData
from samples

select * from #SampleData

CID    Code
---- ---------
ABC     1
ABC     2
ABC     2
XYZ     1
XYZ     3
XYZ     3
XYZ     3

Now I need to convert the CID for all those with Code > 1 into a sequential number and affix it to CID. The result will now look like this:

CID  code new_CID
---- ---- -------
ABC    1   ABC
ABC    2   ABC-01
ABC    2   ABC-02
XYZ    1   XYZ
XYZ    3   XYZ-01
XYZ    3   XYZ-02
XYZ    3   XYZ-03

My immediate reaction is to create a CTE to partion the result set to CID and sort it by code. Then handle the concatenation of the string on the outside call. Not a bad idea, actually.

However, I think in this case, a CTE is not necessary if I can just call the sorting directly.

Here's what I came up with:

select 
CID, code,
new_CID = CID + 
   case 
      when code = 1 then '' 
      else '-' + right('0' + cast(row_number() over(partition by CID order by CID, code) - 1 as varchar(2)),2) 
   end
from #SampleData

Here's the result set as required by the specs

CID  code  new_CID
---- ----- -------
ABC   1      ABC
ABC   2      ABC-01
ABC   2      ABC-02
XYZ   1      XYZ
XYZ   3      XYZ-01
XYZ   3      XYZ-02
XYZ   3      XYZ-03

As always, comments are welcome...


~~ CK

Group Column in Different Rows Into A Single Column

I encountered a question from the forum that I frequented on, on how to group column from different rows into a single column as a comma delimited value. The number of rows that will be grouped will be variable.

Say this is the input table:

if object_id('tempdb..#sample') is not null 
   drop table #sample

SELECT top 20 
   abs(id) as number
   into #sample
FROM master.dbo.sysobjects

select * from #sample

This is how the table look like

number
-----------
4
5
7
8
15
25
28
29
34
41
42
45
46
47
49

I intentionally did not use sequential numbers so this snippet can be used to concatenate numbers or string. The table may or may not be sorted, as well.

The requirement is to grouped these values depending on user input.

If the user wants to group this by two, the result should look like this:

grouped
--------
4,5
7,8
15,25
28,29
34,41
42,45
46,47
49

If the user wants to group this by five, the result should look like this:

grouped
----------------
4,5,7,8,15
25,28,29,34,41
42,45,46,47,49

Here's the query to build the delimited string:

declare @batch as smallint

set @batch = 5

;with batched
as
(
   select      
      batch_id = (row_number() over(order by number) - 1)/@batch,      
      number
   from #sample
), sorted
as
(
   select 
   batch_id,
   sort_id = row_number() over(partition by batch_id order by number),
   number
   from batched   
),GatherAll
as
(
   select batch_id, sort_id, cast(number as varchar(max)) as Gathered 
   from sorted 
   where sort_id = 1
   union all 
   select s.batch_id, s.sort_id, cast(rtrim(Gathered)  + ',' + cast(s.number as varchar(50)) as varchar(max))
   from sorted s
      inner join GatherAll g on g.batch_id = s.batch_id and s.sort_id = g.sort_id + 1
)
select grouped = max(Gathered)
from GatherAll
group by Batch_Id

Change the value of the variable @batch to change the number of rows to be grouped.

As always, comments are welcome...


~~ CK

Capturing The Result Set Returned by Stored Procedure

Previously, I wrote a code snippet on how to retrieve the result set returned by a stored procedure and treat it as a select statement. Here is the previous post. OPENROWSET function, however, sometimes can lead to a resource hug.

Here's a modified version of the code that uses a table variable:

declare @spWhoTable as table
(
   col_spid int,
   col_ecid int,
   col_status varchar(20),
   col_loginame varchar(20),
   col_hostname varchar(20),
   col_blk varchar(20),
   col_dbname varchar(20),
   col_cmd varchar(50),
   col_request_id int
)
insert into @spWhoTable
exec sp_who

select 
   *
from @spWhoTable

If the result will be used later that requires it to be indexed, a temporary table may be used instead of a table variable.

As always, comments are welcome and highly appreciated.


~~ CK

Randomly selecting every Nth record

In my previous post, I showed how I randomly select X numbers of records. This time I was ask to randomly select every Nth record.

This query will select every 10th record in a random set:

;with RandomRecords
as
(
   select  
      rowid = row_number() over(order by newid()),
      <column list>
   from <tablename> t
   where <filtercondition>
)
select
   [Top X]
   *
from RandomRecords
where rowid % 10 = 0 and rowid <= (X * 10)

Now let's analyze the above code...

• The order by newid() will result in randomly ordering the result set.
• The TOP X is an optional clause if the requirement is to return only a subset of the entire data set, say the final set is 30,000 records, then X = 30,000.
• The rowid % 10 = 0 ensures that the query will pick every 10th record.
• The rowid <= (x * 10) further optimizes the query.

As always, comments are welcome...


~~ CK