SQL Server 2017 - Dynamically generate a string based on the number of columns in another string

Question

I have the following table & data:

CREATE TABLE dbo.TableMapping 
(
[GenericMappingKey] [nvarchar](256) NULL,
[GenericMappingValue] [nvarchar](256) NULL,
[TargetMappingKey] [nvarchar](256) NULL,
[TargetMappingValue] [nvarchar](256) NULL
)

INSERT INTO dbo.TableMapping 
(
     [GenericMappingKey]
    ,[GenericMappingValue]
    ,[TargetMappingKey]
    ,[TargetMappingValue]
)
VALUES
( 
     'Generic' 
    ,'Col1Source|Col1Target;Col2Source|Col2Target;Col3Source|Col3Target;Col4Source|Col4Target;Col5Source|Col5Target;Col6Source|Col6Target'
    ,'Target'
    ,'Fruit|Apple;Car|Red;House|Bungalo;Gender|Female;Material|Brick;Solution|IT'
)

I would need to be able to automatically generate my GenericMappingValue string dynamically based on the number of column pairs in the TargetMappingValue column.

Currently, there are 6 column mapping pairs. However, if I only had two mapping column pairs in my TargetMapping such as the following...

'Fruit|Apple;Car|Red'

then I would like for the GenericMappingValue to be automatically generated (updated) such as the following since, as a consequence, I would only have 2 column pairs in my string...

'Col1Source|Col1Target;Col2Source|Col2Target'

I've started building the following query logic:

DECLARE @Mapping nvarchar(256)
SELECT @Mapping = [TargetMappingValue] from TableMapping
print @Mapping
SELECT count(*) ColumnPairCount
FROM String_split(@Mapping, ';')

The above query gives me a correct count of 6 for my column pairs.

How would I be able to continue my logic to achieve my automatically generated mapping string?

Tim Mylott · Accepted Answer · 2019-03-06T18:08:58.147

I think I understand what you are after. This should get you moving in the right direction.

Since you've tagged 2017 you can use STRING_AGG()

You'll want to split your TargetMappingValue using STRING_SPLIT() with ROW_NUMER() in a sub-query. (NOTE: We aren't guaranteed order using string_split() with ROW_NUMBER here, but will work for this situation. Example below using OPENJSON if we need to insure accurate order.)
Then you can then use that ROW_NUMBER() as the column indicator/number in a CONCAT().
Then bring it all back together using STRING_AGG()

Have a look at this working example:

DECLARE @TableMapping TABLE
    (
        [GenericMappingKey] [NVARCHAR](256) NULL
      , [GenericMappingValue] [NVARCHAR](256) NULL
      , [TargetMappingKey] [NVARCHAR](256) NULL
      , [TargetMappingValue] [NVARCHAR](256) NULL
    );

INSERT INTO @TableMapping (
                              [GenericMappingKey]
                            , [GenericMappingValue]
                            , [TargetMappingKey]
                            , [TargetMappingValue]
                          )
VALUES ( 'Generic'
       , 'Col1Source|Col1Target;Col2Source|Col2Target;Col3Source|Col3Target;Col4Source|Col4Target;Col5Source|Col5Target;Col6Source|Col6Target'
       , 'Target'
       , 'Fruit|Apple;Car|Red;House|Bungalo;Gender|Female;Material|Brick;Solution|IT' );



SELECT   [col].[GenericMappingKey]
       , STRING_AGG(CONCAT('Col', [col].[ColNumber], 'Source|Col', [col].[ColNumber], 'Target'), ';') AS [GeneratedGenericMappingValue]
       , [col].[TargetMappingKey]
       , [col].[TargetMappingValue]
FROM     (
             SELECT      *
                       , ROW_NUMBER() OVER ( ORDER BY (
                                                          SELECT 1
                                                      )
                                           ) AS [ColNumber]
             FROM        @TableMapping
             CROSS APPLY STRING_SPLIT([TargetMappingValue], ';')
         ) AS [col]
GROUP BY [col].[GenericMappingKey]
       , [col].[TargetMappingKey]
       , [col].[TargetMappingValue];

