Looping over some selected values in a stored procedure

Question

I'm trying to modify a stored procedure hooked into an ORM tool. I want to add a few more rows based on a loop of some distinct values in a column. Here's the current SP:

SELECT
    GRP = STAT_CD,
    CODE = REASN_CD
FROM dbo.STATUS_TABLE WITH (NOLOCK)
Order by STAT_CD, SRT_ORDR

For each distinct STAT_CD, I'd also like to insert a REASN_CD of "--" here in the SP. However I'd like to do it before the order by so I can give them negative sort orders so they come in at the top of the list.

I'm getting tripped up on how to implement this. Does anyone know how to do this for each unique STAT_CD?

Answer 1

Here you go:

SELECT GRP, CODE, SRT_ORDR FROM 
(
SELECT
    GRP = STAT_CD,
    CODE = REASN_CD,
    SRT_ORDR
FROM dbo.STATUS_TABLE WITH (NOLOCK)
UNION 
SELECT DISTINCT STAT_CD, '--', -1
FROM dbo.STATUS_TABLE WITH (NOLOCK)
) RAW
ORDER BY GRP, SRT_ORDR

Note that you can't alias the first column "Group", as that's a reserved word.

Answer 2

This should do it although I could not test it

select group=stat_cd,
       code=reasn_cd
       from
(SELECT
     2 as sortcol,
     STAT_CD,
     REASN_CD,
    SRT_ORDER
FROM dbo.STATUS_TABLE WITH (NOLOCK)
union all
select 
1 as sortcol,
stat_cd ,'-',null
from dbo.status_table with(nolock)
group by stat_cd) as a
Order by STAT_CD,sortcol, SRT_ORDER