Build a string that simulates an array? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Build a string that simulates an array?

Author

Topic

mmccardle
Starting Member

43 Posts

Posted - 2010-12-08 : 16:33:22

I need to build a string that contains a list of values from a table. There are some conditions but right now I just would like to know how I would get this information into an array of sorts. This is (very simply) what my table looks like:


Job   Operation   Work Center
123   10          I
123   15          MCL
123   16          MCL
123   20          LCM
123   30          OS
123   35          FI

What I would like is a string that lists these work centers separated by commas: "I, MCL, MCL, LCM, OS, FI". This is no key on this table that increments consistently (ie. in increments of 1, 5 or 10).

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-12-09 : 00:56:28

There are many ways to concatenate rows into a column, this is one:


declare @t table (Job int, operation int, WorkCenter varchar(3))
insert into	@t
	select 123,   10,          'I' union all
	select 123,   15,          'MCL' union all
	select 123,   16,          'MCL' union all
	select 123,   20,        'LCM' union all
	select 123,   30,         'OS' union all
	select 123,   35,          'FI' union all
	--
	select 456, 1, 'AA' union all
	select 456, 2, 'BB'


select	Job,
	max(stuff(c.a, 1, 1, ''))
from	@t t -- if you have a normalized table of Jobs use it here instead
cross
apply	(       select ',' +  WorkCenter 
	        from	@t i 
		where	i.Job = t.Job 
		for xml path('')
	) as c(a)
group
by	Job

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-12-09 : 01:00:59

Also see

http://www.sqlteam.com/article/converting-multiple-rows-into-a-csv-string-set-based-method

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Nathan Skerl

mmccardle
Starting Member

43 Posts

Posted - 2011-01-11 : 16:02:45

Thanks for the help nathans. I was actually updating a table variable, which made it a little more complicated, but here is my finished code.


UPDATE @report SET RemainingOps = t1.remaining
from
(
	select job, suffix, oper_num, max(stuff(c.a, 1, 2, '')) as remaining
	from	jobroute t
	cross
	apply	
		(select ', ' 
		 +  wc 
		 + CASE
			WHEN convert(nvarchar,(cast(i.qty_received - i.qty_moved - i.qty_scrapped as decimal (10,0)))) <> 0 THEN ('(' 
			+ convert(nvarchar,(cast(i.qty_received - i.qty_moved as decimal (10,0)))) 
			+ ')') ELSE '' END
	     from	jobroute i
		 where	i.Job = t.Job and i.suffix = t.suffix and i.complete = 0 and i.oper_num >= t.oper_num
		 for xml path('')
	    ) as c(a)
	group
	by	t.Job, t.Suffix, t.oper_num
) t1
INNER JOIN @report rpt ON rpt.ref = t1.job AND rpt.ref_line_suf = t1.suffix and rpt.ref_release = t1.oper_num

mmccardle
Starting Member

43 Posts

Posted - 2011-01-11 : 16:05:42

What it does is gather all of the "work centers" remaining on a work order, and lists them along with the qty of parts at that work center. The end result was something like this:


Job          Location
1234         I(34), LCM(21), LCM, K, FI
1235         MCS(50), G27, K, FI
1236         K(45), OS, FI

unfiresniper
Starting Member

5 Posts

Posted - 2011-01-11 : 22:11:00

Here is my test code, you can take this for example:
CREATE TABLE #temp (Job INT, Operation INT, Work_Center NVARCHAR(10))
INSERT INTO #temp values(123, 10 ,'I')
INSERT INTO #temp values(123, 15 ,'MCL')
INSERT INTO #temp values(123, 16 ,'MCL')
INSERT INTO #temp values(124, 20 ,'LCM')
INSERT INTO #temp values(124, 30 ,'OS')
INSERT INTO #temp values(124, 35 ,'FI')

--###########---Begin---#############--
SELECT t3.job, LEFT(t3.work_centers,LEN(t3.work_centers)-1)
FROM (SELECT DISTINCT job, work_centers = (SELECT Work_Center + ','
FROM #temp t2
WHERE t1.job = t2.job
FOR XML PATH(''))
FROM #temp t1) t3
--###########---End---#############--

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources