22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT Agent_ID ,
sale_year ,
sale_month ,
sale_day ,
MAX(CASE Product_ID
WHEN CONVERT(VARCHAR(20), Product_ID) THEN Quantity
ELSE 0
END) AS [12] ,
MAX(CASE Product_ID
WHEN CONVERT(VARCHAR(20), Product_ID) THEN Quantity
ELSE 0
END) AS [15] ,
MAX(CASE Product_ID
WHEN CONVERT(VARCHAR(20), Product_ID) THEN Quantity
ELSE 0
END) AS [17] ,
MAX(CASE Product_ID
WHEN CONVERT(VARCHAR(20), Product_ID) THEN Quantity
ELSE 0
END) AS [19] ,
MAX(CASE Product_ID
WHEN CONVERT(VARCHAR(20), Product_ID) THEN Quantity
ELSE 0
END) AS [20] ,
MAX(CASE Product_ID
WHEN CONVERT(VARCHAR(20), Product_ID) THEN Quantity
ELSE 0
END) AS [21] ,
MAX(CASE Product_ID
WHEN CONVERT(VARCHAR(20), Product_ID) THEN Quantity
ELSE 0
END) AS [22]
FROM PSALE_SALE
WHERE sale_year = 2010
AND sale_month = 8
AND sale_day = 10
AND agent_id = 13
GROUP BY
Sale_Year ,
Sale_Month ,
Sale_Day
DECLARE @sql VARCHAR(8000)
SET @sql = 'select Agent_ID,[Sale_Year],[Sale_Month],[Sale_Day] '
SELECT @sql = @sql
+ ' , max(case Product_ID when '+CONVERT(VARCHAR(20), Product_ID)+' then Quantity else 0 end) as ['
+ CONVERT(VARCHAR(20), Product_ID) + ']'
FROM ( SELECT DISTINCT
Product_ID
FROM PSALE_SALE
) AS a
SET @sql = @sql
+ ' from PSALE_SALE where [Sale_Year]=2010
and [Sale_Month]=8
and [Sale_Day]=10
and Agent_ID=13
group by Agent_ID,Sale_Year,Sale_Month,Sale_Day '
exec (@sql)
---
Agent_ID Sale_Year Sale_Month Sale_Day 12 15 17 19 20 21 22
13 2010 8 10 7 7 55 9 8 9 44
DECLARE @sql VARCHAR(8000)
SET @sql = 'select Agent_ID,sale_year,sale_month,sale_day '
SELECT @sql = @sql
+ ' , max(case Product_ID when convert(varchar(20),Product_ID) then Quantity else 0 end) as ['
+ CONVERT(VARCHAR(20), Product_ID) + ']'
FROM ( SELECT DISTINCT
Product_ID
FROM PSALE_SALE
) AS a
SET @sql = @sql
+ ' from PSALE_SALE where sale_year=2010 and sale_month=8 and sale_day=10 and agent_id=13 group by saleid, Agent_ID,Sale_Year,Sale_Month,Sale_Day '