How to insert list of data to single column row in Azure SQL/SQL Server?-CodePudding

I have tables like this:

Select CustomerName, CustomerSales, SalesDate 
From Customer
Select Month, WeeklyMessage 
From WeeklyReport

I would like to insert list of CustomerName and Sales to single row of WeeklyMessage like:

Tesla 1200 000\n
Toyota 500 000\n

I would next use this row for monthly notification.

Is this possible? What is Insert query that is able to store list to single row?

CodePudding user response：

You could store it as a json.

This query results a json formatted string:

select CustomerName, CustomerSales from Customer for json auto

That could be used to store the values into your WeeklyReport table. You can query json and get its values as explained in this MS doc JSON data in SQL Server.

CodePudding user response：

i dont know why you want to insert into the same colum the 2 values (name and number), i think you can do it simple if you do a relation with CustomerName and Sales.

create table WeeklyMessage ( name varchar(30), number integer, FOREIGN KEY (name) REFERENCES CustomerName(name), FOREIGN KEY (number) REFERENCES Sales(number), PRIMARY KEY (name,number) );

and then insert it:

insert into WeeklyMessage values (Tesla, 1200 000);