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

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

Collectives™ on Stack Overflow

Pivot using a dynamic query in SQL Server

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related