Columns to Row in SQL

--Create sample table
create table samplePivot(
Name varchar(50),
itemValue int
)

--Insert sample record
insert into samplePivot(Name,itemValue) values('John',100)
insert into samplePivot(Name,itemValue) values('John',1000)
insert into samplePivot(Name,itemValue) values('John',2000)
insert into samplePivot(Name,itemValue) values('Smith',100)
insert into samplePivot(Name,itemValue) values('Smith',300)

--Pivot query for your result

DECLARE @MyColumns AS NVARCHAR(MAX),
    @SQLquery  AS NVARCHAR(MAX)
-- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot query
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(itemValue) 
                    FROM samplePivot
                    GROUP BY itemValue
                    ORDER BY itemValue
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
        select @MyColumns
        
-- here we use the above all Item name to disoplay its price as column and row display
set @SQLquery = N'SELECT Name,' + @MyColumns + N' from 
             (
                 SELECT 
       Name, 
        itemValue  as itemValue
    FROM samplePivot
            ) x
            pivot 
            (
                 sum(itemValue)
                for itemValue in (' + @MyColumns + N')
            ) p '
exec sp_executesql @SQLquery;