This can be achieved using a Cross-Tab query.

Static and Dynamic cross-tab queries are dealt with expertly and in great detail in these two Jeff Moden articles:

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

            
August 18, 2022 at 5:07 pm
            
#4078039

            
            
                                                
        

DOUBLE is not a valid data type in SQL

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

Looking at this from a different angle ....

If you click on the upper left corner, it highlights all the cells.  That way you wont have a highlighted cell in the...

Try this

WHEN LTRIM(S1UPSP) LIKE 'RIOUS%' THEN '0'

or this

WHEN PATINDEX( '%RIOUS%', S1UPSP) > 0 THEN '0'

Is it possible that leading "spaces" are not char(32), but...

Something like this ...

CASE
  WHEN CHARINDEX(' ', S1UPSP) > 0 THEN '0' /* S1UPSP Contains spaces */
  WHEN LEN(S1UPSP) = 10 THEN RIGHT(S1UPSP, 8) /*...

You can read THIS ARTICLE on Splitter functions by Jeff Moden.

At the end of the article, you will find a link "The New Splitter Functions.zip" that will...

Perhaps a small tweak to Piet's 1st answer

SELECT src.*, v.val
FROM #t1 AS src
CROSS APPLY (SELECT val = COUNT(DISTINCT(NULLIF(RTRIM(x.val), '')))
        ...

I believe that this can be simplified as follows

CREATE TABLE #parts ( PartNumber   varchar(50)
           ...

I stand to be corrected, but the pattern appears to be as follows

• P1 is the only PartNumber that has PartValue in (1,2,3,4), therefor has its own UniqueNumber
• P2 and...

I gave you an example of how to use the OUTPUT clause, leaving you to modify it to work with your MERGE statement.

These links should help you further

Doing this with a simple UPDATE, you can construct an output as follows

CREATE TABLE #Employee (
    Emp       int...

I call SPAM ...  Setup and Response

You can't embed the varchar @BookId directly into your query.

You can make your whole query dynamic and embed @BookId into it.  NOTE: This is very dangerous.

You can shred your @BookId...

As a 1st guess, I would think that either C2.ts_primarysecondaryfocus or C2.ts_ukrow has a BIGINT data type.

If that is not the case, then start by commenting all of the fields...

You can use PATINDEX to find the a=(any 3 integers), then use STUFF to replace the (any 3 integers) with 999

DECLARE @x table (y varchar(50));

INSERT INTO @x...