3

I need to merge this individual rows to one column, I now how to merge column by comma separated, 

+---------------+-------+-------+
|CID            |Flag   |Value  |
+---------------+-------+-------+
|1              |F      |10     |
|1              |N      |20     |
|2              |F      |12     |
|2              |N      |23     |
|2              |F      |14     |
|3              |N      |21     |
|3              |N      |22     |
+---------------+-------+-------+

Desired Result can be anything,

+-----------+----------------------------+    +--------------------------+
|Part Number| Value                      |    | Value                    |   
+-----------+----------------------------+    +--------------------------+
| 1         | 1|F|10 ; 1|N|20            | Or | 1|F|10 ; 1|N|20          |
| 2         | 2|F|12 ; 2|N|23 ; 2|F|14   |    | 2|F|12 ; 2|N|23 ; 2|F|14 |
| 3         | 3|N|21 ; 3|N|22            |    | 3|N|21 ; 3|N|22          |
+-----------+----------------------------+    +--------------------------+

Note:

Any hint in right direction with small example is more than enough

EDIT : I have massive data in tables like thousands of records where parent's and child relationship is present. I have to dump this into text files by comma separated values In single line as record. Think as primary record has relationship with so many other table then all this record has to be printed as a big line.

And I am trying to achieve by creating query so load can be distributed on database and only thing i have to worry about in business is just dumping logic into text files or whatever form we need in future.

JBaba
  • 590
  • 10
  • 30
  • could you explain the reason behind trying to get this representation? – Vamsi Prabhala Sep 16 '15 at 20:12
  • @vkp look for explanation – JBaba Sep 16 '15 at 20:19
  • It appears that in DB2 you have create a custom function. Look [here](http://stackoverflow.com/questions/14851025/how-to-concatenate-multiple-rows-inside-a-single-row-in-sql) for an example to get you started. – jradich1234 Sep 16 '15 at 20:21
  • 1
    possible duplicate of [DB2 comma separated output](http://stackoverflow.com/questions/7188542/db2-comma-separated-output) – jradich1234 Sep 16 '15 at 20:24
  • @jradich1234 merging single column can be achieved but merging entire row that's what i want – JBaba Sep 16 '15 at 20:24
  • Whats is the Database: Sql Server? – Arash R Sep 16 '15 at 20:48
  • What DB2 version and platform? – mustaccio Sep 16 '15 at 23:11
  • @JBaba - merging multiple columns from a single row is a subproblem of merging the rows, so the techniques still work regardless. Note that there's no way to guarantee the ordering of children at all. Why are you supposed to output them as a single row? If you're doing import/export of data, you're going to find that working with non-concatenated data will make your life a lot easier; a number of systems will probably squawk at you if you give it varying-length records. – Clockwork-Muse Sep 20 '15 at 01:00

1 Answers1

1

You can try to use LISTAGG and your query will look like this:

select a.cid, a.cid || listagg(a.flag || '|' || a.value, ',') 
from foo.dat a
group by a.cid

You can use different separators and of course play with how the result will be formatted.

data_henrik
  • 16,724
  • 2
  • 28
  • 49