May 3, 2005 at 5:18 pm
Hi all,
I have this stored procedure where now I want to combine some of the values from the select statement into one field separated by commas. The values I want to combine are servicereq2 + servicereq3 +, etc., but I want all the other fields, too.
CREATE PROCEDURE MonthlyComplianceServiceOrdertest @iYear nvarchar (4) ,@sMonth varchar (4)
AS
sELECT case when
dbo.tblCompliance.MechLDDue is not null then 'Leak Detectors' else null end as servicereq2,CASE WHEN dbo.tblCompliance.ElectLDDue IS NOT
NULL THEN 'Leak Detectors' ELSE NULL END AS ServiceReq3, CASE WHEN dbo.tblCompliance.LineTestDue IS NOT NULL THEN 'Line Test'
ELSE NULL END AS ServiceReq4,CASE WHEN dbo.tblCompliance.TankTestDue IS NOT NULL THEN 'Tank Test' ELSE NULL END AS
ServiceReq5,CASE WHEN dbo.tblCompliance.CathodicTestDue IS NOT NULL THEN 'Cathodic Test' ELSE NULL END AS ServiceReq6,
dbo.tblCustomers.Name,dbo.tblCustomers.Address, dbo.tblCustomers.City, dbo.tblCustomers.St, dbo.tblCustomers.Zip,
dbo.tblLocations.LocNum, dbo.tblLocations.LocName, dbo.tblLocations.LocAdd1, dbo.tbllocations.LocCity,
dbo.tblLocations.LocSt, dbo.tblLocations.LocZip,dbo.tblLocations.LocPhone, dbo.tblLocations.County_COD, dbo.tblLocations.LocFacID
FROM dbo.tblCompliance INNER JOIN
dbo.tblCustomers ON dbo.tblCompliance.CustIndex = dbo.tblCustomers.CustIndex INNER JOIN
dbo.tblLocations ON dbo.tblCompliance.LocIndex = dbo.tblLocations.LocIndex AND dbo.tblCustomers.CustIndex = dbo.tblLocations.CustIndex
WHERE ( ( dbo.tblCompliance.MechLDDue LIKE @sMonth) and (datepart(year,dbo.tblCompliance.MechLDDue)=@iyear)) or
( (dbo.tblCompliance.ElectLDDue LIKE @sMonth ) and (datepart(year,dbo.tblCompliance.ElectLDDue)=@iyear ))or
( (dbo.tblCompliance.LineTestDue LIKE @sMonth) and (datepart(year,dbo.tblCompliance.LineTestDue)=@iyear))or
( (dbo.tblCompliance.TankTestDue LIKE @sMonth) and (datepart(year,dbo.tblCompliance.TankTestDue)=@iyear))or
( (dbo.tblCompliance.CathodicTestDue LIKE @sMonth) and (datepart(year,dbo.tblCompliance.CathodicTestDue)=@iyear))
ORDER BY dbo.tblLocations.LocName
GO
Any idea would be appreciated.
Thanks in advance.
Lisa
May 3, 2005 at 9:43 pm
Usually when you want to concatenate columns you can do
SELECT
col1 + col2 + col3 AS OneColumn
FROM
etc.
I dont know if it would work with CASE. I would try checking it out..
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
May 6, 2005 at 8:05 am
Thanks Dinakar,
This seems to work. I had to add the servicereq column to the table.
CREATE PROCEDURE MonthlyComplianceServiceOrdertest @iYear nvarchar (4) ,@sMonth varchar (4)
AS
SELECT (CASE WHEN dbo.tblCompliance.MechLDDue IS NOT NULL THEN 'Leak Detectors,' ELSE' ' END + ' ' + CASE WHEN
dbo.tblCompliance.ElectLDDue IS NOT NULL THEN 'Leak Detectors,' ELSE ' 'END+ ' ' + CASE WHEN dbo.tblCompliance.LineTestDue
IS NOT NULL THEN 'Line Test,' ELSE ' ' END + ' ' + CASE WHEN dbo.tblCompliance.TankTestDue IS NOT NULL THEN 'Tank Test,' ELSE
' ' END + ' ' + CASE WHEN dbo.tblCompliance.CathodicTestDue IS NOT NULL THEN 'Cathodic Test,' ELSE ' ' END) as servicereq ,
dbo.tblCustomers.Name,dbo.tblCustomers.Address, dbo.tblCustomers.City, dbo.tblCustomers.St, dbo.tblCustomers.Zip,
dbo.tblLocations.LocNum, dbo.tblLocations.LocName, dbo.tblLocations.LocAdd1, dbo.tbllocations.LocCity,
dbo.tblLocations.LocSt, dbo.tblLocations.LocZip,dbo.tblLocations.LocPhone, dbo.tblLocations.County_COD, dbo.tblLocations.LocFacID
FROM dbo.tblCompliance INNER JOIN
dbo.tblCustomers ON dbo.tblCompliance.CustIndex = dbo.tblCustomers.CustIndex INNER JOIN
dbo.tblLocations ON dbo.tblCompliance.LocIndex = dbo.tblLocations.LocIndex AND dbo.tblCustomers.CustIndex = dbo.tblLocations.CustIndex
WHERE ( ( dbo.tblCompliance.MechLDDue LIKE @sMonth) and (datepart(year,dbo.tblCompliance.MechLDDue)=@iyear)) or
( (dbo.tblCompliance.ElectLDDue LIKE @sMonth ) and (datepart(year,dbo.tblCompliance.ElectLDDue)=@iyear ))or
( (dbo.tblCompliance.LineTestDue LIKE @sMonth) and (datepart(year,dbo.tblCompliance.LineTestDue)=@iyear))or
( (dbo.tblCompliance.TankTestDue LIKE @sMonth) and (datepart(year,dbo.tblCompliance.TankTestDue)=@iyear))or
( (dbo.tblCompliance.CathodicTestDue LIKE @sMonth) and (datepart(year,dbo.tblCompliance.CathodicTestDue)=@iyear))
ORDER BY dbo.tblLocations.LocName
GO
Have a great day
Lisa
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy