Pivot multiple columns with a column value repeats for each new column

Question

I need your help to build an sql query/sp for the following output.

My table is with the following data:

I would like to generate output like the following:

Kindly note, here, FieldName are not just four as in the table, it varies.

Find below the data:

CREATE TABLE #Results
(
    FieldName  nvarchar(50),
    FieldValue  nvarchar(50),
    RecordStaus int
); 

INSERT INTO #Results(FieldName,FieldValue,RecordStaus)
VALUES ('Coverage',NULL,1)
      ,('Premium',NULL,2)
      ,('F1',100,1)
      ,('F2',100,1)
      ,('Coverage',200,1)
      ,('Premium',10,1)
      ,('F1',50,1)
      ,('F2',NULL,3)
      ,('Coverage',300,1)
      ,('Premium',45,1)
      ,('F1',24,1)
      ,('F2',NULL,1)
      ,('Coverage',450,3)
      ,('Premium',12,3)
      ,('F1',50,1)
      ,('F2',NULL,1);

Answer 1

You can try this:

CREATE TABLE #Results
(
id int identity(1,1),
FieldName  nvarchar(50),
FieldValue  nvarchar(50),
RecordStaus int
); 

INSERT INTO #Results(FieldName,FieldValue,RecordStaus)
VALUES ('Coverage',NULL,1)
,('Premium',NULL,2)
,('F1',100,1)
,('F2',100,1)
,('Coverage',200,1)
,('Premium',10,1)
,('F1',50,1)
,('F2',NULL,3)
,('Coverage',300,1)
,('Premium',45,1)
,('F1',24,1)
,('F2',NULL,1)
,('Coverage',450,3)
,('Premium',12,3)
,('F1',50,1)
,('F2',NULL,1);


DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@Columns NVARCHAR(MAX);


SELECT @Columns = STUFF
(
    (

        SELECT *
        FROM
        (
            SELECT DISTINCT ',[' + CAST([FieldName] AS NVARCHAR(50)) + ']'
            FROM #Results
            UNION
            SELECT DISTINCT ',[' + CAST([FieldName] + '_RecordStaus' AS NVARCHAR(50)) + ']'
            FROM #Results
        ) DS ([FieldName])
        FOR XML PATH(''), TYPE

    ).value('.', 'VARCHAR(MAX)')
    ,1
    ,1
    ,''
);


SET @DynamicTSQLStatement = N'
SELECT *
FROM
(
    SELECT [FieldName]
           + CASE WHEN [Column] = ''RecordStaus'' THEN ''_RecordStaus'' ELSE '''' END AS [FieldName]
          ,[rowID]
          ,[Value]
    FROM 
    ( 
        SELECT [FieldName]
              ,[FieldValue]
              ,CAST([RecordStaus] AS NVARCHAR(50))
              ,ROW_NUMBER() OVER (PARTITION BY [FieldName] ORDER BY [id]) 
        FROM #Results
    ) DS ([FieldName], [FieldValue], [RecordStaus], [rowID])
    UNPIVOT
    (
        [Value] FOR [Column] IN ([FieldValue], [RecordStaus])
    ) UNPVT
) ReadyForPivot
PIVOT
(
    MAX([Value]) FOR [FieldName] IN (' + @Columns +')
) PVT;
';

EXEC sp_executesql @DynamicTSQLStatement;

DROP TABLE #Results;

Few notes:

• I have added id column in order to know the value for which row / in your real case you can use ordering by something else or SELECT 1 in the ROW_NUMBER function; you need such way in order to be sure the results are deterministic;

• I am using dynamic SQL in order to make the query work for various values of FildName column - if you need specific order of the columns, you can do this using ORDER BY clause in the FOR XML clause. For example:

  SELECT @Columns = STUFF
  (
      (
  
          SELECT *
          FROM
          (
              SELECT DISTINCT ',[' + CAST([FieldName] AS NVARCHAR(50)) + ']'
              FROM #Results
              UNION
              SELECT DISTINCT ',[' + CAST([FieldName] + '_RecordStaus' AS NVARCHAR(50)) + ']'
              FROM #Results
          ) DS ([FieldName])
          ORDER BY [FieldName] DESC -- you can order the columns as you like
          FOR XML PATH(''), TYPE
  
      ).value('.', 'VARCHAR(MAX)')
      ,1
      ,1
      ,''
  );
  

  Then add the @columns variable value in the dynamic SQL:

  SET @DynamicTSQLStatement = N'
  SELECT' + @columns + ' ...