# ksqlDB Table - Append new values to existing column as output of query
**URL:** https://forum.confluent.io/t/ksqldb-table-append-new-values-to-existing-column-as-output-of-query/2850
**Category:** ksqlDB
**Created:** [21 September 2021 01:38 UTC](https://forum.confluent.io/t/ksqldb-table-append-new-values-to-existing-column-as-output-of-query/2850 "2021-09-21T01:38:11Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@gautamg](https://forum.confluent.io/u/gautamg)
#### Post date: [21 September 2021 01:38 UTC](https://forum.confluent.io/t/ksqldb-table-append-new-values-to-existing-column-as-output-of-query/2850/1 "2021-09-21T01:38:11Z")
This is an alternative take on the below posted question
> [@Tombstone message in Table when filtering duplicate events](https://forum.confluent.io/t/tombstone-message-in-table-when-filtering-duplicate-events/2808):
>
> GitHub Issue: [Tombstone message in Table when filtering duplicate events · Issue #8145 · confluentinc/ksql · GitHub](https://github.com/confluentinc/ksql/issues/8145) Provide details of the setup you’re running ksql\> version Version: 0.21.0-rc6 Outline your question I am trying to filter duplicate events and end up seeing TOMBSTONE messages - something I was not expecting to see in the output. Below is my setup ## create a stream so store vehicle location CREATE STREAM VEHICLE\_LOCATION ( VIN VARCHAR, LOCATION\_NAME VARCHAR ) WITH …
Given a stream and inputs
```auto
CREATE STREAM VEHICLE_LOCATION (
VIN VARCHAR,
LOCATION_NAME VARCHAR
) WITH (
KAFKA_TOPIC = 'vehicle-location',
FORMAT = 'JSON',
PARTITIONS = 3
);
## insert few values into the stream
INSERT INTO VEHICLE_LOCATION(VIN, LOCATION_NAME) VALUES ('2G1WL54T4R9165225', 'DALLAS');
INSERT INTO VEHICLE_LOCATION(VIN, LOCATION_NAME) VALUES ('2G1WL54T4R9165225', 'DALLAS');
INSERT INTO VEHICLE_LOCATION(VIN, LOCATION_NAME) VALUES ('2G1WL54T4R9165225', 'DALLAS');
INSERT INTO VEHICLE_LOCATION(VIN, LOCATION_NAME) VALUES ('2G1WL54T4R9165225', 'HOUSTON');
INSERT INTO VEHICLE_LOCATION(VIN, LOCATION_NAME) VALUES ('2G1WL54T4R9165225', 'HOUSTON');
INSERT INTO VEHICLE_LOCATION(VIN, LOCATION_NAME) VALUES ('2G1WL54T4R9165225', 'HOUSTON');
```
How can i write a query where result table is something like below (with the last row reflecting the current state)? This is along the lines of
- Read a value from the stream
- Join it with the table and append the new stream value to the same table’s column (not sure if ksqlDB supports this)
```auto
+------------------------------+-------------------------------------------------------------+
|VIN |LOCATION_NAME |
+------------------------------+-------------------------------------------------------------+
|2G1WL54T4R9165225 |DALLAS |
|2G1WL54T4R9165225 |DALLAS, DALLAS |
|2G1WL54T4R9165225 |DALLAS, DALLAS, DALLAS |
|2G1WL54T4R9165225 |DALLAS, DALLAS, DALLAS, HOUSTON |
|2G1WL54T4R9165225 |DALLAS, DALLAS, DALLAS, HOUSTON, HOUSTON, |
|2G1WL54T4R9165225 |DALLAS, DALLAS, DALLAS, HOUSTON, HOUSTON, HOUSTON, HOUSTON |
```
Much appreciated
---
### Author:  [@mjsax](https://forum.confluent.io/u/mjsax)
#### Post date: [21 September 2021 05:54 UTC](https://forum.confluent.io/t/ksqldb-table-append-new-values-to-existing-column-as-output-of-query/2850/2 "2021-09-21T05:54:50Z")
Your query is basically an aggregation. You can use the `collect_list` aggregation function:
```sql
CREATE TABLE result AS
SELECT vin, COLLECT_LIST(location_name)
FROM vehicle_location
GROUP BY vin;
```
Cf [ksqlDB Aggregate Functions - ksqlDB Documentation](https://docs.ksqldb.io/en/latest/developer-guide/ksqldb-reference/aggregate-functions/#collect_list)
Note that a list-aggregation creates larger-and-larger rows over time, what could become an issue, because Kafka (and the producer) applies some (configurable) limitation on the record size.
---
### Author:  [@gautamg](https://forum.confluent.io/u/gautamg)
#### Post date: [22 September 2021 19:21 UTC](https://forum.confluent.io/t/ksqldb-table-append-new-values-to-existing-column-as-output-of-query/2850/3 "2021-09-22T19:21:50Z")
Thank you very much for your time and the solution.
---
### Author:  [@system](https://forum.confluent.io/u/system)
#### Post date: [29 September 2021 19:22 UTC](https://forum.confluent.io/t/ksqldb-table-append-new-values-to-existing-column-as-output-of-query/2850/4 "2021-09-29T19:22:08Z")
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.