CODEWITHSUNDEEP
sqlsql-serverconditional-statementsconcat-ws
Phan Hien Vo
Phan Hien Vo

Reputation: 11

CONCAT_WS in SQL Server with condition

I want to create a View in SQL Server, there is new column in View name TotalApproval, that column concat string value of some columns in record with condition like code below, but the syntax is not right, please solve for me

CREATE VIEW WorkflowRequestViews AS
SELECT ApplicationNo, DescriptionNo, BelongSite as Site, 
        cast(FaxCode as nvarchar(50)) as FaxCode, 
        cast(MainApproval as nvarchar(100)) as MainApproval, 
        TotalStep, Process, UrgencyLevel, Complete, 
        CAST(CONCAT_WS(',', SectionMGR, DepartmentMGR, FactoryManager,
        CASE 
            WHEN BelongSite = 'FVB' THEN Director,ACCMGR 
            ELSE ACCPIC2
        END) as nvarchar(255)) as TotalApproval
FROM BusinessTrips

I want column TotalApproval is a string contain value which join from column I selected seperate by ',' with condition

SOLVE: Just concat value in THEN clause like code below:

CREATE VIEW WorkflowRequestViews AS
SELECT ApplicationNo, DescriptionNo, BelongSite as Site, 
        cast(FaxCode as nvarchar(50)) as FaxCode, 
        cast(MainApproval as nvarchar(100)) as MainApproval, 
        TotalStep, Process, UrgencyLevel, Complete, 
        CAST(CONCAT_WS(',', SectionMGR, DepartmentMGR, FactoryManager,
        CASE 
            WHEN BelongSite = 'FVB' THEN CONCAT_WS(',',Director,ACCMGR) 
            ELSE ACCPIC2
        END) as nvarchar(255)) as TotalApproval
FROM BusinessTrips

Upvotes: 0

Views: 102

Answers (1)

Charlieface
Charlieface

Reputation: 71193

You could use two CASE expressions, as CONCAT_WS ignores nulls

CAST(
  CONCAT_WS(',',
    SectionMGR,
    DepartmentMGR,
    FactoryManager,
    CASE WHEN BelongSite = 'FVB' THEN Director END,
    CASE WHEN BelongSite = 'FVB' THEN ACCMGR ELSE ACCPIC2 END
  )
  as nvarchar(255)
)

Upvotes: 1

Related Questions