--- title: "How to Convert Array to Table: DB2 UNNEST Function" description: "Here is an example UNNEST SQL query explaining how to use it in DB2 programs, a quick reference for you." url: https://srinimf.com/2023/06/11/the-real-use-of-unnest-function-in-db2-sql/ date: 2023-06-11 modified: 2026-10-10 author: "Srini" image: https://i0.wp.com/srinimf.com/wp-content/uploads/2023/06/pexels-photo-121668.jpeg?fit=1880%2C1249&ssl=1 categories: ["DB2"] tags: ["UNNEST Table function"] type: post lang: en --- # How to Convert Array to Table: DB2 UNNEST Function Here is a way [to use](https://srinimf.com/2020/06/28/how-to-create-a-view-to-format-dates/) UNNEST Table function in DB2. It takes an array of elements as input and returns output in Table format. So it is called the Table function. ## UNNEST Table function When you use this function in high-level [programming languages](https://srinimf.com/2025/01/10/getting-started-with-ai-essential-skills-and-tools/)(COBOL), ensure [to pass](https://srinimf.com/2018/12/31/python-pass-by-value-vs-reference-read-today/) an array of data as input [to get](https://srinimf.com/2016/09/22/how-to-analyze-sql-query-the-best-way/) output in Table format. The schema for this function is SYSIBM. [Sample SQL query](https://srinimf.com/2018/10/23/ordering-sql-query-results-using-desc-vs-asc/) The RECENT_CALLS data is an array of phone numbers. It reads this input and gives output in Table format. ``` SELECT T.ID, T.NUM  FROM UNNEST(RECENT_CALLS) WITH ORDINALITY AS T(NUM, ID) ``` Output ``` ID NUM ---------- 1 9055553907 2 4165554213 3 4085553678 ``` ## The Purpose of UNNEST - The function you need to use in FROM clause - The UNNEST function can only be used in a collection-derived-table clause in a context where arrays are supported (SQLSTATE 42887). References - [The UNNEST Table function and SQL Errors](https://www.ibm.com/docs/en/db2/11.1?topic=functions-unnest) Related - [How to CREATE and DROP User-defined Function in DB2](https://srinimf.com/2020/07/25/db2-user-defined-function-syntax-to-create-drop/)DateJuly 25, 2020 - [Master PySpark Functions: Collect_list, Explode, left_anti, Split](https://srinimf.com/2024/04/19/3-pyspark-functions-frequently-asked-in-interviews/)DateApril 19, 2024 - [Sweet Spots of DB2 V11 with Hadoop](https://srinimf.com/2015/06/05/sweet-spots-of-db2-v11-with-hadoop/)DateJune 5, 2015 ### Share this: - - [Tweet](https://twitter.com/share) - [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2023%2F06%2F11%2Fthe-real-use-of-unnest-function-in-db2-sql%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2023%2F06%2Fpexels-photo-121668.jpeg%3Ffit%3D1200%252C797%26ssl%3D1&description=How%20to%20Convert%20Array%20to%20Table%3A%20DB2%20UNNEST%20Function) - - [Share on Tumblr](https://www.tumblr.com/share) - [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2023/06/11/the-real-use-of-unnest-function-in-db2-sql/?share=jetpack-whatsapp) - [More](#) - - [Print (Opens in new window) Print](https://srinimf.com/2023/06/11/the-real-use-of-unnest-function-in-db2-sql/?share=print) - [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2023/06/11/the-real-use-of-unnest-function-in-db2-sql/?share=reddit) - [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2023/06/11/the-real-use-of-unnest-function-in-db2-sql/?share=telegram) - [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Convert%20Array%20to%20Table%3A%20DB2%20UNNEST%20Function&body=https%3A%2F%2Fsrinimf.com%2F2023%2F06%2F11%2Fthe-real-use-of-unnest-function-in-db2-sql%2F&share=email) - ### Like this: Like Loading…