October 16, 2014 at 11:54 am
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 B:(It 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
LEN10|10|4|10
Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234
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..
October 16, 2014 at 1:50 pm
This was fun, but I'm not sure I have the complete solution. The difficult part is done without dynamic sql.
By the way, this seems like a really bad design, but I guess you might not be able to change it and even if you were, I wouldn't have enough information to give an advice.
CREATE TABLE TableA(
ID int,
MATNR varchar(50),
KUNNR varchar(50),
LIFNRvarchar(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,
LIFNRint,
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' );
WITH CTEA AS(
SELECT ID, col, value
FROM TableA
CROSS APPLY( VALUES( 'MATNR', MATNR),
( 'KUNNR', KUNNR),
( 'LIFNR', LIFNR),
( 'VKORG', VKORG),
( 'PRCTR', PRCTR),
( 'KUNRE', KUNRE),
( 'LIFRE', LIFRE),
( 'PRODH', PRODH))u(col, value)
),
CTEB AS(
SELECT ID, col, value AS colorder, s.Item AS leng
FROM TableB
CROSS APPLY( VALUES( 'MATNR', MATNR),
( 'KUNNR', KUNNR),
( 'LIFNR', LIFNR),
( 'VKORG', VKORG),
( 'PRCTR', PRCTR),
( 'KUNRE', KUNRE),
( 'LIFRE', LIFRE),
( 'PRODH', PRODH))u(col, value)
CROSS APPLY dbo.DelimitedSplit8K( [LEN], '|') s
WHERE s.ItemNumber = u.value
)
SELECT CAST((SELECT RIGHT( REPLICATE( '0', leng) + a.value, leng)
FROM CTEA a
JOIN CTEB b ON a.ID = b.ID AND a.col = b.col
ORDER BY colorder
FOR XML PATH('')) AS varchar(max))
GO
DROP TABLE TableA
DROP TABLE TableB
References:
CROSS APPLY Unpivot: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
DelimitedSplit8K function: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Concatenation: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
October 16, 2014 at 7:03 pm
Thanks Luic for the response...
It seems close, the problem is, it was returning one value for all the rows.
I want it to return the values for each row and put them in a table along with its ID...
Actually I missed Key fields in my initial post, The key fields KSCHL and KOTABNR which will be used to link the tables A and B... these fields should also be appearing on the final result table. (Can we insert the result into a table?)
Appreciate you help...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply