0

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
Raymond Anderson
  • 81
  • 2
  • 13

1 Answers1

2

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;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • Excellent. Thank you. It's a bit slow but I expected that. – Raymond Anderson Oct 15 '18 at 21:41
  • I also used select string = replace(replace(replace(' select single spaces',' ','<>'),'><',''),'<>',' ') from https://stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql to remove the excess spaces. – Raymond Anderson Oct 15 '18 at 21:42