Pivot using a dynamic query in SQL Server

Question

Script

DECLARE @Names AS TABLE(ID INT, Name VARCHAR(MAX))

INSERT INTO @Names 
VALUES (1, 'Paul'), (2, 'John'), (3, 'Tayler');

DECLARE @PersonInfo AS TABLE 
                       (
                           ID INT,
                           NameID INT,
                           Subject VARCHAR(100),
                           Marks FLOAT
                       )

INSERT INTO @PersonInfo 
VALUES (1, 1, 'Maths', 95.34), (2, 2, 'Science', 32.12),
       (3, 3, 'History', 23.21), (4, 2, 'Maths', 32.4),
       (5, 3, 'Science', 60.34), (6, 1, 'Music', 60.23);

SELECT * FROM @PersonInfo

I have tried to generate the dynamic columns using PIVOT expression, but no luck. Can anyone give me an idea or the solution to generate the below output. Thanks in advance

Expected solution:

Subject     Paul    John    Tayler
-----------------------------------
Maths       95.34   32.4    
Science             32.12   60.34
History                     23.21
Music       60.23

MJoy · Accepted Answer · 2019-06-10 06:10:08Z

You could use dynamic PIVOT to achieve the result.

Since table variables are isolated from the scope of the dynamic SQL, it's not visible inside the dynamic SQL., hence I've used temp tables (their scoping rules are different). Please note table variable can also be used in this case, but I prefer using temp tables.

CREATE TABLE #Names (ID INT,Name VARCHAR(MAX))
CREATE TABLE #PersonInfo (ID INT,NameID INT,Subject VARCHAR(100),Marks FLOAT)

INSERT INTO #Names VALUES (1,'Paul');
INSERT INTO #Names VALUES (2,'John');
INSERT INTO #Names VALUES (3,'Tayler');

INSERT INTO #PersonInfo VALUES (1,1,'Maths',95.34);
INSERT INTO #PersonInfo VALUES (2,2,'Science',32.12);
INSERT INTO #PersonInfo VALUES (3,3,'History',23.21);
INSERT INTO #PersonInfo VALUES (4,2,'Maths',32.4);
INSERT INTO #PersonInfo VALUES (5,3,'Science',60.34);
INSERT INTO #PersonInfo VALUES (6,1,'Music',60.23);

DECLARE @DynamicCols NVARCHAR(MAX) = '';
DECLARE @pvt NVARCHAR(MAX) = '';
DECLARE @SQLQuery NVARCHAR(MAX) = '';

SELECT @DynamicCols +=  ', SUM(' +QUOTENAME([Name])+') AS '+[Name] FROM #Names;
SET @DynamicCols = STUFF(@DynamicCols,1,1,'')
SELECT @pvt +=  ', ' +QUOTENAME([Name]) FROM #Names;
SET @pvt = STUFF(@pvt,1,1,'')

EXEC ('
SELECT [Subject],' + @DynamicCols+'
FROM (SELECT [NameID], [Subject], [Marks] FROM #PersonInfo) a
INNER JOIN #Names b ON a.NameID = b.ID
PIVOT 
(
  SUM([Marks])
  FOR [Name] IN ('+ @pvt+')
) PIV
GROUP BY [Subject]');

Arulkumar · Accepted Answer · 2019-06-10 05:04:09Z

2

Using PIVOT you can achieve your expected result:

;WITH Result AS (
   SELECT
      P.Subject, N.Name, P.Marks
   FROM @PersonInfo P
   INNER JOIN @Names N ON N.ID = P.NameID 
)
SELECT *
FROM Result
PIVOT (Max(Marks) FOR Name IN (Paul, John, Tayler)) R

Demo on db<>fiddle

answered Jun 10, 2019 at 5:04

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

Collectives™ on Stack Overflow

Pivot using a dynamic query in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related