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

Experts Exchange connects you with the people and services you need so you can get back to work.

Sponsored byJamf Now

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

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.

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.

Working perfect. Thanks for your assistance

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

Who is Participating?

Already a member? Login.