Here's an example of what an update would look like assuming your primary key is the GenericMappingKey column:

--This what an update would look like
--Assuming your primary key is the [GenericMappingKey] column
UPDATE     [upd]
SET        [upd].[GenericMappingValue] = [g].[GeneratedGenericMappingValue]
FROM       (
               SELECT   [col].[GenericMappingKey]
                      , STRING_AGG(CONCAT('Col', [col].[ColNumber], 'Source|Col', [col].[ColNumber], 'Target'), ';') AS [GeneratedGenericMappingValue]
                      , [col].[TargetMappingKey]
                      , [col].[TargetMappingValue]
               FROM     (
                            SELECT      *
                                      , ROW_NUMBER() OVER ( ORDER BY (
                                                                         SELECT 1
                                                                     )
                                                          ) AS [ColNumber]
                            FROM        @TableMapping
                            CROSS APPLY [STRING_SPLIT]([TargetMappingValue], ';')
                        ) AS [col]
               GROUP BY [col].[GenericMappingKey]
                      , [col].[TargetMappingKey]
                      , [col].[TargetMappingValue]
           ) AS [g]
INNER JOIN @TableMapping [upd]
    ON [upd].[GenericMappingKey] = [g].[GenericMappingKey];

Shnugo brings up a great point in the comments in that we are not guarantee sort order with string_split() and using row number. In this particular situation it wouldn't matter as the output mappings in generic. But what if you needed to used elements from your "TargetMappingValue" column in the final "GenericMappingValue", then you would need to make sure sort order was accurate.

Here's an example showing how to use OPENJSON() and it's "key" which would guarantee that order using Shnugo example:

SELECT   [col].[GenericMappingKey]
       , STRING_AGG(CONCAT('Col', [col].[colNumber], 'Source|Col', [col].[colNumber], 'Target'), ';') AS [GeneratedGenericMappingValue]
       , [col].[TargetMappingKey]
       , [col].[TargetMappingValue]
FROM     (
             SELECT      [tm].*
                       , [oj].[Key] + 1 AS [colNumber] --Use the key as our order/column number, adding 1 as it is zero based.
                       , [oj].[Value] -- and if needed we can bring the split value out.
             FROM        @TableMapping [tm]
             CROSS APPLY OPENJSON('["' + REPLACE([tm].[TargetMappingValue], ';', '","') + '"]') [oj] --Basically turn the column value into JSON string.
         ) AS [col]
GROUP BY [col].[GenericMappingKey]
       , [col].[TargetMappingKey]
       , [col].[TargetMappingValue];

Btw: `STRING_SPLIT()` does not guarantee to return the expected sort order. So your `ORDER BY SELECT 1` might work in all tests, but fail in production - how ever... You might [read this answer](https://stackoverflow.com/a/38275075/5089204) about a string splitting solution using `OPENJSON` overcome this issue... — Shnugo, Mar 06 '19 at 17:22
@Shnugo thank for the feedback. Noted as I didn't think about that. Thinking about this, in this particular situation, order doesn't matter as long as we get the same number of mapping pairs since the mapping they wanted to generate was generic. — Tim Mylott, Mar 06 '19 at 17:48
@Shnugo thanks again, I updated my answer to call that out and included an example using OPENJSON. That's a nice trick. — Tim Mylott, Mar 06 '19 at 18:06
That's great. You have fully and completely addressed my question! — Shawn_M, Mar 06 '19 at 19:11

JBJ · Answer 2 · 2019-03-06T17:17:21.410

0

if the data is already in the table and you want to break it out into columns, this should work

select 
     v.value
    ,left(v.value, charindex('|',v.value) -1) col1
    ,reverse(left(reverse(v.value), charindex('|',reverse(v.value)) -1)) col2 
from String_split(@mapping,';') v

edited Mar 06 '19 at 17:17

answered Mar 06 '19 at 17:00

JBJ

393
3
8

SQL Server 2017 - Dynamically generate a string based on the number of columns in another string

2 Answers2