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 Northwindgo
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 OrderListgo
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
