资料表A:
ID DATE BOUGHT
------------------------
A 2007/1/1 笔
A 2007/1/1 尺
A 2007/1/1 立可白
A 2007/1/3 擦子
B 2007/1/3 笔
B 2007/1/4 美工刀
B 2007/1/4 尺
B 2007/1/4 擦子
…
希望可以用SQL语法产生以下的结果:
ID DATE BOUGHT1 BOUGHT2 BOUGHT3
--------------------------------------------------
A 2007/1/1 笔 尺 立可白
A 2007/1/3 擦子
B 2007/1/3 笔
B 2007/1/4 美工刀 尺 擦子
…
PS:同一ID在同一天买的物品放在同一记录里,假设每人每天最多只买三样东西,若没有则为NULL
爬文过了,可是一直没有找到我想要的答案,所以来请教一下各位前辈,先谢谢大家!
ID DATE BOUGHT
------------------------
A 2007/1/1 笔
A 2007/1/1 尺
A 2007/1/1 立可白
A 2007/1/3 擦子
B 2007/1/3 笔
B 2007/1/4 美工刀
B 2007/1/4 尺
B 2007/1/4 擦子
…
希望可以用SQL语法产生以下的结果:
ID DATE BOUGHT1 BOUGHT2 BOUGHT3
--------------------------------------------------
A 2007/1/1 笔 尺 立可白
A 2007/1/3 擦子
B 2007/1/3 笔
B 2007/1/4 美工刀 尺 擦子
…
PS:同一ID在同一天买的物品放在同一记录里,假设每人每天最多只买三样东西,若没有则为NULL
爬文过了,可是一直没有找到我想要的答案,所以来请教一下各位前辈,先谢谢大家!
ID,
[DATE],
Max(Case When NewOrderID = 1 Then BOUGHT Else '' End) As BOUGHT1,
Max(Case When NewOrderID = 2 Then BOUGHT Else '' End) As BOUGHT2,
Max(Case When NewOrderID = 3 Then BOUGHT Else '' End) As BOUGHT3
From
(Select NewOrderID = (Select Count(OrderID) From #T Where ID = A.ID And [DATE] = A.[DATE] And OrderID <= A.OrderID), * From #T A) B
Group By
ID,
[DATE]Drop Table #T
Create Table A
(ID Varchar(10),
[DATE] Varchar(10),
BOUGHT Nvarchar(20))
--插入數據
Insert A Select 'A', '2007/1/1', N'笔'
Union All Select 'A', '2007/1/1', N'尺'
Union All Select 'A', '2007/1/1', N'立可白'
Union All Select 'A', '2007/1/3', N'擦子'
Union All Select 'B', '2007/1/3', N'笔'
Union All Select 'B', '2007/1/4', N'美工刀'
Union All Select 'B', '2007/1/4', N'尺'
Union All Select 'B', '2007/1/4', N'擦子'
GO
--測試
--沒有關鍵字,需要借助下臨死表Select OrderID = Identity(Int, 1, 1), * Into #T From ASelect
ID,
[DATE],
Max(Case When NewOrderID = 1 Then BOUGHT Else '' End) As BOUGHT1,
Max(Case When NewOrderID = 2 Then BOUGHT Else '' End) As BOUGHT2,
Max(Case When NewOrderID = 3 Then BOUGHT Else '' End) As BOUGHT3
From
(Select NewOrderID = (Select Count(OrderID) From #T Where ID = A.ID And [DATE] = A.[DATE] And OrderID <= A.OrderID), * From #T A) B
Group By
ID,
[DATE]Drop Table #T
GO
--刪除測試環境
Drop Table A
--結果
/*
ID DATE BOUGHT11 BOUGHT12 BOUGHT13
A 2007/1/1 笔 尺 立可白
A 2007/1/3 擦子
B 2007/1/3 笔
B 2007/1/4 美工刀 尺 擦子
*/
但是BOUGHT1、BOUGHT2、BOUGHT3的排列顺序不一定正确
-------------------------------------------------
Declare @sql Varchar(8000)
Set @sql=''
Select @sql=@sql+',Max(Case When OrdID='''+Cast(OrdID As Varchar)+'''Then BOUGHT Else '''' End) As
[BOUGHT'+Cast(OrdID As Varchar)+']' From (
Select OrdID=(Select Count(1) From A
Where ID=T.ID And [Date]=T.[Date] And BOUGHT<=T.BOUGHT),* From A T
) TT Group By OrdID
Exec('Select ID,[Date]'+@sql+'From(
Select OrdID=(Select Count(1) From A
Where ID=T.ID And [Date]=T.[Date] And BOUGHT<=T.BOUGHT),* From A T
) TT Group By ID,[Date]')
---创建测试环境
Create Table A(ID Varchar(10),DATE Varchar(10),BOUGHT Varchar(10))
Insert A Select 'A', '2007/1/1', '笔'
Union All Select 'A', '2007/1/1', '尺'
Union All Select 'A', '2007/1/1', '立可白'
Union All Select 'A', '2007/1/3', '擦子'
Union All Select 'B', '2007/1/3', '笔'
Union All Select 'B', '2007/1/4', '美工刀'
Union All Select 'B', '2007/1/4', '尺'
Union All Select 'B', '2007/1/4', '擦子'Select * From A
---查询结果
Declare @sql Varchar(8000)
Set @sql=''
Select @sql=@sql+',Max(Case When OrdID='''+Cast(OrdID As Varchar)+'''Then BOUGHT
Else '''' End) As [BOUGHT'+Cast(OrdID As Varchar)+']' From (
Select OrdID=(Select Count(1) From A
Where ID=T.ID And [Date]=T.[Date] And BOUGHT<=T.BOUGHT),* From A T
) TT Group By OrdID
Exec('Select ID,[Date]'+@sql+'From(
Select OrdID=(Select Count(1) From A
Where ID=T.ID And [Date]=T.[Date] And BOUGHT<=T.BOUGHT),* From A T
) TT Group By ID,[Date]')
---删除测试环境
Drop Table A
---结果
/*
ID Date BOUGHT1 BOUGHT2 BOUGHT3
---------- ---------- ---------- ---------- ----------
A 2007/1/1 笔 尺 立可白
A 2007/1/3 擦子
B 2007/1/3 笔
B 2007/1/4 擦子 尺 美工刀
*/
create table #a (id char(3),date datetime,bought char(10))
insert into #a select 'A' ,'2007/1/1','笔' union all
select 'A' ,'2007/1/1', '尺' union all
select 'A', '2007/1/1', '立可白' union all
select 'A', '2007/1/3', '擦子' union all
select 'B', '2007/1/3', '笔' union all
select 'B', '2007/1/4', '美工刀' union all
select 'B' ,'2007/1/4', '尺' union all
select 'B' ,'2007/1/4', '擦子'
declare @s varchar(8000)
set @s='select id, date ' --@s=@s+bought+'then bought'
select @s=@s+', max(case when bought='''+rtrim(bought)+''' then '''+rtrim(bought)+''' end) as'''+rtrim(bought)+'''' from #a group by bought
set @s=@s+' from #a group by id ,date '
print @s
exec (@s)
---------结果A 2007-01-01 00:00:00.000 笔 NULL 尺 立可白 NULL
A 2007-01-03 00:00:00.000 NULL 擦子 NULL NULL NULL
B 2007-01-03 00:00:00.000 笔 NULL NULL NULL NULL
B 2007-01-04 00:00:00.000 NULL 擦子 尺 NULL 美工刀
declare @t1 table (id char(1),[date] datetime,bought varchar(50))
insert into @t1 values('A', '2007/1/1', '笔')
insert into @t1 values('A', '2007/1/1', '尺')
insert into @t1 values('A', '2007/1/1', '立可白')
insert into @t1 values('A', '2007/1/3', '擦子')
insert into @t1 values('B', '2007/1/3', '笔')
insert into @t1 values('B', '2007/1/4', '美工刀')
insert into @t1 values('B', '2007/1/4', '尺')
insert into @t1 values('B', '2007/1/4', '擦子')
--查询保存表
declare @tmp table (id char(1),[date] datetime,bought varchar(8000) default '')
insert into @tmp (id,[date]) select distinct id,date from @t1
declare @id char(1),@date datetime,@bought varchar(8000)
declare tmp_cur cursor for select id,[date] from @tmp
open tmp_cur
fetch next from tmp_cur into @id,@date
while @@fetch_status=0
begin
set @bought=''
select @bought=case when @bought='' then bought else @bought+','+bought end from @t1 where id=@id and date=@date
update @tmp set bought=@bought where id=@id and date=@date
fetch next from tmp_cur into @id,@date
end
close tmp_cur
deallocate tmp_cur
select * from @tmp
谢谢两位前辈,利用鱼前辈的语法已得到我的结果,是的是太太太厉害了!
飞鸟前辈的程序码我会再去试。
另外,想再请教一下,如果同一人在同一天买的东西是重复的话,只出现第一笔就好,要如何改程序码呢?谢谢~-----------這麼處理下--創建測試環境
Create Table A
(ID Varchar(10),
[DATE] Varchar(10),
BOUGHT Nvarchar(20))
--插入數據
Insert A Select 'A', '2007/1/1', N'笔'
Union All Select 'A', '2007/1/1', N'尺'
Union All Select 'A', '2007/1/1', N'立可白'
Union All Select 'A', '2007/1/3', N'擦子'
Union All Select 'B', '2007/1/3', N'笔'
Union All Select 'B', '2007/1/3', N'笔'
Union All Select 'B', '2007/1/4', N'美工刀'
Union All Select 'B', '2007/1/4', N'尺'
Union All Select 'B', '2007/1/4', N'擦子'
GO
--測試
--沒有關鍵字,需要借助下臨死表Select OrderID = Identity(Int, 1, 1), * Into #T From (Select Distinct * From A) TSelect
ID,
[DATE],
Max(Case When NewOrderID = 1 Then BOUGHT Else '' End) As BOUGHT1,
Max(Case When NewOrderID = 2 Then BOUGHT Else '' End) As BOUGHT2,
Max(Case When NewOrderID = 3 Then BOUGHT Else '' End) As BOUGHT3
From
(Select NewOrderID = (Select Count(OrderID) From #T Where ID = A.ID And [DATE] = A.[DATE] And OrderID <= A.OrderID), * From #T A) B
Group By
ID,
[DATE]Drop Table #T
GO
--刪除測試環境
Drop Table A
--結果
/*
ID DATE BOUGHT11 BOUGHT12 BOUGHT13
A 2007/1/1 尺 立可白 笔
A 2007/1/3 擦子
B 2007/1/3 笔
B 2007/1/4 尺 美工刀 擦子
*/