Text to columns in SQL

Question

I am trying to perform a fairly simple function in SQL - commonly known as comma delimited function or 'Text to Columns'. I have the output that I would like, now I just need to format it differently.

Currently I have the following output:

Transaction No_ Description
100             Apples, Oranges, Beets
101             Milk Chocolate, Potato Chips
102             Ground Beef, Herbs, Vegetable Stock 
103             Bananas, White Bread
104             Nutella

I'm looking to get the same output but with Description column separated into multiple columns using the ','. Since I am dealing with transactional data, there is no limit on how many description items will be present in that col. Therefore the query must count the highest # of 'words' in the description col and add that many cols. to the output. Basically I'm trying to get a Sparse Matrix to input into another application (R).

Your help will be much appreciated.

Grazi!

You might start by google-ing your subject line, and find https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns or https://stackoverflow.com/questions/17150593/how-to-split-string-into-columns-for-a-view. And try some of these, and come back with interim results. — donPablo, Sep 18 '18 at 01:17
Working with an unknown number of columns requires "dynamic SQL" but still you will need to know the number of columns. Moving this data into another application is probably more efficient by splitting into more ROWS. A fairly common operation in SQL is to split comma delimited strings into multiple ROWS. e.g. https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017 — Paul Maxwell, Sep 18 '18 at 01:50
Check this: https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — CR241, Sep 18 '18 at 01:54

score 0 · Answer 1 · edited Sep 18 '18 at 03:26

you can follow given below code for Text to Columns.

CREATE TABLE TAB1 (ID INT,Description VARCHAR(MAX))

INSERT INTO TAB1
VALUES(100,'Apples, Oranges, Beets'),(101,'Milk Chocolate, Potato Chips'),(102,'Ground Beef, Herbs, Vegetable Stock'),(103,'Bananas, White Bread')
,(104,'Nutella')



SELECT ID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Description
into #temp
FROM
(
SELECT ID,CAST('<XMLRoot><RowData>' + REPLACE(Description,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   TAB1
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

DECLARE @MaxCount INT;

SELECT @MaxCount = max(cnt)
FROM (
    SELECT Id
        ,count(id) AS cnt
    FROM #temp
    GROUP BY Id
    ) X;

DECLARE @SQL NVARCHAR(max)
    ,@i INT;

SET @i = 0;
SET @SQL = '';

WHILE @i < @MaxCount
BEGIN
    SET @i = @i + 1;
    SET @SQL = @Sql + ',
    MAX(CASE WHEN RowNo = ' + cast(@i AS NVARCHAR(10)) + ' THEN  Description END) AS Col' + cast(@i AS NVARCHAR(10));
END

SET @SQL = N';WITH CTE AS (
   SELECT  Description,id, row_number() OVER (PARTITION BY ID ORDER BY id) AS rowno
   FROM   #temp
)
SELECT ID ' + @SQL + N'
FROM   CTE
GROUP  BY ID';

PRINT @SQL;

EXECUTE (@SQL);

Thanks @Nitika for the quick query. I'm going to test it out and get back to you! — Shaurik Kashyap, Sep 18 '18 at 03:40
Ive tried imolementing the code and It conyinues to give me the following error: XML parsing: line 1, character 35, illegal name character. Ive checked what characters are considered illegal in XML and none of them appear in the code. Im using SSMS to run this - would that have an impact on the syntax at all? — Shaurik Kashyap, Sep 18 '18 at 20:34
Hi Shaurik I am using sql server 2012 and I am not getting any error in above script.ID Col1 Col2 Col3 100 Apples Oranges Beets 101 Milk Chocolate Potato Chips NULL 102 Ground Beef Herbs Vegetable Stock 103 Bananas White Bread NULL 104 Nutella NULL NULL — Nitika, Sep 19 '18 at 09:28

Text to columns in SQL

1 Answers1