Select more values from DB into one field in DataSet

Question

i need to select more values from DB and place it into one field in dataset.

I have method in delphi, which have input parameter TDataSet and exports values from dataset to Excel.

One of field that i want to export is multiple values separated with "," from other table.

I have this tables:

KATALOG

ID_Katalog | atribut_1
----------------------
1          | xxx

KATALOGxPOLOZKA

ID_POLOZKA | ID_KATALOG
-----------------------
1          | 1
2          | 1
3          | 1

I need this result:

ID_Katalog | atribut_1 | polozka
--------------------------------
1          | xxx       | 1, 2, 3

Is there any possible way how can i do it please?

I tried subquery, but subquery isnt able to return more than 1 value.

P.S. : Im using - Delphi XE6, ADODB, MS SQL-SERVER.... result must be TDataSet

which query has that result? how can i select more values separated with "," in one field? — KubitaS, Nov 04 '15 at 11:26
refer this question http://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value — Arunprasanth K V, Nov 04 '15 at 11:29

score 2 · Accepted Answer · answered Nov 04 '15 at 11:29

You could use the FOR XML clause in a subselect to concatenate like so:

SELECT
      K.ID_Katalog,
      K.attribut_1,
      STUFF
      (
         (
            SELECT
                  ', ' + CAST(P.ID_POLOZKA AS varchar)
               FROM
                  KATALOGxPOLOZKA P
               WHERE
                  P.ID_KATALOG = K.ID_Katalog
               FOR XML PATH('')
         ), 1, 2, ''
      ) AS polozka
   FROM
      KATALOG K;

score -2 · Answer 2 · answered Nov 04 '15 at 11:32

-2

smth like:

recs:=sqlExecute(join the tables)
while not recs.eof() do
begin
  s:=recs.fields['join field from master table')
  dataset.append, and init other fields
  while (not recs.eof()) and s=recs.fields['join field from master table') do
  begin
    dataset.fields['concat field']:=dataset.fields['concat field'] + recs.field['polozka'] + ', ';
    recs.next;
  end;
  cut trailing comma
  dataset.post;  
end;

answered Nov 04 '15 at 11:32

Jur

92
6

its called pseudo-code.. giving general structure, for the asker to figure out exact syntax – Jur Nov 04 '15 at 11:54
so you have to mentioned that. otherwise it will be confusing, and why you are using while here ? – Arunprasanth K V Nov 04 '15 at 11:58
outer while is for processing all records, inner while is for keeping detail records together for the same master. – Jur Nov 04 '15 at 12:27
no need we can use COALESCE , or xml path() method – Arunprasanth K V Nov 04 '15 at 12:28

Select more values from DB into one field in DataSet

2 Answers2