How do i achieve this pivot task

Transact-SQL
1

Question 5

Instructions

100 XP

Convert the following query to be pivoted, using PIVOT() .

SELECT CategoryID, AVG(UnitPrice)
FROM Products
GROUP BY CategoryID;

Your result set should look like this:

Per Category 1 2 3 4 5 6 7 8
Average Unit Price 37.98 23.06 25.16 28.73 20.25 54.01 32.37 20.68

the products table looks like this

CategoryID
1 37.9791
2 23.0625
3 25.1600
4 28.7300
5 20.2500
6 54.0066
7 32.3700
8 20.6825
2

Have a look at this
http://www.nigelrivett.net/SQLTsql/Pivot_Statement.html

Sounds a bit like you're trying to get the answer to a question given to you rather than learning how to use the function.

3

i actually know how to use the pivot function...but i have never tried turning integers to column names

4

You just need to to encapsulate the integer values with
So
for CategoryID in [1],[2],[3],...

5

hi

i am trying to do this !!

please click arrow to the left for Drop Create SAMPLE Data 
drop table #data
go 

create table #data 
(
category_id int ,
Average_unit_price float
)
go 

insert into #data select 1,	37.9791
insert into #data select 2,	23.0625
insert into #data select 3,	25.1600
insert into #data select 4,	28.7300
insert into #data select 5,	20.2500
insert into #data select 6,	54.0066
insert into #data select 7,	32.3700
insert into #data select 8,	20.6825
go 

select * from #data
go

image

6
please click arrow to the left for Dynamic PIVOT 
DECLARE @cols   AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category_id) FROM #data c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')


set @query = 'SELECT ''Average Unit Price'', ' + @cols + ' from 
            (
                select   category_id
                       , Average_unit_price
                from 
				    #data
           ) x
            pivot 
            (
                 max(Average_unit_price) for category_id in (' + @cols + ')
            ) p '


execute(@query)

image