Is it possible to insert multiple items to one row and get them back one by one

Question

I am using this Sql command

INSERT INTO SAMPLES (reportno, samplename, analysisname) 
       VALUES (1, 'Lemon', 'ecobali1,ecobali2,ecobali3)

My structure will be :

REPORT NO : 1
   SAMPLE : LEMON

   ANALYSIS

   Ecobali1

   Ecobali2

   Ecobali3

Later on I need to get analysisnames one by one to enter details;

SELECT * FROM SAMPLES WHERE reportno =1

My question is:

Is it possible to develop such a structure?

I am stuck here since I need to add multiple values to one row and get them one by one

Can I use TVP here and how?

Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). BTW, it's "Thanks in advance", not "Thanks in advanced". — John Saunders, May 21 '15 at 06:14

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

It is not a great practice to try to store multiple values in one row of your table. You should be just fine having a single set of values for each row because storage is cheap. If you want to have a single insert statement, you can insert multiple rows by adding to your VALUES clause. I found instructions here and have copied the example below:

INSERT INTO Table ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 )

edited May 23 '17 at 11:51

Community

1
1

answered May 21 '15 at 01:55

Cory

783
5
12

first of thanks for the reply. My concern is here that how can I be sure that I am getting correct value after adding. I successfully added such data here ok. Later on I need to get value1 value2 one by one how I can I be sure that I am getting correct value – Selçuk AYDIN May 21 '15 at 02:01
The point here is that your table structure is incorrect. You need one table for the report and another, separate one for the analysis. You might actually need more than that, but hopefully you get the general concept. – LoztInSpace May 21 '15 at 02:12
Yea, I guess I'm not understanding exactly what you're trying to accomplish here? – Cory May 21 '15 at 02:16
@Cory Let me try to prepare detailed structure and share with you . Do you have any mail address so I can share my expectation with you – Selçuk AYDIN May 21 '15 at 02:24

score 0 · Answer 2 · answered May 21 '15 at 02:20

Yes, you can save your data in such flatted structure in a single table, but not in a good way.

Adding efforts to manipulate analysisname
Hard to analysis the data in SQL query

You should create a Master and Detail tables, in which the detail table store analysisname

score 0 · Answer 3 · answered May 21 '15 at 02:40

what i saw is, you should create another table named as analysis

analysis((primary)analysisID,analysisname,analysisdesc);
SAMPLES((primary)reportNo,samplename,analysisID(foreign));

this two table will create a relation one sample with zero or more analysis

Is it possible to insert multiple items to one row and get them back one by one

3 Answers3