SQL server 2012 - convert sales order line data for knime market basket analysis

Question

I am trying to read a table in knime for market basket analysis. To do that I need sales order line data from SQL server 2012 in this format: A single column with space concatenated items. Example:

ordNo itemNo
x     a1
x     c2
y     a1
y     b4
y     r1

to the following:

col0
a1 c2
a1 b4 r1

Answer 1

You can use FOR XML clause :

select distinct stuff ((select distinct ' '+ t1.itemno
                        from table t1
                        where t1.ordno = t.ordno
                        for xml path('')
                       ), 1, 1, ''
                      ) as [col0]
from table t;