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

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;

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;

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.

LVL 42

ste5anSenior DeveloperCommented: 2018-01-26

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

Either it's simply

or when a GROUP BY is necessary

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.

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

LVL 1

JDCamAuthor Commented: 2018-01-26

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?