# 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

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.

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

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

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
``````

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)
``````