Pivot with Temporary Table

Question

Hello. I am building a query where I need to use pivot to analyze the results, but it does not return the necessary data.

Original table:

    select * from tbCampoAdicionalTicket where TCK_SEQUENCIAL = 415945

I need the CAT_VALOR column to become a single row. However, I find some problems when creating the pivot:

1 - The column TCK_SEQUENCIAL exists through a foreign key, and I cannot call it in the pivot select.

2 - The CAT_VALOR column is of the VARCHAR type and I cannot convert it because there are cases with text.

Below, the closest I could create from the pivot, but the result returns a table with all NULL values:

SELECT  TCK_SEQUENCIAL, 
        [1] AS CAT_NUMERO_1, 
        [2] AS CAT_NUMERO_2, 
        [3] AS CAT_NUMERO_3, 
        [4] AS CAT_NUMERO_4 
FROM tbCampoAdicionalTicket
PIVOT(
    MAX(CAT_VALOR)
    FOR CAT_NUMERO IN (
    [1],
    [2],
    [3],
    [4])) AS pivot_table
WHERE TCK_SEQUENCIAL = 415948 

I would like the result to be similar to the photo, but with the column TCK_SEQUENCIAL together, because this table will be used in a previous:

From now on I thank you all!

Answer 1

I was able to solve the problem through the following script:

    SELECT DISTINCT MAX(pivot_table.TCK_SEQUENCIAL) AS TCK_SEQUENCIAL, 
                    MAX([1])                        AS CAT_NUMERO_1, 
                    MAX([2])                        AS CAT_NUMERO_2, 
                    MAX([3])                        AS CAT_NUMERO_3, 
                    MAX([4])                        AS CAT_NUMERO_4,

    FROM tbCampoAdicionalTicket CAT
                    PIVOT(MAX(CAT_VALOR) FOR CAT_NUMERO IN (
                    [1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) AS pivot_table

Answer 2

Hello you have here the solution,

SELECT  TCK_SEQUENCIAL, [1] AS CAT_NUMERO_1, 
        [2] AS CAT_NUMERO_2, 
        [3] AS CAT_NUMERO_3, 
        [4] AS CAT_NUMERO_4
FROM
(
    SELECT TCK_SEQUENCIAL, CAT_NUMERO, CAT_VALOR
    FROM tbCampoAdicionalTicket
) x
PIVOT(
    MAX(CAT_VALOR)
    FOR CAT_NUMERO IN (
    [1],
    [2],
    [3],
    [4])) AS pivot_table
WHERE TCK_SEQUENCIAL = 415948