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.
| 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). |
 |
|
|
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 |
|
|
|
|
|
|
|
|
Dynamically concatenating multiple columns in seq