Pivot query in Microsoft SQL server

Question

I am a new to sql server and I have been trying for the last couple of days to transform a simple TRANSFORM query in Access to SQL Server.

In access the query looks like :

TRANSFORM tblDefHolidays.colDate SELECT tblDefHolidays.colDate FROM tblDefHolidays WHERE tblDefHolidays.colDate >= DateAdd ("d", -60, date()) AND tblDefHolidays.colDate <= DateAdd ("yyyy",2, date()) GROUP BY tblDefHolidays.colDate ORDER BY tblDefHolidays.colCal DESC PIVOT tblDefHolidays.colCal;

In SQL Server the table tblDefHolidays is defined as follows :

colCal nvarchar(40) colDate date colCodeBB nvarchar(20) colDesc nvarchar(255)

This is my current SQL Server query which is not running.

SELECT colDate, colCal FROM ( SELECT dbo.tblDefHolidays.colDate, dbo.tblDefHolidays.colCal FROM dbInv.dbo.tblDefHolidays) as [subTable] PIVOT ( max(colDate) FOR dbInv.dbo.tblDefHolidays.colCal IN ([OSAKA],[LIFFE],[HKEX],[EUREX],[CME],[CBOE]) ) as [pivotTable]

When running the current sql server query I get :

Msg 107, Level 15, State 1, Line 9 The column prefix 'dbInv.dbo.tblDefHolidays' does not match with a table name or alias name used in the query. Msg 207, Level 16, State 1, Line 2 Invalid column name 'colDate'.

The result (from Access) should look like :

 colDate OSAKA LIFFE HKEX EUREX CME CBOE 7/1/2014 7/1/2014 7/4/2014 7/4/2014 7/4/2014 7/21/2014 7/21/2014 8/25/2014 8/25/2014 9/1/2014 9/1/2014 9/1/2014 9/9/2014 9/9/2014 9/15/2014 9/15/2014 9/23/2014 9/23/2014 10/1/2014 10/1/2014

Thanks for reading.

Best,

Manu

What 'not running' means? Do you have any error message to share with us? Where do you select the pivoted columns? (You listed the original fields in the outer query instead of the pivoted fields) — Pred
– Pred, Commented Aug 14, 2014 at 8:45

Paul Maxwell · Accepted Answer · 2014-08-14 11:58:22Z

Please try the following. After the SourceTable subquery you should no longer refer to fields via dbInv.dbo.tblDefHolidays the alias SourceTable takes over

SELECT [OSAKA] , [LIFFE] , [HKEX] , [EUREX] , [CME] , [CBOE] FROM ( SELECT dbo.tblDefHolidays.colDate , dbo.tblDefHolidays.colCal FROM dbInv.dbo.tblDefHolidays ) AS SourceTable PIVOT ( MAX(colDate) FOR colCal IN ([OSAKA], [LIFFE], [HKEX], [EUREX], [CME], [CBOE]) ) AS PivotTable ;

attempt at variant with redundant column (don't be mad, its technically redundant)

SELECT displayDt , [OSAKA] , [LIFFE] , [HKEX] , [EUREX] , [CME] , [CBOE] FROM ( SELECT convert(varchar,dbo.tblDefHolidays.colDate,101) as displayDt , dbo.tblDefHolidays.colDate , dbo.tblDefHolidays.colCal FROM dbInv.dbo.tblDefHolidays ) AS SourceTable PIVOT ( MAX(colDate) FOR colCal IN ([OSAKA], [LIFFE], [HKEX], [EUREX], [CME], [CBOE]) ) AS PivotTable ORDER BY displayDt ;

I have guessed at MM/DD/YYYY style 101 for that date column.

You should be able to add an order by clause that references a field in the select clause see: BOL

There is NO way to structure the query without naming the columns (OSAKA, LIFFE, etc.) In Access there may be no need to do that, here there is.

Thanks for the help. When running your query I get : Msg 207, Level 16, State 1, Line 2 Invalid column name 'colDate'. I tried dbo.tblDefHolidays.colDate instead but that did not solve the problem.
mmm, I hadn't noticed but as you only have 2 fields in the source there really isn't anything to pivot on, try it by removing coldate from the select clause (as it is above now). let me know what happens. Edit this answer with any error message (don't use comment)
I know you want an additional row, but I can only guess that this will work.
ok, stop editing the answer now - I suggested that for error messages only, it'ds not working - so please just use comments

Collectives™ on Stack Overflow

Pivot query in Microsoft SQL server

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related