数字的行转列？

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           5
1           6           7           8           9           10
2           11          12          13          14          15
3           16          17          18          19          20
...
197         986         987         988         989         990
198         991         992         993         994         995
199         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

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 1000
select * from #testdeclare @str varchar(max)
set @str=''
select @str=@str+','+'[col'+ltrim(title1)+']'+'=max(case when title1='+
QUOTENAME(title1,'''')+' then number else 0 end)' from #test
group by title1
exec('select col1,col2,col3,col4,col5 from(
select title2'+@str+' from #test group by title2)a')
/*
col1 col2 col3 col4 col5
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
26 27 28 29 30
31 32 33 34 35
36 37 38 39 40
41 42 43 44 45
46 47 48 49 50
51 52 53 54 55
56 57 58 59 60
61 62 63 64 65
66 67 68 69 70
71 72 73 74 75
76 77 78 79 80
81 82 83 84 85
86 87 88 89 90
91 92 93 94 95
96 97 98 99 100
101 102 103 104 105
106 107 108 109 110
111 112 113 114 115
116 117 118 119 120
121 122 123 124 125
126 127 128 129 130
131 132 133 134 135
136 137 138 139 140
141 142 143 144 145
146 147 148 149 150
151 152 153 154 155
156 157 158 159 160
161 162 163 164 165
166 167 168 169 170
171 172 173 174 175
176 177 178 179 180
181 182 183 184 185
186 187 188 189 190
191 192 193 194 195
196 197 198 199 200
201 202 203 204 205
......
......
911 912 913 914 915
916 917 918 919 920
921 922 923 924 925
926 927 928 929 930
931 932 933 934 935
936 937 938 939 940
941 942 943 944 945
946 947 948 949 950
951 952 953 954 955
956 957 958 959 960
961 962 963 964 965
966 967 968 969 970
971 972 973 974 975
976 977 978 979 980
981 982 983 984 985
986 987 988 989 990
991 992 993 994 995
996 997 998 999 1000
*/

行列转换参考资料，楼主根据实际情况选择：
--行列互转
--摘自中国风博客，引用请标明内容来源
--1、行换列
if object_id('Class') is not  null
    drop table Class
Go
Create table Class(
[Student] nvarchar(2),
[Course] nvarchar(2),
[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='

    +quotename([Course],'''')+' then [Score] else 0 end)'
from
    Class group by[Course]
--select @s
exec('select [Student]'+@s+' from Class group by [Student]')
--生成静态:
select
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
    Class
group by [Student]
GO
--动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
select @s
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')--生成静态:
select *
from
    Class
pivot
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b--生成格式:
/*
Student 数学         物理         英语         语文
------- ----------- ----------- ----------- -----------
李四     77          85          65          65
张三     87          90          82          78(2 行受影响)
*/go
--加上总成绩(学科平均分)--2000方法:
--动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+'then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end),
    [总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))
from
    Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select  @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
--isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')--生成静态:select
    [Student],[数学],[物理],[英语],[语文],[总成绩]
from
    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学         物理         英语         语文         总成绩
------- ----------- ----------- ----------- ----------- -----------
李四     77          85          65          65          292
张三     87          90          82          78          337(2 行受影响)
*/go--2、列转行

if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
    Student,[Course],[Score]
from
    Class
unpivot
    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四     数学     77
李四     物理     85
李四     英语     65
李四     语文     65
张三     数学     87
张三     物理     90
张三     英语     82
张三     语文     78
*/

如果是3起点，首先你我不清楚3是否第一格，或许像日历星期日永远在第一列也未定。其次，如果3是第一格，1起点number-1,3起点number-3不就完事了，还动态调字段，我晕，5列好调，如果输出100列岂不是调死人。

从3开始的话：select number, ((number-3)%5)+1 as title1,
((number-3)/5)+1 as title2 into #t from master..spt_values
where type='P' and number between 3 and 1002
select * from #tdeclare @str varchar(max)
set @str=''
select @str=@str+','+'[col'+ltrim(title1)+']'+'=max(case when title1='+
QUOTENAME(title1,'''')+' then number else 0 end)' from #t
group by title1
exec('select col1,col2,col3,col4,col5 from(
select title2'+@str+' from #t group by title2)a')
--结果我已经看了，就不贴出来了

调试易

数字的行转列？

解决方案 »