已有表a
bh,rq, sj
1, 2008-08-08,08:00
1, 2008-08-08,17:30
1, 2008-08-08,18:30
1, 2008-08-09,09:00
1, 2008-08-09,17:00
1, 2008-08-09,19:00
2, 2008-08-08,08:00
2, 2008-08-08,12:00
.
.
.已有表b
bh,rq, sj1, sj2, sj3, sj4, sj5, sj6, sj7, sj8, sj9
1, 2008-08-08,
1, 2008-08-09,
2, 2008-08-08,
3, 2008-08-08, 结果:表b
bh,rq, sj1, sj2, sj3, sj4, sj5, sj6, sj7, sj8, sj9
1, 2008-08-08, 08:00, 17:30, 18:30
1, 2008-08-09, 09:00, 17:00, 19:00
2, 2008-08-08, 08:00, 12:00
3, 2008-08-08,求SQL语句,将表a中对应bh及对应rq的sj注入到表b的sj1到sj(N)的列中。
bh,rq, sj
1, 2008-08-08,08:00
1, 2008-08-08,17:30
1, 2008-08-08,18:30
1, 2008-08-09,09:00
1, 2008-08-09,17:00
1, 2008-08-09,19:00
2, 2008-08-08,08:00
2, 2008-08-08,12:00
.
.
.已有表b
bh,rq, sj1, sj2, sj3, sj4, sj5, sj6, sj7, sj8, sj9
1, 2008-08-08,
1, 2008-08-09,
2, 2008-08-08,
3, 2008-08-08, 结果:表b
bh,rq, sj1, sj2, sj3, sj4, sj5, sj6, sj7, sj8, sj9
1, 2008-08-08, 08:00, 17:30, 18:30
1, 2008-08-09, 09:00, 17:00, 19:00
2, 2008-08-08, 08:00, 12:00
3, 2008-08-08,求SQL语句,将表a中对应bh及对应rq的sj注入到表b的sj1到sj(N)的列中。
@oldRq CHAR(10),@count INTEGER,@ColName VarChar(10)
SET @oldBh=''
SET @oldRq=''
DECLARE @c0 cursor SET @c0=CURSOR LOCAL SCROLL FOR
SELECT * FROM a
OPEN @c0
FETCH NEXT FROM @c0 INTO @bh,@rq,@sj
WHILE @@fetch_status=0
BEGIN
IF @rq=@oldRq and @bh=@oldBh
SET @count=@count+1
ELSE
SET @count=1
SET @ColName='sj'+convert(char(1),@count)
EXEC('UPDATE b SET '+@ColName+'='''+@sj+''' WHERE bh='''+@bh+''' and rq='''
+@rq+'''')
SET @oldBh=@bh
SET @oldRq=@rq
FETCH NEXT FROM @c0 INTO @bh,@rq,@sj
END
CLOSE @c0
DEALLOCATE @c0
CREATE TABLE #(
bh char(2),rq varchar(10), sj char(5)
)
INSERT INTO #
SELECT '1', '2008-08-08','08:00' UNION ALL
SELECT '1', '2008-08-08','17:30' UNION ALL
SELECT '1', '2008-08-08','18:30' UNION ALL
SELECT '1', '2008-08-09','09:00' UNION ALL
SELECT '1', '2008-08-09','17:00' UNION ALL
SELECT '1', '2008-08-09','19:00' UNION ALL
SELECT '2', '2008-08-08','08:00' UNION ALL
SELECT '2', '2008-08-08','12:00'
Declare @S varchar(1000)
SELECT @S=''
SELECT @S=@S+', MAX(CASE WHEN sj='''+sj+''' THEN sj ELSE '''' END)'
FROM # GROUP BY sj
EXEC('SELECT bh,rq '+@S+' FROM # GROUP BY bh,rq ORDER BY bh,rq')/*
bh rq
---- ---------- ----- ----- ----- ----- ----- ----- -----
1 2008-08-08 08:00 17:30 18:30
1 2008-08-09 09:00 17:00 19:00
2 2008-08-08 08:00 12:00 */
bh char(2),rq varchar(10), sj char(5)
)
INSERT INTO #
SELECT '1', '2008-08-08','08:00' UNION ALL
SELECT '1', '2008-08-08','17:30' UNION ALL
SELECT '1', '2008-08-08','18:30' UNION ALL
SELECT '1', '2008-08-09','09:00' UNION ALL
SELECT '1', '2008-08-09','17:00' UNION ALL
SELECT '1', '2008-08-09','19:00' UNION ALL
SELECT '2', '2008-08-08','08:00' UNION ALL
SELECT '2', '2008-08-08','12:00'
SELECT *,sign=ltrim((SELECT COUNT(1) FROM # WHERE bh=A.bh AND rq=A.rq AND sj<=A.sj))INTO #1 FROM # ADeclare @S1 varchar(1000),@S2 varchar(1000)
SELECT @S1=' '
SELECT @S1=@S1+', MAX(CASE WHEN sign='''+sign+''' THEN sj ELSE '''' END) AS ''sj'+sign+''' '
FROM #1 GROUP BY sign
EXEC('SELECT bh,rq '+@S1+' FROM #1 GROUP BY bh,rq ORDER BY bh,rq')/*
bh rq sj1 sj2 sj3
---- ---------- ----- ----- -----
1 2008-08-08 08:00 17:30 18:30
1 2008-08-09 09:00 17:00 19:00
2 2008-08-08 08:00 12:00
*/
发哥是不是啊?你的方案是最好的。谢谢了。