新手问题,请帮忙,谢谢
已知表 Aa b
01 1221223212122111121211122211222想得出以下结果,每行从新排列,每列4个数 第5个数从新排4个数..
a 新1 新2 新3...01 1 2 1 2 1 1 2 2
2 2 2 1 2 1 2 2
2 3 1 1 1 1 1 2
1 2 2 1 2 2 1
已知表 Aa b
01 1221223212122111121211122211222想得出以下结果,每行从新排列,每列4个数 第5个数从新排4个数..
a 新1 新2 新3...01 1 2 1 2 1 1 2 2
2 2 2 1 2 1 2 2
2 3 1 1 1 1 1 2
1 2 2 1 2 2 1
a b
01 1221223212122111121211122211222 结果a 新1 新2 新3... 01 1 2 1 2 1 1 2 2
2 2 2 1 2 1 2 2
2 3 1 1 1 1 1 2
1 2 2 1 2 2 1 就是如此排列第1个 第5个 第9个 第13个...
第2个 第6个 第10个
第3个 第7个 第11个
第4个 第8个 第12个
01 1221223212122111121211122211222
结果 a b1 b2 b3 b4 b5 b6 b7
01 1 2 1 2 1 1 2 2
2 2 2 1 2 1 2 2
2 3 1 1 1 1 1 2
1 2 2 1 2 2 1 就是如此排列
b
1221223212122111121211122211222
第1个数是1这里就排1 第5个数是2这里就排2 第9个数是1这里就排1 第13个...
第2个数是2这里就排2 第6个数是2这里就排2 第10个数是2这里就排2
第3个数是2这里就排2 第7个数是3这里就排3 第11个数是1这里就排1
第4个数是1这里就排1 第8个数是2这里就排2 第12个数是2这里就排2
然后有一条记录 01,1221223212122111121211122211222 对么?你说的真不是很明白a b1 b2 b3 b4 b5 b6 b7
01 1 2 1 2 1 1 2 2
2 2 2 1 2 1 2 2
2 3 1 1 1 1 1 2
1 2 2 1 2 2 1
这个也让人看不懂,太乱了,列和列之间间距大一点,对齐一点就好了。
01 1221223212122111121211122211222
INSERT @TB
SELECT '01', '1221223212122111121211122211222'SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO # FROM SYSCOLUMNSSELECT A,MAX(CASE WHEN SEQ=1 THEN B2 END) AS C1,
MAX(CASE WHEN SEQ=2 THEN B2 END) AS C2,
MAX(CASE WHEN SEQ=3 THEN B2 END) AS C3,
MAX(CASE WHEN SEQ=4 THEN B2 END) AS C4,ID2=IDENTITY(INT,1,1) INTO #T
FROM (
SELECT *,SUBSTRING(B,ID,1) AS B2,SEQ=ID-4*((ID-1)/4),SEQ2=(ID-1)/4 -- INTO #T
FROM @TB,#
WHERE LEN(B)>=ID
) A
GROUP BY A,SEQ2DECLARE @SQL VARCHAR(8000),@SQL2 VARCHAR(8000),@SQL3 VARCHAR(8000),@SQL4 VARCHAR(8000)
SET @SQL=''
SET @SQL2=''
SET @SQL3=''
SET @SQL4=''SELECT @SQL=@SQL+','+'MAX(CASE WHEN ID2='+RTRIM(ID2)+' THEN RTRIM(C1) ELSE '''' END) AS [C'+RTRIM(ID2)+']'
FROM #TSELECT @SQL2=@SQL2+','+'MAX(CASE WHEN ID2='+RTRIM(ID2)+' THEN RTRIM(C2) ELSE '''' END) AS [C'+RTRIM(ID2)+']'
FROM #TSELECT @SQL3=@SQL3+','+'MAX(CASE WHEN ID2='+RTRIM(ID2)+' THEN RTRIM(C3) ELSE '''' END) AS [C'+RTRIM(ID2)+']'
FROM #TSELECT @SQL4=@SQL4+','+'MAX(CASE WHEN ID2='+RTRIM(ID2)+' THEN RTRIM(C4) ELSE '''' END) AS [C'+RTRIM(ID2)+']'
FROM #TSET @SQL= 'SELECT A'+@SQL+' FROM #T GROUP BY A'
SET @SQL2= 'SELECT '''''+@SQL2+' FROM #T GROUP BY A'
SET @SQL3= 'SELECT '''''+@SQL3+' FROM #T GROUP BY A'
SET @SQL4= 'SELECT '''''+@SQL4+' FROM #T GROUP BY A'EXEC(@SQL+ ' UNION ALL ' + @SQL2+' UNION ALL '+ @SQL3+' UNION ALL '+ @SQL4)DROP TABLE #
DROP TABLE #T
/*
A C1 C2 C3 C4 C5 C6 C7 C8
---- ---- ---- ---- ---- ---- ---- ---- ----
01 1 2 1 2 1 1 2 2
2 2 2 1 2 1 2 2
2 3 1 1 1 1 1 2
1 2 2 1 2 2 1
*/
if object_id('tb') is not null drop table tb
go
create table [tb]([a] varchar(2),[b] varchar(39))
insert [tb]
select '01','1221223212122111121211122211222' union all
select '02','22222111121211111111111122222211111' union all
select '03','212121212111111112222212121221111222222'
go
--select * from tb
--获取最大列数
declare @n int
select @n=ceiling(max(len(b))/4.0) from tb
set rowcount @n
select identity(int) id into #1 from sysobjects a,sysobjects b
set rowcount 0
--整合原表
select * into #2
from
(
select *,id=1 from tb
union all select *,2 from tb
union all select *,3 from tb
union all select *,4 from tb
) t
order by a,id
--动态选取
declare @sql varchar(8000)
set @sql='select a=case id when 1 then a else '''' end '
select @sql=@sql+',b'+rtrim(id)+'=substring(b,4*'+rtrim(id-1)+'+id,1)'
from #1
set @sql=@sql+' from #2'
exec(@sql)
/*
a b1 b2 b3 b4 b5 b6 b7 b8 b9 b10
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01 1 2 1 2 1 1 2 2
2 2 2 1 2 1 2 2
2 3 1 1 1 1 1 2
1 2 2 1 2 2 1
02 2 2 1 1 1 1 2 2 1
2 1 2 1 1 1 2 2 1
2 1 1 1 1 1 2 1 1
2 1 2 1 1 1 2 1
03 2 2 2 1 1 2 1 2 1 2
1 1 1 1 2 2 2 1 2 2
2 2 1 1 2 1 1 1 2 2
1 1 1 1 2 2 2 1 2 (12 row(s) affected)
*/
drop table #1
drop table #2
select ceiling(max(len(b))/4.0) from tb --本例中为10select * into #
from
(
select *,id=1 from tb
union all select *,2 from tb
union all select *,3 from tb
union all select *,4 from tb
) t
order by a,id--select * from #select a=case id when 1 then a else '' end
,n1=substring(b,id,1)
,n2=substring(b,4+id,1)
,n3=substring(b,4*2+id,1)
,n4=substring(b,4*3+id,1)
,n5=substring(b,4*4+id,1)
,n6=substring(b,4*5+id,1)
,n7=substring(b,4*6+id,1)
,n8=substring(b,4*7+id,1)
,n9=substring(b,4*8+id,1)
,n10=substring(b,4*9+id,1)
from #
/*
a b1 b2 b3 b4 b5 b6 b7 b8 b9 b10
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01 1 2 1 2 1 1 2 2
2 2 2 1 2 1 2 2
2 3 1 1 1 1 1 2
1 2 2 1 2 2 1
02 2 2 1 1 1 1 2 2 1
2 1 2 1 1 1 2 2 1
2 1 1 1 1 1 2 1 1
2 1 2 1 1 1 2 1
03 2 2 2 1 1 2 1 2 1 2
1 1 1 1 2 2 2 1 2 2
2 2 1 1 2 1 1 1 2 2
1 1 1 1 2 2 2 1 2 (12 row(s) affected)
*/
drop table #