CODEWITHSUNDEEP
sqlsql-servert-sql
dIvYaNsH sInGh
dIvYaNsH sInGh

Reputation: 1993

How to express this query in SQL Server 2008

I have table called Reporting with following columns

OutletId CampaignId    ItemId  Qty  
10          1          Item1   12    
10          1          Item2   13    
10          1          Item3   14    
20          2          Item4   10    
20          2          Item5   11    
20          2          Item6   12    
20          2          Item7   8

Now I want to retrieve the data in this format when user select campaignId =1

OutletId   CampaignId  Item1  Item2   Item3  
10            1         12     13       14

when user select CampaignId=2 

OutletId   CampaignId Item4 Item5 Item6 Item7   
20            2         10    11   12    8

Here Items for campaign are not fixed

Upvotes: 1

Views: 76

Answers (2)

Tareq
Tareq

Reputation: 2006

I think it is efficient in this way:

SELECT *
FROM
(
    SELECT OutletId, CampaignId, ItemId, Qty
FROM Reporting) AS p
PIVOT
(
    SUM(Qty)
    FOR ItemId IN (SELECT ItemId FROM Reporting WHERE campaignId =1) 
) as pvt

Comment: Here campaignId =1 or campaignId =2 or campaignId =... whatever u want

Upvotes: 4

David Aleu
David Aleu

Reputation: 3942

A possible solution would be:

SELECT *
FROM
(
SELECT OutletId, CampaignId,ItemId, Qty
FROM test) AS p
PIVOT
(
    SUM(Qty)
    FOR ItemId IN (Item1,Item2,Item3,Item4)
) as pvt

But obviously, as commented before is not very efficient because you don't always know the Items... you either redesign your table or if using PIVOT you can build a dynamic sql building pivot items previously.

Upvotes: 0

Related Questions