How to automatically convert multiple text fields in one cell to different rows or other format for reporting?

Question

I'm looking to report on data in Tableau, I'm having trouble with one column in the data source which has multiple texts separated by commas. Its currently on Google Sheets and I plan to build a table in data warehouse and then link it to the tableau for reporting. The data looks like below:

ID        Year     Location       Fruits
1         2018     USA            Apple, Banana, Kiwi
2         2019     Japan          Kiwi, Orange, Mango, (up to 40 fruits in some columns)

I'm looking for a way to report on Fruits column like pivots. For eg, a way to see trends: How many apples in 2018 vs how many apples in 2019, How many instances of apples in the USA? metrics like that. I'm open to reshaping the data to suit my needs. Using SQL to query data as such and import to Tableau.

Tableau 2018.3 Oracle SQL Server

Sreenu131 · Answer 1 · 2019-06-10T15:14:12.560

First u split the comma separated data into rows and save the data in to table object and pull that table into tableau using sqlserver connection ,below is the code

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL 
DROP TABLE #Temp
;WITH CTE(ID,[Year], Location, Fruits)
AS
(
SELECT 1,2018,'USA'  ,'Apple, Banana, Kiwi' UNION ALL
SELECT 2,2019,'Japan','Kiwi, Orange, Mango'
)
SELECT ID,[Year], Location,
LTRIM(RTRIM(Split.a.value('.','nvarchar(max)'))) AS Fruits INTO #Temp
FROM
(
SELECT ID,[Year], Location,                     
    CAST( '<S>'+ REPLACE(Fruits,',','</S><S>') +'</S>' AS XML ) AS Fruits
FROM CTE 
) AS A
CROSS APPLY Fruits.nodes('S') AS Split(a)


SELECT * 
FROM #Temp

Using PIVOT in sql server u can expect the result

SELECT DISTINCT ID,Location ,[2018],[2019]
FROM
(
SELECT *
FROM #Temp
) AS src
PIVOT
(
COUNT(Fruits) FOR [Year] IN ([2018],[2019])
) AS pvt
ORDER BY ID

Result

ID  Location    2018    2019
---------------------------
1   USA           3      0
2   Japan         0      3

In your first block of codes, WITH CTE section, i have around 1000 rows so i cannot type select statements for each row as you have shown. is there another way? Also, is there an automatic way to convert text to rows in google sheets? so when new rows are added, it gets converted to rows automatically? — Prashanth Cp, Jun 10 '19 at 17:45
u no need to use CTE, i just used it for data, instead of CTE u replace it with ur tableName — Sreenu131, Jun 11 '19 at 07:18

score 0 · Answer 2 · answered Jun 10 '19 at 15:20

If your SQL Server compatibility level>=130 you can use STRING_SPLIT()

DECLARE @T TABLE (ID INT, Fruits NVARCHAR(MAX))
INSERT @T VALUES
(1,'Apple,Banana,Kiwi'),
(2,'Kiwi,Orange,Mango')

SELECT
    ID,
    COUNT(*)
FROM
    @T T
    CROSS APPLY STRING_SPLIT(Fruits, ',') AS X
GROUP BY
    ID

How to automatically convert multiple text fields in one cell to different rows or other format for reporting?

2 Answers2