sql多列数据变成一列数据

bmbxhit 2018-05-22 03:02:39

现在想把图一的数据结构转换成图二的样子 保留linkid speeddate两个字段 把每一列的列名作为一个新的字段加入,如图二 t2等等照此连接在下方 哪位大神能说一下用哪个函数或是什么操作
...全文
2577 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
bmbxhit 2018-05-29
  • 打赏
  • 举报
回复 1
引用 18 楼 RINK_1 的回复:
[quote=引用 12 楼 bmbxhit 的回复:] [quote=引用 9 楼 mywisdom88 的回复:] -- 上面的写错了,忘记 from... -- 直接连接来不可以吗? select linkid,speeddate,t='t1',speed=t1 from 你表 union all select linkid,speeddate,t='t2',speed=t2 from 你表 union all select linkid,speeddate,t='t3',speed=t3 from 你表 union all select linkid,speeddate,t='t4',speed=t4 from 你表 union all select linkid,speeddate,t='t5',speed=t5 from 你表 union all select linkid,speeddate,t='t6',speed=t6 from 你表 union all select linkid,speeddate,t='t7',speed=t7 from 你表
太多了 100行 还有点麻烦[/quote] 如果列名是有规则的,都是t为前缀,后面跟数字,而且这数字肯定是从1开始连续递增的,那也可以用下面的试试。

declare @i int
declare @t int
declare @sql varchar(max)
set @i=1
set @t=120 --t开头的列数

while @i<=@t
begin
set @sql=isnull(@sql+' union all ','')+'select linkid,speeddate,'+'''t'+CAST(@i as varchar)+''' as t,t'+CAST(@i as varchar)+' from table'
set @i=@i+1
end

exec('select * from ('+@sql+') as A order by t')

[/quote] 谢谢各位!!!
bmbxhit 2018-05-29
  • 打赏
  • 举报
回复
引用 20 楼 shinger126 的回复:
[quote=引用 18 楼 RINK_1 的回复:] [quote=引用 12 楼 bmbxhit 的回复:] [quote=引用 9 楼 mywisdom88 的回复:] -- 上面的写错了,忘记 from... -- 直接连接来不可以吗? select linkid,speeddate,t='t1',speed=t1 from 你表 union all select linkid,speeddate,t='t2',speed=t2 from 你表 union all select linkid,speeddate,t='t3',speed=t3 from 你表 union all select linkid,speeddate,t='t4',speed=t4 from 你表 union all select linkid,speeddate,t='t5',speed=t5 from 你表 union all select linkid,speeddate,t='t6',speed=t6 from 你表 union all select linkid,speeddate,t='t7',speed=t7 from 你表
太多了 100行 还有点麻烦[/quote] 如果列名是有规则的,都是t为前缀,后面跟数字,而且这数字肯定是从1开始连续递增的,那也可以用下面的试试。

declare @i int
declare @t int
declare @sql varchar(max)
set @i=1
set @t=120 --t开头的列数

while @i<=@t
begin
set @sql=isnull(@sql+' union all ','')+'select linkid,speeddate,'+'''t'+CAST(@i as varchar)+''' as t,t'+CAST(@i as varchar)+' from table'
set @i=@i+1
end

exec('select * from ('+@sql+') as A order by t')

[/quote] print 有长度限制的,但是可以select出来,你试下就知道了 DECLARE @i INT DECLARE @sql NVARCHAR(MAX) SET @i=1 SET @sql='select linkid,speeddate,t1 t from table' WHILE @i<=94 BEGIN SET @i=@i+1 SET @sql=@sql+N' union all select linkid,speeddate,t'+CAST(@i AS NVARCHAR(2))+' t from table' END SET @sql=@sql+' order by linkid' PRINT @sql SELECT @sql[/quote] 非常好用 大神 非常感谢!!
shinger126 2018-05-23
  • 打赏
  • 举报
回复
引用 18 楼 RINK_1 的回复:
[quote=引用 12 楼 bmbxhit 的回复:] [quote=引用 9 楼 mywisdom88 的回复:] -- 上面的写错了,忘记 from... -- 直接连接来不可以吗? select linkid,speeddate,t='t1',speed=t1 from 你表 union all select linkid,speeddate,t='t2',speed=t2 from 你表 union all select linkid,speeddate,t='t3',speed=t3 from 你表 union all select linkid,speeddate,t='t4',speed=t4 from 你表 union all select linkid,speeddate,t='t5',speed=t5 from 你表 union all select linkid,speeddate,t='t6',speed=t6 from 你表 union all select linkid,speeddate,t='t7',speed=t7 from 你表
太多了 100行 还有点麻烦[/quote] 如果列名是有规则的,都是t为前缀,后面跟数字,而且这数字肯定是从1开始连续递增的,那也可以用下面的试试。

