Make column ItemID SQL Server 2008 R2

Navigation
0

I have 2 Rows of name Type and TypeID I need to create a call ItemID that the itemID pick up the digits of the rows type and TypeID but that always the typeID is filled with zeros in case the number is less than (100) example below:

Problem

  • If the column% co_of% is 1 and the column% co_of% goes from 1 to 100
  • If the column% co_of% is 2 and the column% co_of% goes from 1 to 100
  • If the column Type is 3 and the column TypeID goes from 1 to 100
  • If the column Type is 4 and the column TypeID goes from 1 to 100

Example: 

Type TypeID
1    1
1    2
1    3
1    4
1    5 sigue hasta el numero 100
1    100
2    1
2    2
2    3
2    4
2    5 sigue hasta el numero 100
2    100
3    1
3    2
3    3
3    4
3    5 sigue hasta el numero 100
3    100
4    1
4    2
4    3
4    4
4    5 sigue hasta el numero 100
4    100
100  1
100  2
100  3
100  4
100  5 sigue hasta el numero 100
100  100

I need to create a third column called Type that takes the TypeID its number is 1 Type is 1 and it looks like this:

Example A: 

Type TypeID ItemID
1    1      1001
1    2      1002
1    3      1003
1    4      1004
1    5      1005
1    5      1100

Note: the filling of two missing digits between TypeID and ItemID is filled with zeros (0) as shown in example A.

Example of creating a row from Type 200 and typeID is 200 would be:

Example B: 

Type TypeID ItemID
200  201    200201
200  202    200202
200  203    200203
200  204    200204
200  205    200205
200  206    200206

Example B shows the combination of digits in type and TypeID (Without digit padding)

    
asked by Juan Carlos Villamizar Alvarez 20.01.2017 в 06:20
source

1 answer

1

If I understand correctly, then the column you want would be: 

SELECT Type,
       TypeID,
       CONVERT(VARCHAR(10),Type) + 
       RIGHT('00'+CONVERT(VARCHAR(3),TypeID),3) ItemID
FROM dbo.TuTabla;
    
answered by 20.01.2017 в 13:18
Validate a text with jquery Difference DataTable and Grid of jQuery