Forum Discussion

CharlyStella's avatar
CharlyStella
Copper Contributor
Mar 11, 2025
Solved

Table

Hello everyone, 
I have a problem. Our products are dimension-dependent (width / height) and the prices are stored in a table [Costinformation]. The grid table is in the Pricetable column.
Rows are separated with CRLF and the individual columns with Tab.

 

Table [Costinformation]

MandantCodeTextCategoryPricetable
14711Product Table 1Window 
14712Product Table 2Door 


Example Pricetable-Column

W / H100200300400
10010,0020,0030,0040,00
20020,0030,0040,0050,00
30030,0040,0050,0060,00
40040,0050,0060,0070,00


Now it is so that there are always price adjustments. In 100% of cases, this is in percentage terms. 
This means that the prices in the grid table have to be extrapolated. I need a solution so that I no longer have to do this manually. 
Can it be done directly via an SQL statement?


Example:
Product Table 1 --> price + 5%
Produkt Table 2 --> price + 3 %

I hope it is understandable. My English is not so good. I can also provide an example table. 

Thank you very much for you help. Greetings from Germany
CharlyStella

  • rodgerkong's avatar
    rodgerkong
    Mar 13, 2025

    Assume the key columns of table Costinformation are Mandant and Code, we can update its Pricetable by following steps:

    1. Split each cell of the Pricetable to rows. This will use STRING_SPLIT, because it can only use CHAR(1)/NCHAR(1) to identify separator, I have to replace all CRLF( CHAR(13)CHAR(10) ) to CR( because CRLF is CHAR(2) ). I treat the Pricetable column as NVARCHAR, so I use NCHAR to declare separators, if your column is VARCHAR, please change NCHAR(n) to CHAR(n).
    2. Adjust the price. STRING_SPLIT can assign an ordinal number to each value. In your case, the 1st row and 1st column of the Pricetable are headers, and the others are the price values. Your data uses German format digitals, so I have to convert them to numeric using PARSE(), and after calculating the new price, convert them to strings with FORMAT(), both of functions I have 'de-de' as culture parameter. Then UNION all header rows with price rows. NOTE: I'm assuming that all cells of Pricetable have values, otherwise you'll need to add some process steps before PARSE()
    3. Aggregated price rows to one string. I'm using two GROUP BY SQL to do this. STRING_AGG help to concatenate strings in the correct order.
    4. Update Costinformation with new Pricetable value.

    I'm using CTE to make the process clear, you can find the condition which filter the rows that need to be adjusted at code line 11, and the percent value at line 18. This is not good to use, you'd better replace them with variants, or rewrite the process on other way.

    Code is here

    WITH SPLITED_ORG  --Split each cells of the Pricetable to rows, and filter the data which will be adjusted.
    AS
    (
    	SELECT M.Mandant, M.Code, R.ordinal AS row_num, C.ordinal AS col_num, C.value FROM Costinformation M
    	CROSS APPLY
    		STRING_SPLIT( REPLACE(M.Pricetable, NCHAR(13)+ NCHAR(10), NCHAR(13)), NCHAR(13), 1) R
    		CROSS APPLY
    			STRING_SPLIT(R.value, NCHAR(9), 1) C
    -- ******** CHANGE THE CONDITIONS BELOW TO FILTER THE DATA THAT WILL BE ADJUSTED 
    	WHERE
    		[Text] = 'Product Table 1'
    )
    , SPLITED_ADJ --Adjusting price
    AS
    (
    	SELECT O.Mandant, O.Code, O.row_num, O.col_num, --Filter all rows contain prices value (row_num and col_num > 1)
    -- ******** 1.05 = 105%, change this value to adjust price
    		FORMAT(PARSE(O.value AS DECIMAL(8,2) USING 'de-de') * 1.05, 'N2', 'de-de' ) AS T
    	FROM SPLITED_ORG O
    	WHERE O.row_num>1 AND O.col_num>1
    	UNION ALL
    	SELECT O.Mandant, O.Code, O.row_num, O.col_num, O.value AS T  --Assemble back all header cells( row_num or col_num = 1)
    	FROM SPLITED_ORG O
    	WHERE O.row_num=1 OR O.col_num=1
    )
    , AGG_ROWS  --Assemble rows
    AS
    (
    	SELECT Mandant, Code, STRING_AGG(T, NCHAR(9)) WITHIN GROUP(ORDER BY col_num ASC) AS row, row_num
    	FROM SPLITED_ADJ
    	GROUP BY Mandant, Code, row_num
    )
    , RESULTS  --Assemble to string
    AS
    (
    	SELECT Mandant, Code, STRING_AGG(row, NCHAR(13)+ NCHAR(10)) WITHIN GROUP(ORDER BY row_num ASC) AS r
    	FROM AGG_ROWS
    	GROUP BY Mandant, Code
    )
    UPDATE C  --Update data with new Pricetable
    	SET Pricetable = R.r
    FROM 
    	Costinformation C INNER JOIN RESULTS R
    	ON C.Mandant = R.Mandant AND C.Code=R.Code

     

  • CharlyStella's avatar
    CharlyStella
    Copper Contributor

    Hello rodgerkong ,
    yes you are correct. I am not sure why my first message is not visible. But your solution go in the right direction. 

    CharlyStella

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor

    Dose the example table Pricetable belong to one row of table Costinformation? If it does, assume its belonging Code 4711,after price adjustment, the result of Pricetable would be 


    Am I correct?

      • rodgerkong's avatar
        rodgerkong
        Iron Contributor

        Assume the key columns of table Costinformation are Mandant and Code, we can update its Pricetable by following steps:

        1. Split each cell of the Pricetable to rows. This will use STRING_SPLIT, because it can only use CHAR(1)/NCHAR(1) to identify separator, I have to replace all CRLF( CHAR(13)CHAR(10) ) to CR( because CRLF is CHAR(2) ). I treat the Pricetable column as NVARCHAR, so I use NCHAR to declare separators, if your column is VARCHAR, please change NCHAR(n) to CHAR(n).
        2. Adjust the price. STRING_SPLIT can assign an ordinal number to each value. In your case, the 1st row and 1st column of the Pricetable are headers, and the others are the price values. Your data uses German format digitals, so I have to convert them to numeric using PARSE(), and after calculating the new price, convert them to strings with FORMAT(), both of functions I have 'de-de' as culture parameter. Then UNION all header rows with price rows. NOTE: I'm assuming that all cells of Pricetable have values, otherwise you'll need to add some process steps before PARSE()
        3. Aggregated price rows to one string. I'm using two GROUP BY SQL to do this. STRING_AGG help to concatenate strings in the correct order.
        4. Update Costinformation with new Pricetable value.

        I'm using CTE to make the process clear, you can find the condition which filter the rows that need to be adjusted at code line 11, and the percent value at line 18. This is not good to use, you'd better replace them with variants, or rewrite the process on other way.

        Code is here

        WITH SPLITED_ORG  --Split each cells of the Pricetable to rows, and filter the data which will be adjusted.
        AS
        (
        	SELECT M.Mandant, M.Code, R.ordinal AS row_num, C.ordinal AS col_num, C.value FROM Costinformation M
        	CROSS APPLY
        		STRING_SPLIT( REPLACE(M.Pricetable, NCHAR(13)+ NCHAR(10), NCHAR(13)), NCHAR(13), 1) R
        		CROSS APPLY
        			STRING_SPLIT(R.value, NCHAR(9), 1) C
        -- ******** CHANGE THE CONDITIONS BELOW TO FILTER THE DATA THAT WILL BE ADJUSTED 
        	WHERE
        		[Text] = 'Product Table 1'
        )
        , SPLITED_ADJ --Adjusting price
        AS
        (
        	SELECT O.Mandant, O.Code, O.row_num, O.col_num, --Filter all rows contain prices value (row_num and col_num > 1)
        -- ******** 1.05 = 105%, change this value to adjust price
        		FORMAT(PARSE(O.value AS DECIMAL(8,2) USING 'de-de') * 1.05, 'N2', 'de-de' ) AS T
        	FROM SPLITED_ORG O
        	WHERE O.row_num>1 AND O.col_num>1
        	UNION ALL
        	SELECT O.Mandant, O.Code, O.row_num, O.col_num, O.value AS T  --Assemble back all header cells( row_num or col_num = 1)
        	FROM SPLITED_ORG O
        	WHERE O.row_num=1 OR O.col_num=1
        )
        , AGG_ROWS  --Assemble rows
        AS
        (
        	SELECT Mandant, Code, STRING_AGG(T, NCHAR(9)) WITHIN GROUP(ORDER BY col_num ASC) AS row, row_num
        	FROM SPLITED_ADJ
        	GROUP BY Mandant, Code, row_num
        )
        , RESULTS  --Assemble to string
        AS
        (
        	SELECT Mandant, Code, STRING_AGG(row, NCHAR(13)+ NCHAR(10)) WITHIN GROUP(ORDER BY row_num ASC) AS r
        	FROM AGG_ROWS
        	GROUP BY Mandant, Code
        )
        UPDATE C  --Update data with new Pricetable
        	SET Pricetable = R.r
        FROM 
        	Costinformation C INNER JOIN RESULTS R
        	ON C.Mandant = R.Mandant AND C.Code=R.Code

         

    • OFL's avatar
      OFL
      Copper Contributor

      Good Morning rodgerkong ,

      yes, you are completely right. this is what the result should look like. 
      Each data set has an extra grid table that you have to extrapolate. For the sake of simplicity, I have only shown an example with 2 data sets here. 

      In reality, there may be many data records with a grid table that need to be extrapolated. 

      (Note: this is my work account, hence the different username)

Resources