Creating multiple columns in a view

[brunei2730]

SQL 2012 Sample data:

Table labeled Data-

User Item Date
Ann shoe 01022015
Beth coat 02182015
Carl pants 01242015
Dave coat 03072015

I would like to create a view that would show the number of items sold per month in the following layout

View labeled Results-

Jan Feb Mar
2 1 1

This is what I have right now

create view [dbo].[View]
as
Select Count(*) AS Jan from
OPENQUERY(Table1,'Select * From DATA where DATE >= 01012015 and DATE <= 01312015')
UNION ALL
Select Count(*) AS Feb from
OPENQUERY(Table1,'Select * From DATA where DATE >= 02012015 and DATE <= 02282015')
UNION ALL
Select Count(*) AS Mar from
OPENQUERY(Table1,'Select * From DATA where DATE >= 03012015 and DATE <= 03312015')

which yields these results-

Jan
2
1
1

Is there a way to do what I'm trying to do?

 

[simian336]

I think you want a pivot table... Here is a pretty good example of what you want.

http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query

Simi