问题:行转列,可获取的数量是最大值,而不是具体值,请各位帮忙,谢谢Table:
CREATE TABLE [dbo].[PSALE_SALE] (
[SALEID] int IDENTITY(1, 1) NOT NULL,
[Agent_ID] int NOT NULL,
[Sale_Year] int NOT NULL,
[Sale_Month] int NOT NULL,
[Sale_Day] int NOT NULL,
[Product_ID] int NOT NULL,
[Quantity] int NOT NULL,
CONSTRAINT [PK_PSALE_SALE] PRIMARY KEY CLUSTERED ([SALEID])
)
ON [PRIMARY]
GO数据:
/* Data for the `dbo.PSALE_SALE` table (Records 1 - 21) */
INSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (36, 13, 2010, 8, 10, 22, 44)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (37, 13, 2010, 8, 10, 21, 9)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (38, 13, 2010, 8, 10, 17, 55)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (39, 13, 2010, 8, 10, 12, 7)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (40, 13, 2010, 8, 10, 20, 8)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (41, 13, 2010, 8, 10, 19, 9)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (42, 13, 2010, 8, 10, 15, 7)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (43, 15, 2010, 8, 12, 22, 33)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (44, 15, 2010, 8, 12, 21, 44)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (45, 15, 2010, 8, 12, 17, 55)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (46, 15, 2010, 8, 12, 12, 66)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (47, 15, 2010, 8, 12, 20, 7)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (48, 15, 2010, 8, 12, 19, 8)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (49, 15, 2010, 8, 12, 15, 99)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (50, 17, 2010, 8, 12, 22, 55)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (51, 17, 2010, 8, 12, 21, 5)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (52, 17, 2010, 8, 12, 17, 67)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (53, 17, 2010, 8, 12, 12, 89)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (54, 17, 2010, 8, 12, 20, 44)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (55, 17, 2010, 8, 12, 19, 3)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (56, 17, 2010, 8, 12, 15, 33)
GOSqlScript: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) 结果:13 2010 8 10 55 55 55 55 55 55 55而正确的值应该是跟下面一样的
select * from PSALE_SALE where sale_year=2010 and sale_month=8 and sale_day=10 and agent_id=13 order by product_id
不知哪里出错,求解.
CREATE TABLE [dbo].[PSALE_SALE] (
[SALEID] int IDENTITY(1, 1) NOT NULL,
[Agent_ID] int NOT NULL,
[Sale_Year] int NOT NULL,
[Sale_Month] int NOT NULL,
[Sale_Day] int NOT NULL,
[Product_ID] int NOT NULL,
[Quantity] int NOT NULL,
CONSTRAINT [PK_PSALE_SALE] PRIMARY KEY CLUSTERED ([SALEID])
)
ON [PRIMARY]
GO数据:
/* Data for the `dbo.PSALE_SALE` table (Records 1 - 21) */
INSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (36, 13, 2010, 8, 10, 22, 44)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (37, 13, 2010, 8, 10, 21, 9)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (38, 13, 2010, 8, 10, 17, 55)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (39, 13, 2010, 8, 10, 12, 7)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (40, 13, 2010, 8, 10, 20, 8)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (41, 13, 2010, 8, 10, 19, 9)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (42, 13, 2010, 8, 10, 15, 7)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (43, 15, 2010, 8, 12, 22, 33)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (44, 15, 2010, 8, 12, 21, 44)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (45, 15, 2010, 8, 12, 17, 55)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (46, 15, 2010, 8, 12, 12, 66)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (47, 15, 2010, 8, 12, 20, 7)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (48, 15, 2010, 8, 12, 19, 8)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (49, 15, 2010, 8, 12, 15, 99)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (50, 17, 2010, 8, 12, 22, 55)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (51, 17, 2010, 8, 12, 21, 5)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (52, 17, 2010, 8, 12, 17, 67)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (53, 17, 2010, 8, 12, 12, 89)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (54, 17, 2010, 8, 12, 20, 44)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (55, 17, 2010, 8, 12, 19, 3)
GOINSERT INTO [dbo].[PSALE_SALE] ([SALEID], [Agent_ID], [Sale_Year], [Sale_Month], [Sale_Day], [Product_ID], [Quantity])
VALUES (56, 17, 2010, 8, 12, 15, 33)
GOSqlScript: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) 结果:13 2010 8 10 55 55 55 55 55 55 55而正确的值应该是跟下面一样的
select * from PSALE_SALE where sale_year=2010 and sale_month=8 and sale_day=10 and agent_id=13 order by product_id
不知哪里出错,求解.
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 '
select * from PSALE_SALE where sale_year=2010 and sale_month=8 and sale_day=10 and agent_id=13 order by product_id ? 这段只有一条记录吗?
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
WHEN CONVERT(VARCHAR(20), Product_ID) THEN Quantity
ELSE 0
END) AS [12] 我不知道你写这个SQL的逻辑是啥。但是你应该知道的是,case when的用法
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
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