Link to home
Start Free TrialLog in
Avatar of JDCam
JDCam

asked on

MS T-SQL - Row Number on group Concatonate with another value

Experts,
I am over my head on this one.

I have a simple data query with a group by
Note the column Batch_num is common on all groups
SELECT 
comp, cust, Lev1, Lev2, sum(FROM_QTY), from_code, MVT_TRANS_TYPE, Batch_num
from BVF947_HOLD_TEST
group by comp, cust, Lev1, Lev2, from_code, MVT_TRANS_TYPE, Batch_Num

Open in new window

comp      cust      Lev1      Lev2      QTY      from_code      MVT_TRANS_TYPE      Batch_num
A1              BVFBALT         ITEM1      LOTA      -270      QI              HL              99000    
A1              BVFBALT         ITEM1      LOTB      -130      QI              HL              99000    
A1              BVFBALT         ITEM2      LOTA      -90      QI              HL              99000    

I need to make the batch_num unique per group by adding a suffix.
My attempt is to use ROW_NUMBER on the group, then concatenate it with Batch_num.
I am unable to concatenate this value. Can anyone assist, or point me in a different direction please

SELECT 
comp, cust, Lev1, Lev2, sum(FROM_QTY), from_code, MVT_TRANS_TYPE, Batch_num, Row_number() OVER (ORDER BY LEV1) as RowNum
from BVF947_HOLD_TEST
group by comp, cust, Lev1, Lev2, from_code, MVT_TRANS_TYPE, Batch_Num

Open in new window

comp      cust      Lev1      Lev2      QTY      from_code      MVT_TRANS_TYPE      Batch_num      RowNum
A1              BVFBALT         ITEM1      LOTA      -270      QI              HL              99000           1
A1              BVFBALT         ITEM1      LOTB      -130      QI              HL              99000           2
A1              BVFBALT         ITEM2      LOTA      -90      QI              HL              99000           3

My ideal output is a batch_num that reads:
99000-1
99000-2
99000-3
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JDCam
JDCam

ASKER

Thanks, I will give this try shortly. Group By is needed
The data I shared was the group by results, not the raw data.  In the raw data there are multiple rows within each group.
Sorry I wasnt more clear.
Avatar of JDCam

ASKER

Working perfect. Thanks for your assistance