Forum Discussion
Table
- Mar 13, 2025
Assume the key columns of table Costinformation are Mandant and Code, we can update its Pricetable by following steps:
- 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).
- 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().
- 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.
- 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
yes. This is correct
Assume the key columns of table Costinformation are Mandant and Code, we can update its Pricetable by following steps:
- 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).
- 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().
- 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.
- 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
- CharlyStellaMar 16, 2025Copper Contributor
It works thank you very much!