Sql Query for Particular Concept

Posted by Samy33 under Sql Server on 7/8/2010 | Views : 1141 | Status : [Member] | Replies : 2
I am having one table called Sales tables.tables has the following Datas

Product Date Sales
Product1 Date1 20
Product2 Date1 0
Product3 Date1 10
Product1 Date2 50
Product2 Date2 20
Product3 Date2 20
Product1 Date3 0
Product2 Date3 2
Product3 Date3 24

This is the table which contains these information.
In my summary screen records should be displayed like following type

Product Date1 Date2 Date3
Product1 20 50 -NA-
Product2 -NA- 20 2
Product3 10 20 24

if sales 0 means -NA- should displayed .I need Sql Query for this above concept,
if any body knows please inform ne




Responses

Posted by: PandianS on: 7/8/2010 [Member] [MVP] Silver

Up
0
Down
Hi

If you using SQL Server 2005, 2008 or 2008 R2 then...

Try this... It should work...

SELECT	Product, 

Case Date1 When 0 Then '-NA-' Else Cast(Date1 as varchar) End Date1,
Case Date2 When 0 Then '-NA-' Else Cast(Date2 as varchar) End Date2,
Case Date3 When 0 Then '-NA-' Else Cast(Date3 as varchar) End Date3
FROM
(SELECT Product, [Date], Sales
FROM SalesTable ) ps
PIVOT
(
SUM(Sales)
FOR [Date] IN
( [Date1], [Date2],[Date3])
) AS pvt


Result should be like this:
Product	Date1	Date2	Date3

Product1 20 50 -NA-
Product2 -NA- 20 2
Product3 10 20 24


Pls reply... If its fine.

Note: If you using SQL Server 6.5 or 7.0 or 2000 then you have use Co-Related subquery or CASE.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Samy33, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Samy33 on: 7/15/2010 [Member] Starter

Up
0
Down
what is the use of PIVOT in above Query

Samy33, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response