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
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
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
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
comp cust Lev1 Lev2 QTY from_code MVT_TRANS_TYPE Batch_numA1 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
comp cust Lev1 Lev2 QTY from_code MVT_TRANS_TYPE Batch_num RowNumA1 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Working perfect. Thanks for your assistance
ASKER
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.