0

I have a table Sample with data stored like below

Id   | String
--------------
1     abc,def,ghi
2     jkl,mno,pqr

I need the output like..

Id   | processedrows
--------------
1     abc
1     def
1     ghi
2     jkl
2     mno
2     pqr

I run in SQL server successfully with XML table.

select a.Id,trim(COLUMN_VALUE) 
FROM tableA a ,xmltable(('"'|| REPLACE(a.String, ',', '","')|| '"'))

How can I do the same with a select query in netezza?

Braiam
  • 1
  • 11
  • 47
  • 78
Danh Tran Quoc
  • 25
  • 3
  • `xmltable` is *not* a valid built-in T-SQL table value function. If it's a user defined TVF then we don't know what it is. Though why are you not using `STRING_SPLIT`? – Thom A Jan 04 '22 at 10:00
  • Also, you reference an object named `a`, yet there are no objects aliased as `a` in your query. In additional, you use the odd 1980's `CROSS JOIN` to your table value function `xmltable`, however, as a result you won't be able to reference columns from the prior table in it's parameters; you would need to us `APPLY` to be able to do that. – Thom A Jan 04 '22 at 10:02
  • Already asked and answered here: https://stackoverflow.com/questions/52859424/sql-to-split-a-column-values-into-rows-in-netezza – Tim Biegeleisen Jan 04 '22 at 10:02
  • Does this answer your question? [SQL to split a column values into rows in Netezza](https://stackoverflow.com/questions/52859424/sql-to-split-a-column-values-into-rows-in-netezza) – Stu Jan 04 '22 at 10:36
  • Though the answer on that question has it's [answer in another castle](https://meta.stackexchange.com/q/225370/397219), @TimBiegeleisen , it's not really that helpful (especially if the link ever goes dead). – Thom A Jan 04 '22 at 10:42
  • Does this answer your question? [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) third answr down uses the new built-in `STRING_SPLIT` – Charlieface Jan 04 '22 at 10:55

0 Answers0