Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamically concatenating multiple columns in seq

Author  Topic 

sarjan
Starting Member

2 Posts

Posted - 2014-10-16 : 14:05:53
Hi Experts,
I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths.
eg..
Input 1:
Table A: (below are the fields and their respective values, not all fields will have values)
-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH
---------
Table BSadIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)
---------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN--> 10|10|4|10

Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR 1234567890
LIFNR
VKORG a234
PRCTR
KUNRE 4355325363
LIFRE 0088390234
PRODH
Concat_String 12345678904355325363a2340088390234

Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif
then after concat the value should be like below:
12345678904355325363a234 88390234

Note:
The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...
eg..
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH

I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...

I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..


Can any one help me on how can I achieve this in SQL...

Thanks a lot for your help..

Y SarjanRao

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-17 : 10:09:30
Please post the table definitions (CREATE TABLE statements).
Go to Top of Page

sarjan
Starting Member

2 Posts

Posted - 2014-10-17 : 16:42:44
Here the script of the tables..

CREATE TABLE TableA(
ID int,
MATNR varchar(50),
KUNNR varchar(50),
LIFNR varchar(50),
VKORG varchar(50),
PRCTR varchar(50),
KUNRE varchar(50),
LIFRE varchar(50),
PRODH varchar(50))
INSERT INTO TableA (ID, KUNNR, VKORG, KUNRE, LIFRE)
VALUES (1, '1234567890' , 'a234' , '4355325363' , '88390234' )
CREATE TABLE TableB(
ID int,
MATNR int,
KUNNR int,
LIFNR int,
VKORG int,
PRCTR int,
KUNRE int,
LIFRE int,
PRODH int,
[LEN] varchar(50))
INSERT INTO TableB (ID, KUNNR, VKORG, KUNRE, LIFRE, [LEN])
VALUES (1, 1,3,2,4, '10|10|4|10' );

Thanks for looking into this...

Y SarjanRao
Go to Top of Page
   

- Advertisement -