数字的行转列？
有连续的数字，比如1到1000,
要求按顺序转为5列，输出的结果为
第1行:1,2,3,4,5
第2行:6,7,8,9,10
第3行:11,12,13,14,15
....................
即按每5个换行,顺序输出.
------解决方案--------------------
- SQL code
with cte(n,mod,row) as( select number, (number-1)%5, (number-1)/5 from master..spt_values where type='P' and number between 1 and 1000)select * from cte t pivot (max(n) for mod in ([0],[1],[2],[3],[4])) p/*row 0 1 2 3 4----------- ----------- ----------- ----------- ----------- -----------0 1 2 3 4 51 6 7 8 9 102 11 12 13 14 153 16 17 18 19 20...197 986 987 988 989 990198 991 992 993 994 995199 996 997 998 999 1000*/
------解决方案--------------------
1楼有点遐思，如果从3-1002....
with t(n) as
(
select number from master..spt_values where type='P' and number between 3 and 1002
)
,t1 as (select *,n%5 as c from t)
, t2 as(select *,ROW_NUMBER() over(PARTITION BY c order by n) rownum from t1)
select [3] c1,[4] c2,[0] c3,[1] c4,[2] c5--这里决定顺序,如果要动态变，需要用动态SQL语句写查询改变查询顺序
from(select * from t2) src
pivot (max(n) for c in([0],[1],[2],[3],[4]))pvt
------解决方案--------------------
- SQL code
select number, ((number-1)%5)+1 as title1, ((number-1)/5)+1 as title2 into #test from master..spt_values where type='P' and number between 1 and 1000select * from #testdeclare @str varchar(max)set @str=''select @[email protected]+','+'[col'+ltrim(title1)+']'+'=max(case when title1='+QUOTENAME(title1,'''')+' then number else 0 end)' from #test group by title1exec('select col1,col2,col3,col4,col5 from(select [email protected]+' from #test group by title2)a')/*col1 col2 col3 col4 col51 2 3 4 56 7 8 9 1011 12 13 14 1516 17 18 19 2021 22 23 24 2526 27 28 29 3031 32 33 34 3536 37 38 39 4041 42 43 44 4546 47 48 49 5051 52 53 54 5556 57 58 59 6061 62 63 64 6566 67 68 69 7071 72 73 74 7576 77 78 79 8081 82 83 84 8586 87 88 89 9091 92 93 94 9596 97 98 99 100101 102 103 104 105106 107 108 109 110111 112 113 114 115116 117 118 119 120121 122 123 124 125126 127 128 129 130131 132 133 134 135136 137 138 139 140141 142 143 144 145146 147 148 149 150151 152 153 154 155156 157 158 159 160161 162 163 164 165166 167 168 169 170171 172 173 174 175176 177 178 179 180181 182 183 184 185186 187 188 189 190191 192 193 194 195196 197 198 199 200201 202 203 204 205............911 912 913 914 915916 917 918 919 920921 922 923 924 925926 927 928 929 930931 932 933 934 935936 937 938 939 940941 942 943 944 945946 947 948 949 950951 952 953 954 955956 957 958 959 960961 962 963 964 965966 967 968 969 970971 972 973 974 975976 977 978 979 980981 982 983 984 985986 987 988 989 990991 992 993 994 995996 997 998 999 1000*/