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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
JDCam
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
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
JDCam

ASKER

Working perfect. Thanks for your assistance
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo