SQL. PIVOT returning NULL values

Question

I've simple trying to convert rows to columns, but PIVOT returning NULL values. Before I've tried simple PIVOT, but unsuccessfully. For now I'm trying Dynamic PIVOT, but still the same result - NULL values.

Before pivot (how It is for now):

  1 test   -- Column alias name
----------
|  One   | -- Values which should be pivoted.
|  Two   | -- Values which should be pivoted.
|  Three | -- Values which should be pivoted.
|  Four  | -- Values which should be pivoted.
|  Five  | -- Values which should be pivoted.

After pivot (how It should be):

| Test1 | Test2 | Test3 | Test4 | Test5 | -- New columns alias names.
---------------------------------------------
|  One  |  Two  | Three | Four  | Five  | -- Pivoted values.

Here is my code:

  DECLARE   @sql    AS NVARCHAR(MAX),
            @cols   AS NVARCHAR(MAX) = ''

  -- Below selecting new column names
    SELECT @cols += '[' + Name1 + '],' + 
                    '[' + Name2 + '],' + 
                    '[' + Name3 + '],' + 
                    '[' + Name4 + '],' + 
                    '[' + Name5 + '],' 
    FROM   (SELECT  'Test1' AS Name1, 
                    'Test2' AS Name2, 
                    'Test3' AS Name3, 
                    'Test4' AS Name4, 
                    'Test5' AS Name5                        
            ) a 

    SET @cols   = LEFT(@cols, LEN(@cols) - 1)
    SET @sql    = N'SELECT * 
    FROM 
    (
    -- If I selecting only this subquery (select clause) It returning values in 1 column (as shown in 'Before Pivot'), which should be pivoted to row (as shown in 'After Pivot')
        SELECT DISTINCT TOP 5   NA.ItemNo           AS  [1 test]                                                
        FROM        FormI                           AS  FI
        INNER JOIN  FormF                           AS  FF
        ON          FF.FormVId  = FI.FormVId
        INNER JOIN  FormV                           AS  FormV
        ON          FI.FormVId  = FormV.Id
        INNER JOIN  NavArt                          AS  NA
        ON          FI.ArticleId    = NA.Id
        WHERE       FI.WorkShiftId  = 10
    ) x 
    PIVOT
    (
        MIN ([1 test])
        FOR [1 test] IN (' + @cols + ') 
    ) p'
    EXEC Sp_executesql @sql

For now this code returning only column names, but with NULL values. Like that:

| Test1 | Test2 | Test3 | Test4 | Test5 | -- New columns alias names.
---------------------------------------------
|  NULL |  NULL |  NULL |  NULL |  NULL | -- NULL instead of values

Have you ideas?

EDIT:

I've tried to change this

SET @sql = N'SELECT * FROM (...

To this:

SET @sql = N'SELECT ''TestValue'' AS Test1, * 
            FROM 
            (...

In this case It returning 1 more column Test1, but with value TestValue

|  Test1  | Test1 | Test2 | Test3 | Test4 | Test5 | -- 2x Test1 columns.
---------------------------------------------
|TestValue|  NULL |  NULL |  NULL |  NULL |  NULL | -- TestValue added

But I did this only for testing purpose, this is not good method for me, that because values are dynamic.

EDIT 2 For now my @cols looks like:

SELECT @cols += '[' + Name + '],' 
            FROM   (SELECT DISTINCT TOP 5   NA.ItemNo AS Name--, 
                            FROM        FormI                       AS  FI
                            INNER JOIN  FormF                       AS  FF
                            ON          FF.FormVId  = FI.FormVId
                            INNER JOIN  FormV                       AS  FormV
                            ON          FI.FormVId  = FormV.Id
                            INNER JOIN  NavArticles                 AS  NA
                            ON          FI.ArticleId        = NA.Id
                            WHERE       FI.WorkShiftId      = 10

                    ) a 

Answer 1

The values used in the IN clause in the pivot must match the column values you are pivoting. If you want to rename the columns you can do it in the select statement. Something like this should work:

SELECT 
    [One Test]   as [NameOne], 
    [Two Test]   as [NameTwo], 
    [Three Test] as [NameThree], 
    [Four Test]  as [NameFour]
FROM (
SELECT DISTINCT TOP 4 NA.ItemId AS  [1 test]
FROM        .....               AS  FI
    INNER JOIN  .....           AS  FF
    ON          FF..= FI..
    INNER JOIN  ....            AS  FormV
    ON          FI..= FormV..
    INNER JOIN  ....            AS  NA
    ON          FI..= NA.Id
    WHERE       FI..= 10
) a
PIVOT
(
MIN([1 test])
FOR [1 test] IN ([One Test], [Two Test], [Three Test], [Four Test])
) AS piv;