1

I am trying to pivot into columns and I keep running into errors. I don't know what I am doing wrong. I tried the below code but I keep getting the red squiggly line under the "FOR" and the first word in the Parenthesis. Here is my code:

select d.City,d.Geographic_Region_Name, d.Site_Type
from Site_Profile as d
pivot
(City for Geographic_Region_Name in (City,Geographic_Region_Name,site_type) as pivotable;
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
user2089542
  • 11
  • 4

1 Answers1

2

Pivot is for turning aggregated rows into columns. From the documentation:

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>   FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>   PIVOT   (

The pertinent line:

<aggregation function>(<column being aggregated>)

and the rest

    FOR    [<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])   ) AS <alias for the pivot table>   
    <optional ORDER BY clause>;

You need to use an aggregate function (SUM, COUNT, MAX, MIN, etc) before the FOR.

pcdev
  • 2,852
  • 2
  • 23
  • 39
  • Without knowing more about what your data looks like and what you're trying to achieve, I can't really say which function you should use. – pcdev Jun 16 '17 at 05:16
  • Also see [this question](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – pcdev Jun 16 '17 at 05:17
  • Thank you so very much for the reply, pcdev. Much appreciated – user2089542 Jun 20 '17 at 16:26