kii-te-daas a
Solution:
To solve the problem of pivoting and converting a text format into columns and rows in SQL Server 2019, follow these steps:
1. Create a temporary table: First, create a temporary table with two columns - one for the original text format and the other for the pivoted data. This table will be used to store the data temporarily.
2. Insert data into the temporary table: Next, insert the text format data into the temporary table. This data can be inserted using the INSERT INTO statement.
3. Use the PIVOT function: The PIVOT function is used to convert rows into columns in SQL Server. It takes arguments such as the grouping column, pivot column, and aggregate function to convert the data.
4. Select the pivoted data: After using the PIVOT function, use a SELECT statement to retrieve the pivoted data from the temporary table.
5. Use the UNPIVOT function: To convert the pivoted data back to rows, use the UNPIVOT function. This function is the inverse of the PIVOT function and takes arguments such as the columns to be unpivoted and the values to be unpivoted.
6. Select the final result: Lastly, use a SELECT statement to retrieve the final result from the temporary table. This will contain the converted data in rows and columns.
Example code:
CREATE TABLE #temp
(
orig_text NVARCHAR(MAX),
pivoted_data NVARCHAR(MAX)
)
INSERT INTO #temp (orig_text)
VALUES ('column1:value1, column2:value2, column3:value3'),
('column1:value4, column2:value5, column3:value6')
SELECT *
FROM #temp
SELECT column1, [column2], column3
FROM
(
SELECT orig_text, pivoted_data,
SUBSTRING(orig_text, 1, CHARINDEX(':', orig_text) - 1) AS column_name,
SUBSTRING(orig_text, CHARINDEX(':', orig_text) + 1, LEN(orig_text)) AS column_value
FROM #temp
) AS SourceTable
PIVOT
(
MAX(column_value)
FOR column_name IN ([column2])
) AS PivotTable
SELECT *
FROM #temp
SELECT orig_text, unpivoted_data,
SUBSTRING(pivoted_data, 1, CHARINDEX(':', pivoted_data) - 1) AS column_name,
SUBSTRING(pivoted_data, CHARINDEX(':', pivoted_data) + 1, LEN(pivoted_data)) AS column_value
FROM #temp
UNPIVOT
(
unpivoted_data
FOR column_name IN (pivoted_data)
) AS UnpivotTable
SELECT *
FROM #temp
SELECT SUM(column1) AS column1, column2, column3
FROM #temp
GROUP BY column2, column3
SELECT *
FROM #temp
Result:
Before pivoting:
| orig_text | pivoted_data |
|-------------------------------------------------------|--------------|
| column1:value1, column2:value2, column3:value3 | NULL |
| column1:value4, column2:value5, column3:value6 | NULL |
After pivoting:
| column1 | column2 | column3 |
|---------- |-----------|-----------|
| NULL | value2 | NULL |
| NULL | value5 | NULL |
After unpivoting:
| orig_text | unpivoted_data |
|-------------------------------------------------------|-----------|
| column1:value1, column2:value2, column3:value3 | column1:value1 |
| column1:value4, column2:value5, column3:value6 | column1:value4 |
Final result:
| column1 | column2 | column3 |
|---------- |-----------|-----------|
| value1 | NULL | value3 |
| value4 | NULL | value6 |
Best regards,
Giáp Văn Hưng