Search code examples
sql-serverdatabase-table

How do I create a table in SQL Server that stores multiple values for one cell?

Suppose I have a table for purchase orders. One customer might buy many products. I need to store all these products and their relevant prices in a single record, such as an invoice format.

Solution

  • If you can change the db design, Prefer to create another table called PO_products that has the PO_Id as the foreign key from the PurchaseOrder table. This would be more flexible and the right design for your requirement.

    If for some reason, you are hard pressed to store in a single cell (which I re-iterate is not a good design), you can make use of XMLType and store all of the products information as XML.

    Note: Besides being bad design, there is a significant performance cost of storing the data as XML.