CODEWITHSUNDEEP
sqlsql-server
SUPERGIRL
SUPERGIRL

Reputation: 23

Alternate query for cross apply in SQL Server 2005

enter image description here

Query:

SELECT DISTINCT 
    CREATEDBY, PITCHES, [COUNT], REVENUE, ORD 
FROM 
    @TEMPMAILER
CROSS APPLY  
    (VALUES ('Total Active', CNTACTIVE, TOTALREVACT, 0),
            ('In Suspect', CNTSUSPECT, TOTALREVS, 1),
            ('In Prospect', CNTPROSPECT, TOTALREVP, 2),
            ('In Approach', CNTAPPROACH, TOTALREVA, 3),
            ('In Negotiation', CNTNEGO, TOTALREVN, 4),
            ('In Closure', CNTCLOSURE, TOTALREVC, 5),
            ('In Order', CNTORDER, TOTALREVO, 6)) V (PITCHES,[COUNT], REVENUE, ORD)

I want data to come in 2nd form as in the image without using cross apply.

Upvotes: 1

Views: 212

Answers (1)

Martin Smith
Martin Smith

Reputation: 452998

You don't give any reason for wanting to avoid CROSS APPLY.

I'm assuming this is because your current query doesn't work in 2005 and you have come to the incorrect conclusion that this is the fault of the APPLY. The reason your query doesn't work in 2005 is beacuse of the VALUES not the APPLY.

This will work in 2005.

SELECT DISTINCT CREATEDBY,
                PITCHES,
                [COUNT],
                REVENUE,
                ORD
FROM   @TEMPMAILER
CROSS APPLY (SELECT 'Total Active',CNTACTIVE,TOTALREVACT,0 UNION ALL
                SELECT 'In Suspect',CNTSUSPECT,TOTALREVS,1 UNION ALL
                SELECT 'In Prospect',CNTPROSPECT,TOTALREVP,2 UNION ALL
                SELECT 'In Approach',CNTAPPROACH,TOTALREVA,3 UNION ALL
                SELECT 'In Negotiation',CNTNEGO,TOTALREVN,4 UNION ALL
                SELECT 'In Closure',CNTCLOSURE,TOTALREVC,5 UNION ALL
                SELECT 'In Order',CNTORDER,TOTALREVO,6               
                )V(PITCHES,[COUNT],REVENUE,ORD)

Upvotes: 3

Related Questions