Merge cell contents with grouping on SQL View

Transact-SQL
1

Hi I have a table [table1] that contains a list of part numbers in column A, Operation No in column B and the machine we make them on in column C. What I am wanting to do is group on column A and merge the contents of column C with '[ ]' around each one

Part_No | Operation_No | Machine
123 | 20 | MT1
123 | 30 | MT2
123 | 40 | MT3
124 | 10 | MT4
124 | 20 | MT5

Result

123 | [MT1] [MT2] [MT3]
124 | [MT4] [MT5]

2

Welcome, what version of SQL Server do you have?

3

MS 2012, sorry thought I had tagged that in

4

oops sorry, you sure did

use sqlteam
go

declare @markrooker table(Part_No  int, 
Operation_No  int, 
Machine varchar(10))

insert into @markrooker
select 123 , 20 ,'MT1' union 
select 123 , 30 ,'MT2' union 
select 123 , 40 ,'MT3' union 
select 124 , 10 ,'MT4' union 
select 124 , 20 ,'MT5' 

select Part_No,  
STRING_AGG(concat('[', Machine, ']') ,' ') 

 from @markrooker
 group by Part_No
5

Hi it is a view I want to create to read only. The table contains thousands of records. I just want the view to group on column A (Part_No), Ignore Column B, and merge column C based on the grouping

6 
create view cookitup
as

select Part_No,  
          STRING_AGG(concat('[', Machine, ']') ,' ') as machines

 from your_table
 group by Part_No
7

says:
'STRING_AGG' is not a recognized built-in function name.

8

try this

select Part_No,  
    STUFF((SELECT concat('[',b.machine,'] ')
              FROM machines b
			  where a.Part_no = b.Part_no
			  ORDER BY b.machine
              FOR XML PATH('')),1,0,'') AS tag

 from machines a
 group by Part_No
9

Worked perfectly, many thanks

10

I would recommend adding TYPE and specifying the data type. For example:

select Part_No,  
    STUFF((SELECT concat('[',b.machine,'] ')
              FROM machines b
			  where a.Part_no = b.Part_no
			  ORDER BY b.machine
              FOR XML PATH(''), TYPE).value('(./text())[1]', 'varchar(8000)'),1,0,'') AS tag

 from machines a
 group by Part_No

Including the ./text and the specific data type will help improve performance on larger data sets.