I'm basically using php with access to remote SQL server, and then formatting in JSON to display on a iPhone app. I'cant really see an easy way to dump the database as its not managed by me. – Simon Unsworth SQL Server doesn't allow for pivoting on multiple columns, I'd actually suggest looking at unpivoting the Planed and Actual column first, then pivot the data into the final result that you want. The UNPIVOT will turn the PurchasingValue and SellingValue columns into rows. Once this is done, then you can pivot the data into your result. The code will be: select * from ( select itemid, case when col = 'PurchasingValue' then 'P' when col = 'SellingValue' then 'S' end + cast (year as varchar (4)) new_col, value from yourtable unpivot Configure an SSIS package for SSIS Pivot Transformation. Launch Visual Studio 2019 and click on File > New > Project on the menu bar or by clicking the New Project button on the toolbar. It opens project templates. Click on Integration Service Project : Specify the project name and storage location. select * from ( select prof_sk, prod_sk, rep_sk from pivot_temp) as t PIVOT ( SUM(metric_value) for metric_sk in (attainment, sales_trx, sales_nrx)) AS PivotTable Sample Data before pivot: Data after pivot required : and how to do unvipot as well via sparksql I chose not to - but using PIVOT would require the same thing. how about this - create the table and populate in sqlserver. Show us how you would do this in sqlserver, then insert another row into the table with values (4,'XXX',10) and show us the query that now outputs an extra column for XXX without changing the original query. 3. The least complicated, most straight-forward way of doing this is by simply wrapping your main query with the pivot in a common table expression, then grouping/aggregating. WITH PivotCTE AS ( select * from mytransactions pivot (sum (totalcount) for country in ( [Australia], [Austria])) as pvt ) SELECT numericmonth, chardate, SUM (totalamount Closed 4 years ago. My problem is pivoting one single Date column into my row headings while all my current Column headings become rows. My Column headings are [Date], which contains 50 years of days (from an awesome link provided by scsimon), and the other columns are vehicle license plates. What I learned, from Mitch Wheat, is that using SUM() can only operate on the numeric data types. The numeric data types have a higher precedence than any textual data type 1.. So the -is converted to a number, e.g.:. select CONVERT(int,'-') nE1GeO.

how to use pivot in sql