TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
L A
NA
170
171.2k
Pivot Table Data
Jun 15 2017 7:20 PM
Hello all,
I'm working on SQL Server 2012. Trying to Pivot table data, shown below
CategoryName
Sales
ShippingYear
Confections
27257.51
1996
Meat/Poultry
81338.06
1997
Beverages
102074.31
1997
Grains/Cereals
9219.92
1996
Seafood
65544.18
1997
Confections
80894.14
1997
Produce
12651.16
1996
Condiments
17754.78
1996
Produce
53019.98
1997
Grains/Cereals
55948.82
1997
Dairy Products
36711.37
1996
Meat/Poultry
24617.86
1996
Dairy Products
114749.78
1997
Beverages
46338
1996
Condiments
55277.6
1997
Seafood
18765.97
1996
to
Years
Beverages
Condiments
Confections
Dairy Products
Grains/Cereals
Meat/Poultry
Produce
Seafood
1996
46338
17754.78
27257.51
36711.37
9219.92
24617.86
12651.16
18765.97
1997
102074.31
55277.6
80894.14
114749.78
55948.82
81338.06
53019.98
65544.18
I tried writing following SQL Query
SELECT
CategoryName, 1996_Val, 1997_Val
FROM
(
SELECT
Categories.CategoryName,
Sum
(
CONVERT
(money,(
"Order Details"
.UnitPrice*Quantity*(1-Discount)/100))*100)
AS
Sales,
YEAR
(Orders.ShippedDate)
AS
ShippingYear
FROM
Orders
INNER
JOIN
[
Order
Details]
ON
Orders.OrderID = [
Order
Details].OrderID
INNER
JOIN
Products
ON
[
Order
Details].ProductID = Products.ProductID
INNER
JOIN
Categories
ON
Products.CategoryID = Categories.CategoryID
WHERE
(((Orders.ShippedDate)
Between
'19960101'
And
'19971231'
))
GROUP
BY
Categories.CategoryID, Categories.CategoryName,
YEAR
(Orders.ShippedDate)
)p
PIVOT
(
MAX
(Sales)
For
ShippingYear
IN
(1996,1997))
AS
pvt
ORDER
BY
Categories.CategoryID
which didn't work. Please help me to find out my mistake & achieve this.
Thnaks in advance.
Reply
Answers (
1
)
Conversion failed when converting date and/or time from character string
SSIS send mail task