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
LVL 1
JDCamAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You're violating 1NF.. also it is not clear why you need a GROUP BY at all..

Either it's simply

DECLARE @Sample TABLE
    (
        comp NVARCHAR(255) ,
        cust NVARCHAR(255) ,
        Lev1 NVARCHAR(255) ,
        Lev2 NVARCHAR(255) ,
        QTY INT ,
        from_code NVARCHAR(255) ,
        MVT_TRANS_TYPE NVARCHAR(255) ,
        Batch_num INT
    );

INSERT INTO @Sample ( comp ,
                      cust ,
                      Lev1 ,
                      Lev2 ,
                      QTY ,
                      from_code ,
                      MVT_TRANS_TYPE ,
                      Batch_num )
VALUES ( 'A1', 'BVFBALT', 'ITEM1', 'LOTA', -270, 'QI', 'HL', 99000 ) ,
       ( 'A1', 'BVFBALT', 'ITEM1', 'LOTB', -130, 'QI', 'HL', 99000 ) ,
       ( 'A1', 'BVFBALT', 'ITEM2', 'LOTA', -90, 'QI', 'HL', 99000 );

SELECT S.comp ,
       S.cust ,
       S.Lev1 ,
       S.Lev2 ,
       S.QTY ,
       S.from_code ,
       S.MVT_TRANS_TYPE ,
       CAST(S.Batch_num AS NVARCHAR(255)) + '-' + 
       CAST(ROW_NUMBER() OVER ( PARTITION BY S.Batch_num
                           ORDER BY S.Lev1 ) AS NVARCHAR(255))
FROM   @Sample S;

Open in new window

or when a GROUP BY is necessary

SELECT   S.comp ,
         S.cust ,
         S.Lev1 ,
         S.Lev2 ,
         SUM(S.QTY) ,
         S.from_code ,
         S.MVT_TRANS_TYPE ,
         CAST(S.Batch_num AS NVARCHAR(255)) + '-' +  
         CAST(ROW_NUMBER() OVER ( PARTITION BY S.comp ,
                                               S.cust ,
                                               S.Lev2 ,
                                               S.from_code ,
                                               S.MVT_TRANS_TYPE ,
                                               S.Batch_num
                                  ORDER BY S.Lev2 ) AS NVARCHAR(255))
FROM     @Sample S
GROUP BY S.comp ,
         S.cust ,
         S.Lev1 ,
         S.Lev2 ,
         S.from_code ,
         S.MVT_TRANS_TYPE ,
         S.Batch_num;

Open in new window


then the column in the ROW_NUMBER ORDER BY must not be in the PARTITION BY, but you need the other columns of the GROUP BY.

Updated: Missed a CAST.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JDCamAuthor Commented:
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.
0
JDCamAuthor Commented:
Working perfect. Thanks for your assistance
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.