I think this might be what you want:

SELECT pt.[imtPartID],
       COUNT(CASE WHEN pt.[imtPartID] = 1 THEN 'x' ELSE NULL END) AS Receipt,
...

I can't see that your query will work?

You are missing a bracket after PIVOT.

If you could supply your input data and the output expected it would be easier to understand.

CREATE TABLE [dbo].[tblClientService]
(
    [ID]          [int] IDENTITY(1,1) NOT NULL,
    [ClientID]   ...

Thanks Jeff,

Good so see the function is in use. It does make your code much shorter than other methods.

Data type disparity on join conditions etc is not really an after thought I've seen over time, its more like a 'no thought.' You bring up an excellent point...

I think the functions TRY_CAST, TRY_CONVERT and TRY_PARSE might be of use.

I think OUTER APPLY is the way to go.

Here is another way to write it, also using OUTER APPLY:

SELECT p.PatID, 
      ...

This should work

SELECT w2.RCWHS#                       ...

Another method

select *
  from #temp t
 where t.Branch = 'head quarters'
 union all
select *
  from #temp t
 where not exists(select *
    ...

;with cte as
(
    select distinct t.Id
      from #temp t
)
select t.*
  from cte x
 cross apply(select top(1) *
 ...

I would disagree with the normal way as using DelimitedSplit8K - if the goal is to split out to individual columns, using that utility would require a secondary pivot/cross-tab...

Well - you can avoid this issue using a slightly different approach.  I would create an iTVF to split the elements and then cross apply to that function.

            
October 4, 2021 at 6:20 pm
            
#3936368

            
            
                                                
        

I...

"It Depends".  I've found that a whole lot of folks end up writing code...

"It Depends".  I've found that a whole lot of folks end up writing code that causes...