declare @i int
declare @t int
declare @sql varchar(max)
set @i=1
set @t=120 --t开头的列数

while @i<=@t
begin
set @sql=isnull(@sql+' union all ','')+'select linkid,speeddate,'+'''t'+CAST(@i as varchar)+''' as t,t'+CAST(@i as varchar)+' from table'
set @i=@i+1
end

exec('select * from ('+@sql+') as A order by t')

[/quote] print 有长度限制的,但是可以select出来,你试下就知道了 DECLARE @i INT DECLARE @sql NVARCHAR(MAX) SET @i=1 SET @sql='select linkid,speeddate,t1 t from table' WHILE @i<=94 BEGIN SET @i=@i+1 SET @sql=@sql+N' union all select linkid,speeddate,t'+CAST(@i AS NVARCHAR(2))+' t from table' END SET @sql=@sql+' order by linkid' PRINT @sql SELECT @sql
  • 打赏
  • 举报
回复
SELECT linkid,speeddate,t1,(t2+t3+t4+t5+t6+t7)as speed FROM table_name WHERE 1 LIMIT 10 这样不行吗? 还是我没理解对要求啊?怎么楼上各位写那么长
RINK_1 2018-05-22
  • 打赏
  • 举报
回复
引用 12 楼 bmbxhit 的回复:
[quote=引用 9 楼 mywisdom88 的回复:] -- 上面的写错了,忘记 from... -- 直接连接来不可以吗? select linkid,speeddate,t='t1',speed=t1 from 你表 union all select linkid,speeddate,t='t2',speed=t2 from 你表 union all select linkid,speeddate,t='t3',speed=t3 from 你表 union all select linkid,speeddate,t='t4',speed=t4 from 你表 union all select linkid,speeddate,t='t5',speed=t5 from 你表 union all select linkid,speeddate,t='t6',speed=t6 from 你表 union all select linkid,speeddate,t='t7',speed=t7 from 你表
太多了 100行 还有点麻烦[/quote] 如果列名是有规则的,都是t为前缀,后面跟数字,而且这数字肯定是从1开始连续递增的,那也可以用下面的试试。

declare @i int
declare @t int
declare @sql varchar(max)
set @i=1
set @t=120 --t开头的列数

while @i<=@t
begin
set @sql=isnull(@sql+' union all ','')+'select linkid,speeddate,'+'''t'+CAST(@i as varchar)+''' as t,t'+CAST(@i as varchar)+' from table'
set @i=@i+1
end

exec('select * from ('+@sql+') as A order by t')

mywisdom88 2018-05-22
  • 打赏
  • 举报
