Combine values from select statement into one field

  • 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

  • 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.
    ******************

  • 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