17 October 2009 09:29
tonyrogerson
Another row concatenation example
I had an email today wanting a solution to a row concatenation problem, for those who already know where this is going - yes, its FOR XML, anyway....
Consider the data source:
|
PRODUCT |
PROCESS |
TOP |
BOTTOM |
RIGHT |
LEFT |
|
|
|
|
|
|
|
|
GLASS 1 |
CUTTING |
|
|
|
|
|
GLASS 1 |
BEVELLING |
|
YES |
YES |
|
|
GLASS 1 |
GRINDING |
YES |
YES |
|
|
|
GLASS 1 |
POLISHING |
|
|
YES |
YES |
|
|
|
|
|
|
|
|
GLASS 2 |
CUTTING |
|
|
|
|
|
GLASS 2 |
BEVELLING |
|
YES |
YES |
|
|
GLASS 2 |
MILTERING |
|
|
|
YES |
|
GLASS 2 |
SAND BLASTING |
|
YES |
|
|
|
|
|
|
|
|
|
|
GLASS 3 |
CUTTING |
|
|
|
|
|
GLASS 3 |
POLISHING |
|
|
YES |
YES |
|
GLASS 3 |
TEMPERING |
|
|
|
|
You want the result:
|
PRODUCT |
PROCESS |
|
|
|
|
GLASS1 |
CUTTING/ BEVELLING (B,R)/ GRINDING (T,B)/ POLISHING (R,L) |
|
GLASS2 |
CUTTING/ BEVELLING (B,R)/ MILTERING (L)/ SAND BLASTING (B) |
|
GLASS3 |
CUTTING/ POLISHING (R,L)/ TEMPERING |
The FOR XML extension is the perfect tool for the job...
declare @input table (
Item varchar(50) not null,
Process varchar(50) not null,
P_Top char(1) null,
P_Bottom char(1) null,
P_Right char(1) null,
P_Left char(1) null
)
insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )
values( 'Blah', 'P1', null, null, null, null )
insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )
values( 'Blah', 'P2', null, 'Y', 'Y', null )
insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )
values( 'Blah', 'P3', 'Y', 'Y', null, null )
insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )
values( 'Blah', 'P4', null, null, 'Y', 'Y' )
insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )
values( 'Humbug', 'P1', null, null, null, null )
insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )
values( 'Humbug', 'P2', null, 'Y', 'Y', null )
insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )
values( 'Humbug', 'P3', 'Y', null, null, 'Y' )
insert @input ( Item, Process, P_Top, P_Bottom, P_Right, P_Left )
values( 'Humbug', 'P4', null, null, 'Y', 'Y' )
select *
from @input
select r.Item,
Concatenated = substring( replace( replace( r.Concatenated, ' ()', '' ), '(,', '(' ), 3, 4096 )
from (
select r.Item,
Concatenated = ( select '/ ' + Process + ' ('
+ case when P_Top = 'Y' then ',T' else '' end
+ case when P_Bottom = 'Y' then ',B' else '' end
+ case when P_Right = 'Y' then ',R' else '' end
+ case when P_Left = 'Y' then ',L' else '' end + ')' as [text()]
from @input i
where i.Item = r.Item
for xml path( '' ) )
from (
select Item
from @input
group by Item
) as r
) as r
Filed under: SQL Development