I recently came across a scenario where the description in a column was being stored in a comma delimited column as illustrated by the Products table below. This table needed to be unpivoted so that the columns where rotated into column values. The following example illustrates how this can be achieved using an auxiliary numbers table.
CREATE TABLE Products
(
Category INT,
ProductDescription VARCHAR(1000)
)
INSERT Products SELECT 1, ‘Fruit, Vegetables’
INSERT Products SELECT 2, ‘Cooking’
INSERT Products SELECT 2, ‘Cereal, Deserts, Spices’
INSERT Products SELECT 3, ‘Cat Food, Dog Food, Pet Care’
— ~~~ START Generate Auxiliary Table ~~~
CREATE TABLE dbo.Nums
(
i INT NOT NULL IDENTITY
)
INSERT dbo.Nums DEFAULT VALUES
WHILE SCOPE_IDENTITY() < 500
INSERT dbo.Nums DEFAULT VALUES
— ~~~ END Generate Auxiliary Table ~~~
SELECT Category,
CASE
WHEN SUBSTRING(‘,’ + ProductDescription + ‘,’, i, 1) = ‘,’
THEN LTRIM(RTRIM(SUBSTRING(‘,’ + ProductDescription + ‘,’, i + 1,
CHARINDEX(‘,’, ‘,’ + ProductDescription + ‘,’, i + 1) – i – 1 )))
END
FROM Nums cross join Products
WHERE i BETWEEN 1 AND LEN(‘,’ + ProductDescription + ‘,’) – 1
AND SUBSTRING(‘,’ + ProductDescription + ‘,’, i, 1) = ‘,’
