troubleshooting Question

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

Avatar of JDCam
JDCam asked on
Microsoft SQL ServerSQL
3 Comments1 Solution51 ViewsLast Modified:
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
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
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros