How to combine/append multiple values/rows of column B according to column A

Other SQL Server Topics
1

I have a table that has 3 columns (ID, key, value).
like this:

Untitled
Untitled447×501 5.21 KB

As you see column key has repeated or same texts, but the value does not. I want to combine/append the texts of the column value into one field according to the column key separating them with a comma or newline. and also delete the repeated texts of the column key.
Is there any SQL query code to accomplish this?
I know that this may be against the SQL rules, but I need this table.
I don't want just SELECT; I want to UPDATE the table.
Thanks in advance.

2

Welcome,

So if you want an update what happens to previous data in the table?

3

The previous information will be deleted. as you see in the picture, the words in the key column are the same, so we pick one of them, and the words in the value column are different, so we append them to one of the words in the key column.

4

hi hope this helps

create data script

drop table if exists #Data
create table #Data (id int, key1 varchar(20) , value1 varchar(20))
insert into #Data values
(1,'book','book1'),
(2,'book','book2'),
(3,'book','book3'),
(4,'book','book4'),
(5,'pen','pen1'),
(6,'pen','pen2'),
(7,'notebook','notebook1'),
(8,'notebook','notebook2'),
(9,'notebook','notebook3')

1st way = using string_agg

SELECT
     min(id)
   , key1
   , string_agg(value1,',') 
FROM
   #Data 
GROUP BY
      key1 
ORDER BY
     min(id)

you want to update the table .. you have to be specific

any how

i deleted data from the table and inserted the new data into the table

drop table if exists #def 

select 
     min(id)  id 
   , key1
   , string_agg(value1,',') value1
into #def 
from 
   #Data 
group by 
      key1 
order by 
     min(id) 

delete from #Data

insert into #Data select * from #def

image
image1112×571 12 KB

5

Thank you, dear Harish Reddy, I am new to SQLite and I didn't understand the #def. Also, the string_agg() function was not recognized so I used group_concat() instead and it worked. Please can you write me a complete sample of this query (the 2nd one)? and help me with this problem. thank you.

6

sorry

did not understand what you mean ?

d. Please can you write me a complete sample of this query (the 2nd one) ? and help me with this problem. thank you.

7

Sorry Bro, I mean I don't know what is the #def and #data in your code snippet.
Can you write the complete query/code?
I really appreciate your help.

8

Hi

Those are temp tables

Temp tables are prefixed with #

Abc
Regular table

#Abc
Temp table

Both are tables
But #Abc exists only in the session and then gets dropped
Abc is a permanent table in the database