Create an array in a result field

I am between the "newbie" and "intermediate" stages of writing SQL code and I am wondering if there is a way to capture multiple results into one field so I can basically create a "set" for a unique identifier. Here is few result samples I receive from this code I am using now.

ReqNo ProcID

7102005 1409

7102005 1796

7139003 1411

7139003 6097

7261030 1409

7261030 1796

7268303 3998

7268303 4000

I would like to create a single row for each "ReqNo" and have a field that will an array of the "ProcID" results I receive. In other words, for the first "ReqNo" 7102005, can I create a field that will combine the 1409, 1796 into one field? I am trying to capture an array of integers used for that "ReqNo" so I can use that as a unique identifier in a join for another table.

So, ideally my result would be:

ReqNo ProcSet

7102005 1409, 1796

7139003 1411, 6097

7261030 1409, 1796

7268303 3998, 4000

Is this possible?

declare

@.startdate smalldatetime,

@.enddate smalldatetime ,

@.month int,

@.year int

select

@.startdate = dateadd (dd, -7, getdate())

SELECT

@.month = datepart (month, @.startdate),

@.year = datepart (year, @.startdate)

SELECT

@.startdate = convert (smalldatetime, convert(varchar(2), @.month) + "/1/" + convert (varchar(4), @.year))

select

@.enddate = dateadd (dd, 1 , @.startdate)

select distinct

pp_req_no as ReqNo,

pp_cproc_id_r as ProcID

from

risdb_rch08_stag..performed_procedure

(index pp_serv_time_r_ndx)

where

pp_service_time_r between @.Startdate and @.Enddate

and pp_status_v = 'CP'

and pp_rep_id > 0

order by

pp_req_no, pp_cproc_id_r

You could create a function that would concatenate the fields together and return them as a string. This is not really a recommended practice, but can be occassionally useful for presentation. One trick is to use a local variable to accumulate the results. Of course, your function would be specific to this table.

I am not up on 2005 features, but it seems like there is a new unpivot operator that might work.

This demonstrates how to do the accumulation into a local variable.

use Northwind

go

Declare @.result varchar(1000)

Select @.result = Case When @.result Is Not Null Then @.result + ', ' Else '' End + Convert(varchar(10),OrderID)

From Orders

Where CustomerID = 'ALFKI'

Select @.result|||

Confused?

So are you saying that this syntax @.result + ', ' will build the results in the field? This looks like it will concatenate one result followed by the OrderID in your example (123456, 99999).

How will that build multiple results in one field? I understand how to declare and select, but this does not seem to make sense to me. I will be the first to admit if I am missing something here...which could very well be the case. Bear with me.

|||

Hi,

SELECT t3.ReqNo, MAX(case t3.seq when 1 then t3.ProcID end)

+ MAX(case t3.seq when 2 then ', ' + t3.ProcID else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.ProcID else '' end) AS ProcID

FROM ( SELECT ReqNo, ProcID, (SELECT COUNT(*) FROM yourTable AS t2 WHERE t2.ReqNo = t1.ReqNo and t2.ProcID <= t1.ProcID) AS seq

FROM yourTable AS t1

) as t3

GROUP BY t3.ReqNo

Or you can use this for SQL Server 2005:

SELECT t3.ReqNo, t3.[1] + coalesce(', ' + t3.[2], '') + coalesce(', ' + t3.[3], '') AS ProcID

FROM (SELECT ProcID, ReqNo, ROW_NUMBER() OVER(PARTITION BY ReqNo ORDER BY ProcID) AS seq FROM yourTable) as t1

PIVOT (MAX(ProcID) for seq in ([1], [2], [3])) AS t3

Or using CTE in SQL Server 2005:

With MyCTE(ReqNo, ProcID, ProcIDs, myNum) AS

(SELECT a.ReqNo, CONVERT(varchar(50), MIN(a.ProcID)) as col1, CONVERT(varchar(50),(a.ProcID)) as ProcIDs, 1 as myNum

FROM yourTable a GROUP BY a.ReqNo, CONVERT(varchar(50),(a.ProcID))

UNION ALL

SELECT b.ReqNo, CONVERT(varchar(50), b.ProcID), CONVERT(varchar(50), (c.ProcIDs + ',' + b.ProcID)), c.myNum+1 as myNum

FROM yourTable b INNER JOIN MyCTE c ON b.ReqNo=c.ReqNo

WHERE b.ProcID>c.ProcID

)

SELECT a.ReqNo, ProcIDs FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.ReqNo FROM MyCTE a1

group by a1.ReqNo) b on a.ReqNo=b.ReqNo AND a.myNum= b.myNumMax ORDER BY a.ReqNo

|||

Sorry that I wasn't very clear.

You would build a User Defined Function that would be specific to the table you are working with and would accept a parameter that would identify the records -- like the customerID in the Northwind case. The function would concatenate the values into the local variable and return that variable. You could then call this function as part of your Select clause.

Performance would likely be a challenge.

Here is a NorthWind example

Drop Function OrderList

go

Create Function OrderList( @.cust varchar(10) )

Returns varchar(1000)

As

Begin

Declare @.result varchar(1000)

Select@.result =

Case When @.result Is Not Null

Then @.result + ', '

Else ''

End + Convert(varchar(10), OrderID )

FromOrders

WhereCustomerId = @.cust

return @.result

End

go

SelectCustomerID,

CompanyName,

dbo.OrderList( CustomerID )

FromCustomers