Sql Query for Particular Concept

Posted by Samy33 under Sql Server on 7/8/2010 | Views : 2126 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	what is the use of PIVOT in above Query Samy33, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts