一个sql语句的问题
表 t1 中
列 abc cba
100 aaa
101 bbb
555 ccc
777 aaa
.
.
.
.
要求生成的表
列 abc cba mmm
× × 1
× × 1
× × 1
× × 2
× × 2
× × 2
× × 3
× × 3
.
.
.
.
.
abc 和 cba 内容不变 mmm 每3个加一
[解决办法]
try:
CREATE TABLE #T1(abc int,cba nvarchar(20))
INSERT INTO #T1
SELECT 101, 'A ' UNION ALL
SELECT 102, 'B ' UNION ALL
SELECT 104, 'we ' UNION ALL
SELECT 101, 'tye ' UNION ALL
SELECT 201, 'we ' UNION ALL
SELECT 201, '3e ' UNION ALL
SELECT 241, '3e ' UNION ALL
SELECT 251, 'sfe ' UNION ALL
SELECT 271, 'we ' UNION ALL
SELECT 328, 'DD '
SELECT IDENTITY(int, 3,1) [id],*,CAST(NULL AS int) mmm INTO #T2 FROM #T1
UPDATE #T2 SET mmm=[id]/3
SELECT * FROM #T2
DROP TABLE #T1,#T2
[解决办法]
create table #test(xm varchar(10),lx varchar(10))
insert #test select 'aaa ', 'A '
union all select 'bbb ', 'C '
union all select 'ccc ', 'C '
union all select 'aaa ', 'C '
union all select 'bbb ', 'C '
union all select 'ccc ', 'C '
union all select 'aaa ', 'C '
union all select 'bbb ', 'C '
union all select 'ccc ', 'C '
SELECT *,Identity(Int,1,1) As RowIndex INTO #TEST1 FROM #TEST
SELECT XM,LX,(ROWINDEX-1)/3+1 FROM #TEST1
[解决办法]
Select abc,cba,
(Select Count(*) From t1 Where abc < A.abc) / 10 + 1 As mmm
(Select Count(*) From t1 Where abc < A.abc) / 6+ 1 As ddd
into #t
From t1 A
declare @sql varchar(8000)
set @sql = 'select mmm , '
select @sql = @sql + 'max(case [ddd] when ' ' '+ddd+ ' ' '
then abc+cba end) as ' ' '+ddd+ ' ' ', '
from (select distinct mmm from #t) as a
select @sql = left(@sql,len(@sql)-1) + ' from #t group by mmm '
exec(@sql)
[解决办法]
樓主，原來的數據是60行 * 2列，有120個數據，怎麼填充到10行 * 6列的表格裡？
假設只取這60行數據的第一列來填充。
Create Table TEST
(ANvarchar(20),
BNvarchar(20))
Insert TEST Select null,null
Union All Select N '数学 ', '三.3 '
Union All Select N '数学 ', '三.1 '
Union All Select null,null
Union All Select null,null
Union All Select N '科学 ', '三.3 '
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select N '数学 ', '三.1 '
Union All Select N '数学 ', '三.3 '
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select N '科学 ', '三.3 '
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select N '数学 ', '三.3 '
Union All Select N '数学 ', '三.1 '
Union All Select null,null
Union All Select null,null
Union All Select N '科学 ', '三.1 '
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select N '数学 ', '三.3 '
Union All Select null,null
Union All Select N '数学 ', '三.1 '
Union All Select N '体健 ', '三.3 '
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select N '数学 ', '三.1 '
Union All Select N '数学 ', '三.3 '
Union All Select null,null
Union All Select null,null
Union All Select N '科学 ', '三.1 '
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
Union All Select null,null
GO
--Select * From TEST
Select ID = Identity(Int, 0, 1), * Into #T From TEST
Select
MAX(Case When ID / 10 = 0 Then A Else null End) As 周一,
MAX(Case When ID / 10 = 1 Then A Else null End) As 周二,
MAX(Case When ID / 10 = 2 Then A Else null End) As 周三,
MAX(Case When ID / 10 = 3 Then A Else null End) As 周四,
MAX(Case When ID / 10 = 4 Then A Else null End) As 周五,
MAX(Case When ID / 10 = 5 Then A Else null End) As 周六
From
#T
Group By ID % 10
GO
Drop Table #T
Drop Table TEST
GO
--Result
/*
周一周二周三周四周五周六
NULL数学NULL数学NULLNULL
数学数学数学NULL数学NULL
数学NULL数学数学数学NULL
NULLNULLNULL体健NULLNULL
NULLNULLNULLNULLNULLNULL
科学科学科学NULL科学NULL
NULLNULLNULLNULLNULLNULL
NULLNULLNULLNULLNULLNULL
NULLNULLNULLNULLNULLNULL
NULLNULLNULLNULLNULLNULL
*/
