如有一个表:
-------------------
学号 姓名
A01 A
B01 B
C01 C
D01 D
E01 E
---------------------
要把他变成这种表:
----------------------
学号1 姓名1 学号2 姓名2 学号3 姓名3
A01 A B01 B C01 C
D01 D E01 E
----------------------
能有一条SELECT语句实现吗?怎么写???
-------------------
学号 姓名
A01 A
B01 B
C01 C
D01 D
E01 E
---------------------
要把他变成这种表:
----------------------
学号1 姓名1 学号2 姓名2 学号3 姓名3
A01 A B01 B C01 C
D01 D E01 E
----------------------
能有一条SELECT语句实现吗?怎么写???
Answer:
表
F1 F2
jack book1
jack book2
jack book3
mary book4
mary book5
...转化为
F1 F2 F3 F4 F5
jack book1 book2 book3
mary book4 book5
billy book6 book7--------------------------------------------测试过程:
--------------------------------------------
create table Test
(F1 char(10),
F2 char(10))--测试表insert into Test
select 'jack' F1,'book1' F2
union
select 'jack' F1,'book2' F2
union
select 'jack' F1,'book3' F2
union
select 'Mary' F1,'book4' F2
union
select 'Mary' F1,'book5' F2
union
select 'Mike' F1,'book1' F2
union
select 'Mike' F1,'book5' F2
union
select 'Mike' F1,'book7' F2
union
select 'Mike' F1,'book9' F2--插入数据select id=identity(int,0,1),f1,f2 into #t from testselect a.f1,a.f2,a.id,cc ,N=
case when (id>cc) then cast(id-cc-minn+1 as Char(10))
when (id<=cc) then cast(id+1 as Char(10))
end
into #Temp
from #t a,
(select f1,cc,minn from
(select f1,count(*)as cc,min(id)-count(*) as minn from #t group by f1) t)b
where a.f1=b.f1
--构造两个临时表,由于要用到行号,所以必须要第一个临时表,第二个可以不用
select * from #Temp--这个表笔原来的多一个字段,每个人的第n条记录DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT f1 姓名'
SELECT @SQL= @SQL+ ',MIN(CASE WHEN N = ''' + N + ''' THEN F2 END) [F' + N + ']' FROM (SELECT DISTINCT N FROM #Temp) A
SET @SQL=@SQL+' FROM #Temp GROUP BY F1'
EXEC (@SQL)
--一条动态SQL语句drop table #t
drop table #Temp
drop table Test
/*
jack book1 1
jack book2 2
jack book3 3
Mary book4 1
Mary book5 2
Mike book1 1
Mike book5 2
Mike book7 3
Mike book9 4
--------Temp表数据*//*
jack book1 book2 book3 NULL
Mary book4 book5 NULL NULL
Mike book1 book5 book7 book9 --------最终结果*/
Dim rs As ADODB.Recordset
Dim Sql As String
Dim Index As Long
Set Con = New ADODB.Connection
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db.mdb;Persist Security Info=False"
Set rs = New ADODB.Recordset
rs.Open "Select * From 表1 Order By 学号", Con, adOpenDynamic, adLockOptimistic
Index = 0
Sql = vbNullString
Do While Not rs.EOF
If Index Mod 3 = 0 Then
If Sql = vbNullString Then
Sql = "Insert Into 表2 Values('" & rs.Fields("学号") & "','" & rs.Fields("姓名") & "'"
Else
Sql = Sql & ")"
Con.Execute Sql
Sql = "Insert Into 表2 Values('" & rs.Fields("学号") & "','" & rs.Fields("姓名") & "'"
End If
Else
If Sql = vbNullString Then
Sql = "Insert Into 表2 Values('" & rs.Fields("学号") & "','" & rs.Fields("姓名") & "'"
Else
Sql = Sql & ",'" & rs.Fields("学号") & "','" & rs.Fields("姓名") & "'"
End If
End If
Index = Index + 1
rs.MoveNext
Loop
If Index > 0 And (Index Mod 3 <> 0) And Sql <> vbNullString Then
Sql = Sql & ")"
Con.Execute Sql
End If这个程序还有个问题,就是插入的记录不满3条的话,要定义好插入记录的字段数,你自己解决这个问题吧