TSQL - Pivot without Aggregating Results

This is based on today's work. I had two tables Table A and Table B. I had to Pivot results of Table B and update the results. There were two more constraints

• Table B can Have 0 to 10 Records for Each Id in Table A
• We were using Entity Framework and Calling the Proc
• For this I would need to provide the result set (Fixed Schema)

Below is the solution approach

• For dynamic pivot this post was helpful to get started
• Created a dummy record with ten results to achieve Fixed Schema Results. This record would be deleted before sending results
• I was not aware till date how EF handling Stored Procs. This post was very useful as my proc returned error. Changed Setting to SET FMTONLY OFF based on the post

Getting to Actual Example 

--Step 1

IF OBJECT_ID('dbo.TableB') IS NOT NULL
    DROP TABLE dbo.TableB;

--Step 2

IF OBJECT_ID('dbo.TableA') IS NOT NULL
    DROP TABLE dbo.TableA;

CREATE TABLE TableA (
    Id   INT          IDENTITY (1, 1) PRIMARY KEY,
    NAME VARCHAR (50) NULL
);

CREATE TABLE TableB (
    Id       INT           NOT NULL,
    Comments VARCHAR (500) NOT NULL,
    FOREIGN KEY (Id) REFERENCES TableA (Id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

IF OBJECT_ID('dbo.TableC') IS NOT NULL

    DROP TABLE dbo.TableC;

IF OBJECT_ID('tempdb..##TestResults') IS NOT NULL

    DROP TABLE ##TestResults;

CREATE TABLE TableC (

    Id         INT          NULL,
    Comments   VARCHAR (50) NULL,
    [RowCount] INT          NULL
);

--Step 3 
INSERT  INTO [dbo].[TableA] ([NAME])
VALUES                     ('A'),
('B'),
('C');

INSERT  INTO [dbo].[TableB] ([Id], [Comments])

VALUES                     (1, 'A Comments'),
(1, 'A Second Comments'),
(1, 'A 3rd Comments'),
(2, 'B 1st Comments'),
(2, 'B 2nd Comments'),
(2, 'B 3rd Comments');

--Step 4
WITH   CTE ([Id], [Comments], [RowCount])
AS     (SELECT [T].[Id],
               [T].[Comments],
               row_number() OVER (PARTITION BY [Id] ORDER BY [Id] ASC)
        FROM   TABLEB AS T)
INSERT INTO TableC (Id, Comments, [RowCount])
SELECT [CA].[Id],
       [CA].[Comments],
       [CA].[RowCount]
FROM   [CTE] AS CA;

--Step 5, Insert Dummy Records
INSERT  INTO [dbo].[TableC] ([Id], [Comments], [RowCount])
VALUES                     (999999, 'Test', 1),
(999999, 'Test', 2),
(999999, 'Test', 3),
(999999, 'Test', 4),
(999999, 'Test', 5),
(999999, 'Test', 6),
(999999, 'Test', 7),
(999999, 'Test', 8),
(999999, 'Test', 9),
(999999, 'Test', 10);

DECLARE @sql AS NVARCHAR (MAX);

SET @sql = N'SELECT [ID]';

SELECT   @sql = @sql + ',MAX(CASE WHEN [RowCount] =' + CAST ([RowCount] AS CHAR (5)) + ' THEN [Comments] ELSE '''' END) AS [Col' + CAST ([RowCount] AS CHAR (5)) + ']'

FROM     [dbo].[TableC]
GROUP BY [dbo].[TableC].[RowCount]
ORDER BY [dbo].[TableC].[RowCount];

SET @sql = @sql + N'

                    INTO ##TestResults FROM  [dbo].[TableC]
                    GROUP BY  [dbo].[TableC].[Id]
                    ORDER BY  [dbo].[TableC].[Id]';

EXECUTE sp_executesql @sql;

DELETE ##TestResults

WHERE  [Id] = 999999;

SELECT *

FROM ##TestResults;

Please feel free to comment this post for any better approach to handle this scenario.

More Reads

Dumping SQL data in pivoted format
SQL query to convert columns into rows

Happy Learning!!!