Table Transform with SQL

Question

Normally when I deal with SQL (rarely) I only use SELECT * FROM statement all the time. My primary job is working with Excel as an analysis. However, I feel my efficiency can be much improved with programming, I have started to learn some programming (VBA for Excel). Today I want to do something more advantage which is trying to transform a table using Microsoft SQL like attached picture below.

SELECT Part_Number as [Part Number], SubPart, Quantity FROM....

Bascially, the Part Number can be as many as 200, SubPart only has 3 type Sub-I, Sub-II, and Sub-III, Quantity can be anything. I need some help to transform the table as shown

Answer 1

Write dynamic T-Sql query as:

DECLARE @columns NVARCHAR(MAX)      
       ,@sql NVARCHAR(MAX)

SET @columns = N''
--Get column names for entire pivoting
SELECT @columns += N', ' + QUOTENAME(SpreadCol)
  FROM (select distinct Part_Number as SpreadCol 
        from test
       ) AS T
--select @columns
SET @sql = N'
SELECT SubPart, ' + STUFF(@columns, 1, 2, '') + '
FROM
(select SubPart , Part_Number as SpreadCol ,  Quantity
from test ) as D
PIVOT
(
  sum(Quantity) FOR SpreadCol IN ('
  + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
  + ')
) AS Pivot1
'
--select @sql

EXEC sp_executesql @sql

Check Fiddle Demo here..

Answer 2

It seems you need to use Pivoting.

---

Refer to:

Using PIVOT and UNPIVOT

Simple Way To Use Pivot In SQL Query