回复
-- 打印出来后,我这里也包错误,到T56这里,就不行了,我查长度才4011长 --测试数据 if not object_id(N'T') is null drop table T declare @s1 varchar(8000),@i int,@s2 varchar(8000) set @i=1 set @s1 = 'Create table T([linkid] int,[speeddate] Datetime' while @i<99 begin set @s1= @s1 + ',[t' + cast(@i as varchar(2)) +'] int' set @i=@i+1 end set @s1=@s1+')' exec(@s1) print @s1 set @s2 = 'insert into T ' + char(10) set @s2 = @s2 + 'select 1,'+''''+'2017-09-20'+'''' set @i=1 while @i<99 begin set @s2=@s2+','+cast(@i as varchar(2)) set @i=@i+1 end exec(@s2) print @s2 select * from t --测试数据结束 DECLARE @sql VARCHAR(8000) SELECT @sql=isnull(@sql+' union all ','')+char(10)+' select linkid,speeddate, [t]=' +quotename(Name,'''')+' , [speed] = '+quotename(Name)+' from T' FROM syscolumns WHERE Name!='linkid' AND Name!='speeddate' AND ID=object_id('T') ORDER BY colid print @sql --EXEC(@sql+' order by t') select len(@sql+' order by t')
mywisdom88 2018-05-22
  • 打赏
  • 举报
回复
select linkid,speeddate, [t]='t1' , [speed] = [t1] from T union all select linkid,speeddate, [t]='t2' , [speed] = [t2] from T union all select linkid,speeddate, [t]='t3' , [speed] = [t3] from T union all select linkid,speeddate, [t]='t4' , [speed] = [t4] from T union all select linkid,speeddate, [t]='t5' , [speed] = [t5] from T union all select linkid,speeddate, [t]='t6' , [speed] = [t6] from T union all select linkid,speeddate, [t]='t7' , [speed] = [t7] from T union all select linkid,speeddate, [t]='t8' , [speed] = [t8] from T union all select linkid,speeddate, [t]='t9' , [speed] = [t9] from T union all select linkid,speeddate, [t]='t10' , [speed] = [t10] from T union all select linkid,speeddate, [t]='t11' , [speed] = [t11] from T union all select linkid,speeddate, [t]='t12' , [speed] = [t12] from T union all select linkid,speeddate, [t]='t13' , [speed] = [t13] from T union all select linkid,speeddate, [t]='t14' , [speed] = [t14] from T union all select linkid,speeddate, [t]='t15' , [speed] = [t15] from T union all select linkid,speeddate, [t]='t16' , [speed] = [t16] from T union all select linkid,speeddate, [t]='t17' , [speed] = [t17] from T union all select linkid,speeddate, [t]='t18' , [speed] = [t18] from T union all select linkid,speeddate, [t]='t19' , [speed] = [t19] from T union all select linkid,speeddate, [t]='t20' , [speed] = [t20] from T union all select linkid,speeddate, [t]='t21' , [speed] = [t21] from T union all select linkid,speeddate, [t]='t22' , [speed] = [t22] from T union all select linkid,speeddate, [t]='t23' , [speed] = [t23] from T union all select linkid,speeddate, [t]='t24' , [speed] = [t24] from T union all select linkid,speeddate, [t]='t25' , [speed] = [t25] from T union all select linkid,speeddate, [t]='t26' , [speed] = [t26] from T union all select linkid,speeddate, [t]='t27' , [speed] = [t27] from T union all select linkid,speeddate, [t]='t28' , [speed] = [t28] from T union all select linkid,speeddate, [t]='t29' , [speed] = [t29] from T union all select linkid,speeddate, [t]='t30' , [speed] = [t30] from T union all select linkid,speeddate, [t]='t31' , [speed] = [t31] from T union all select linkid,speeddate, [t]='t32' , [speed] = [t32] from T union all select linkid,speeddate, [t]='t33' , [speed] = [t33] from T union all select linkid,speeddate, [t]='t34' , [speed] = [t34] from T union all select linkid,speeddate, [t]='t35' , [speed] = [t35] from T union all select linkid,speeddate, [t]='t36' , [speed] = [t36] from T union all select linkid,speeddate, [t]='t37' , [speed] = [t37] from T union all select linkid,speeddate, [t]='t38' , [speed] = [t38] from T union all select linkid,speeddate, [t]='t39' , [speed] = [t39] from T union all select linkid,speeddate, [t]='t40' , [speed] = [t40] from T union all select linkid,speeddate, [t]='t41' , [speed] = [t41] from T union all select linkid,speeddate, [t]='t42' , [speed] = [t42] from T union all select linkid,speeddate, [t]='t43' , [speed] = [t43] from T union all select linkid,speeddate, [t]='t44' , [speed] = [t44] from T union all select linkid,speeddate, [t]='t45' , [speed] = [t45] from T union all select linkid,speeddate, [t]='t46' , [speed] = [t46] from T union all select linkid,speeddate, [t]='t47' , [speed] = [t47] from T union all select linkid,speeddate, [t]='t48' , [speed] = [t48] from T union all select linkid,speeddate, [t]='t49' , [speed] = [t49] from T union all select linkid,speeddate, [t]='t50' , [speed] = [t50] from T union all select linkid,speeddate, [t]='t51' , [speed] = [t51] from T union all select linkid,speeddate, [t]='t52' , [speed] = [t52] from T union all select linkid,speeddate, [t]='t53' , [speed] = [t53] from T union all select linkid,speeddate, [t]='t54' , [speed] = [t54] from T union all select linkid,speeddate, [t]='t55' , [speed] = [t55] from T union all select linkid,speeddate, [t]='t56' , [speed] = [t56] fro
二月十六 版主 2018-05-22
  • 打赏
  • 举报
回复
把EXEC(@sql+' order by t') 改成PRINT(@sql+' order by t') ,看看拼接完的语句有什么问题
Hello World, 2018-05-22
  • 打赏
  • 举报
回复
看上去还是语法问题,你把生成的sql语句输出来看看
bmbxhit 2018-05-22
  • 打赏
  • 举报
回复
引用 8 楼 sinat_28984567 的回复:
[quote=引用 3 楼 bmbxhit 的回复:] 老提示order附近有错误
这个order前边得有空格,还有错误继续截图[/quote] 我如果t1到t96一起弄 就会报错 t1到t40一起做也会报错 但是我从t1到t32一起弄就可以了
bmbxhit 2018-05-22
  • 打赏
  • 举报
回复
引用 8 楼 sinat_28984567 的回复:
[quote=引用 3 楼 bmbxhit 的回复:]
老提示order附近有错误

这个order前边得有空格,还有错误继续截图[/quote]

还是报错 是不是列数太多 就不能这么弄 t一直从1到96
RINK_1 2018-05-22
  • 打赏
  • 举报
回复

select linkid,speeddate,'t1' as t,t1 from table
union all
select linkid,speeddate,'t2' as t,t2 from table
union all
select linkid,speeddate,'t3' as t,t2 from table
.
.
.
union all
select linkid,speeddate,'tn' as t,tn from table
bmbxhit 2018-05-22
  • 打赏
  • 举报
回复
引用 9 楼 mywisdom88 的回复:
-- 上面的写错了,忘记 from... -- 直接连接来不可以吗? select linkid,speeddate,t='t1',speed=t1 from 你表 union all select linkid,speeddate,t='t2',speed=t2 from 你表 union all select linkid,speeddate,t='t3',speed=t3 from 你表 union all select linkid,speeddate,t='t4',speed=t4 from 你表 union all select linkid,speeddate,t='t5',speed=t5 from 你表 union all select linkid,speeddate,t='t6',speed=t6 from 你表 union all select linkid,speeddate,t='t7',speed=t7 from 你表
太多了 100行 还有点麻烦
mywisdom88 2018-05-22
  • 打赏
  • 举报
回复
-- 上面的写错了,忘记 from... -- 直接连接来不可以吗? select linkid,speeddate,t='t1',speed=t1 from 你表 union all select linkid,speeddate,t='t2',speed=t2 from 你表 union all select linkid,speeddate,t='t3',speed=t3 from 你表 union all select linkid,speeddate,t='t4',speed=t4 from 你表 union all select linkid,speeddate,t='t5',speed=t5 from 你表 union all select linkid,speeddate,t='t6',speed=t6 from 你表 union all select linkid,speeddate,t='t7',speed=t7 from 你表
mywisdom88 2018-05-22
  • 打赏
  • 举报
回复
-- 直接连接来不可以吗? select linkid,speeddate,t='t1',speed=t1 union all select linkid,speeddate,t='t2',speed=t2 union all select linkid,speeddate,t='t3',speed=t3 union all select linkid,speeddate,t='t4',speed=t4 union all select linkid,speeddate,t='t5',speed=t5 union all select linkid,speeddate,t='t6',speed=t6 union all select linkid,speeddate,t='t7',speed=t7
bmbxhit 2018-05-22
  • 打赏
  • 举报
回复
引用 5 楼 apollokk 的回复:
你那order前少个空格
我测试的时候加了。。。。而且也是提示第一行有错误
二月十六 版主 2018-05-22
  • 打赏
  • 举报
回复
引用 3 楼 bmbxhit 的回复:
老提示order附近有错误
这个order前边得有空格,还有错误继续截图
Hello World, 2018-05-22
  • 打赏
  • 举报
回复
你那order前少个空格
bmbxhit 2018-05-22
  • 打赏
  • 举报
回复
引用 2 楼 sinat_28984567 的回复:
--测试数据
if not object_id(N'T') is null
	drop table T
Go
Create table T([linkid] int,[speeddate] Date,[t1] int,[t2] int,[t3] int)
Insert T
select 1,'2017-09-20',0,10,1 union all
select 2,'2017-09-21',28,0,15 union all
select 3,'2017-09-22',100,10,1
Go
--测试数据结束
DECLARE @sql VARCHAR(8000)  
SELECT @sql=isnull(@sql+' union all ','')+' select linkid,speeddate, [t]='  
+quotename(Name,'''')+' , [speed] = '+quotename(Name)+' from T'  
FROM syscolumns  
WHERE Name!='linkid' AND Name!='speeddate' AND ID=object_id('T')
ORDER BY colid  
EXEC(@sql+' order by t')  
老提示order附近有错误 是不是因为数据太长了 98列
bmbxhit 2018-05-22
  • 打赏
  • 举报
回复
老提示order附近有错误
加载更多回复(2)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