新人求:不固定列转行语句。

qq747426 2018-01-22 11:02:26
name minor_id VALUE
YSFGGA 0 销售开票单头档
YSFGGA 1 发票号码
YSFGGA 2 发票性质
YSFGGA 3 发票种类
YSFGGA 4 发票来源
YSFGGA 5 发票日期
YSFGGA 6 客户
YSFGGA 7 部门
YSFGGA 8 业务员
YSFGGA 9 币种
YSFGGA 10 汇率
YSFGGA 11 税率
YSFGGA 12 专案
YSFGGA 13 预收款日
YSFGGA 14 备注
YSFGGA 15 凭证抛砖否
YSFGGA 16 凭证编号
YSFGGA 17 性质
YSFGGA 18 发票处理
YSFGGA 19 无税金额
YSFGGA 20 税额
YSFGGA 21 本币无税金额
YSFGGA 22 本币税额
YSFGGA 23 购买票据否
YSFGGA 24 现收
YSFGGA 25 现收金额
YSFGGA 26 结算方式
YSFGGA 27 票据号码
YSFGGA 28 收款单号
YSFGGA 29 制单人
YSFGGA 30 审核人
YSFGGA 31 预留
YSFGGA 32 审核码
YSFGGA 33 预留(复核编号)
YSFGGA 34 预留(复核姓名)
YSFGGA 35 红冲发票号码
YSFGGA 36 预留
YSFGGA 37 预留
YSFGGA 38 预留
YSFGGA 39 预留
YSFGGA 40 预留
YSFGGA 41 销售类型
YSFGGA 42 发出商品凭证抛转否
YSFGGA 43 发出商品凭证编号
YSFGGA 44 凭证类别
YSFGGA 45 发出商品凭证类别
YSFGGA 46 核销方式
YSFGGA 47 汇出状态
YSFGGA 48 金税发票代码
YSFGGA 49 金税发票号码
YSFGGA 50 录入者编号
YSFGGA 51 录入时间
YSFGGA 52 更改者编号
YSFGGA 53 更改时间
YSFGGA 54 更新标记
YSFGGA 55 自定文字1
YSFGGA 56 自定文字2
YSFGGA 57 自定文字3
YSFGGA 58 自定文字4
YSFGGA 59 自定文字5
YSFGGA 60 自定文字6
YSFGGA 61 自定数字1
YSFGGA 62 自定数字2
YSFGGA 63 自定数字3
YSFGGA 64 自定数字4
YSFGGA 65 自定数字5
YSFGGA 66 自定数字6
YSFGGA 67 送审状态
YSFGGA 68 送审人
YSFGGA 69 审批人1
YSFGGA 70 审批结果
YSFGGA 71 审批意见
YSFGGA 72 审批人2
YSFGGA 73 审批结果
YSFGGA 74 审批意见
YSFGGA 75 审批人3
YSFGGA 76 审批结果
YSFGGA 77 审批意见
YSFGGA 78 审批人4
YSFGGA 79 审批结果
YSFGGA 80 审批意见
YSFGGA 81 审批人5
YSFGGA 82 审批结果
YSFGGA 83 审批意见
YSFGGA 84 审批人6
YSFGGA 85 审批结果
YSFGGA 86 审批意见
YSFGGA 87 终审人
YSFGGA 88 审批结果
YSFGGA 89 审批意见
YSFGGA 90 通知人1
YSFGGA 91 通知人2
YSFGGA 92 通知人3
TPABBA 0 币种汇率档
TPABBA 1 币种编号
TPABBA 2 货币名称
TPABBA 3 汇率
TPABBA 4 英文名称
TPABBA 5 预留字段1
TPABBA 6 预留字段2
TPABBA 7 录入者编号
TPABBA 8 录入时间
TPABBA 9 更改者编号
TPABBA 10 更改时间
TPABBA 11 更新标记
JSKLFB 0 组合单身档
JSKLFB 1 单号
JSKLFB 2 序号
JSKLFB 3 子件品号
JSKLFB 4 仓库
JSKLFB 5 组成量
JSKLFB 6 单位成本
JSKLFB 7 成本
JSKLFB 8 审核码
JSKLFB 9 预留
JSKLFB 10 预留
JSKLFB 11 (预留)日期
JSKLFB 12 预留字段
JSKLFB 13 预留字段
JSKLFB 14 预留字段
JSKLFB 15 预留字段
JSKLFB 16 预留字段
JSKLFB 17 预留字段
JSKLFB 18 预留字段
JSKLFB 19 预留字段
JSKLFB 20 预留字段
JSKLFB 21 预留(品名)
JSKLFB 22 批号
JSKLFB 23 规格
JSKLFB 24 录入者编号
JSKLFB 25 录入时间
JSKLFB 26 更改者编号
JSKLFB 27 更改时间
JSKLFB 28 更新标记
JSKLFB 29 自定文字1
JSKLFB 30 自定文字2
JSKLFB 31 自定文字3
JSKLFB 32 自定文字4
JSKLFB 33 自定文字5
JSKLFB 34 自定文字6
JSKLFB 35 自定数字1
JSKLFB 36 自定数字2
JSKLFB 37 自定数字3
JSKLFB 38 自定数字4
JSKLFB 39 自定数字5
JSKLFB 40 自定数字6
以上是部分表的说明和表字段注释,为3列状态,现在想列转行,是属于不固定列转行,我试过EXCEL 选择性粘贴-转置但内容太多了,所以开始研究。但怎么研究都搞不来。
首先minor_id=0的时候是表名,其他的1~N是字段名,变成下列样式,minor_id小于等于150
表名	字段0	字段1	字段2	字段3	字段4	字段5	字段6	字段7	字段8	字段9	字段10	字段11	字段12	字段13	字段14	字段15	字段16	字段17	字段18	字段19
YSFGGA 销售开票单头档 发票号码 发票性质 发票种类 发票来源 发票日期 客户 部门 业务员 币种 汇率 税率 专案 预收款日 备注 凭证抛砖否 凭证编号 性质 发票处理 无税金额
TPABBA 币种汇率档 币种编号 货币名称 汇率 英文名称 预留字段1 预留字段2 录入者编号 录入时间 更改者编号 更改时间 更新标记


...全文
1824 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
shinger126 2018-05-21
  • 打赏
  • 举报
回复
CREATE TABLE #t(tname VARCHAR(30),minor_id INT,val VARCHAR(30) PRIMARY KEY CLUSTERED (tname,minor_id)) INSERT INTO #t SELECT name,0,name FROM sysobjects WHERE xtype='U' UNION ALL SELECT OBJECT_NAME(id),colid,name FROM syscolumns a WHERE EXISTS(SELECT 1 FROM sysobjects b WHERE b.id=a.id AND b.xtype='U') DECLARE @sql VARCHAR(MAX) SET @sql=( SELECT ','+ 'max(case when minor_id = '+CAST(minor_id AS VARCHAR(10))+' then val else '''' end) as 字段'+CAST(minor_id AS VARCHAR(10)) FROM (SELECT DISTINCT minor_id FROM #t WHERE minor_id>0) t ORDER BY minor_id FOR XML PATH('')) PRINT @sql SET @sql='select tname'+@sql+' from #t group by tname' EXEC( @sql ) 自己拿去修改一下,前面创建表和插入数据只是我这边没数据创造模拟数据的
shinger126 2018-05-18
  • 打赏
  • 举报
回复
引用 6 楼 corw_by 的回复:
我也想问:要是要把这些查出来的数据放到一个表里,列该怎么建? 数据怎么存? 存json格式又比较麻烦,有没有比较好的办法啊?
等周一吧,下班了,不写了
lueyeo 2018-05-18
  • 打赏
  • 举报
回复
我也想问:要是要把这些查出来的数据放到一个表里,列该怎么建? 数据怎么存? 存json格式又比较麻烦,有没有比较好的办法啊?
qq_25073223 2018-02-09
  • 打赏
  • 举报
回复
动态列,应该采用动态拼接sql语句吧, http://www.maomao365.com/?p=5471 看上面,动态列的转换方法,使用了while 循环表生成动态列.
jaki-egg 2018-01-31
  • 打赏
  • 举报
回复
if object_id('tb') is not null drop table tb
go
create table tb(姓名 varchar(10),课程 varchar(10),分数 int)
insert into tb values('张三','语文',74)
insert into tb values('张三','数学',83)
insert into tb values('张三','物理',93)
insert into tb values('李四','语文',74)
insert into tb values('李四','数学',84)
insert into tb values('李四','物理',94)
go
select* from tb
go
-------------------------------使用SQL Server 2000静态SQL
select 姓名,
 max(case 课程 when'语文' then 分数 else 0 end) 语文,
 max(case 课程 when '数学' then 分数 else 0 end) 数学,
 max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名

-------------------------------使用SQL Server 2000动态SQL
-------变量按sql语言顺序赋值
declare @sql varchar(500)
set @sql='select 姓名'
select @sql=@sql+',max(case 课程 when '''+课程+''' then 分数 else 0 end)['+课程+']'
from(select distinct 课程 from tb) a--同from tb group by课程,默认按课程名排序
print @sql
set @sql=@sql+' from tb group by 姓名'
exec(@sql)

-------使用isnull(),变量先确定动态部分
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+' max(case 课程 when '''+课程+''' then 分数 else 0 end) ['+课程+']'
from(select distinct 课程 from tb) as a  
print @sql    
set @sql='select 姓名,'+@sql+' from tb group by 姓名'
exec(@sql)

-------------------------------使用SQL Server 2005静态SQL
select * from tb pivot(max(分数) for 课程 in (语文,数学,物理)) a

-------------------------------使用SQL Server 2005动态SQL
-------使用stuff()
declare @sql varchar(8000)
set @sql=''  --初始化变量@sql
select @sql=@sql+','+课程 from tb group by 课程--变量多值赋值
print @sql-----------------,数学,物理,语文
set @sql=stuff(@sql,1,1,'')-------去掉首个','
print @sql
set @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+')) a'
exec(@sql)

-------使用isnull()
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+课程 from tb group by 课程
print @sql 
set @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+')) a'
print @sql
exec(@sql)

-------------------------------行转列结果加上总分、平均分
-------SQL SERVER 2000静态SQL
select 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学 'then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
sum(分数) 总分,
cast(avg(分数*1.0) as decimal(18,2))平均分
from tb
group by 姓名

-------SQL SERVER 2000动态SQL
declare @sql varchar(8000)
set @sql='select 姓名'
select @sql=@sql+',max(case 课程 when '''+课程+''' then 分数 else 0 end)['+课程+']'
from (select distinct 课程 from tb) a
set @sql=@sql+',sum(分数) 总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名'
print @sql
exec(@sql)

-------使用SQL Server 2005静态SQL
select m.*,n.总分,n.平均分
from
(select * from tb pivot(max(分数) for 课程 in(语文,数学,物理)) a) m,
(select 姓名,sum(分数) 总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分
from tb
group by 姓名) n
where m.姓名=n.姓名

-------------------------------使用SQL Server 2005动态SQL
-------使用stuff()
declare @sql varchar(8000)
set @sql=''  --初始化变量@sql
select @sql=@sql+','+课程 from tb group by 课程--变量多值赋值---同select @sql = @sql + ','+课程from (select distinct课程from tb)a
print @sql
set @sql=stuff(@sql,1,1,'')--去掉首个','
set @sql='select m.* , n.总分,n.平均分 from
(select * from tb pivot (max(分数) for 课程 in ('+@sql+')) a) m ,
(select 姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名) n
where m.姓名= n.姓名'
print @sql
exec(@sql)

-------使用isnull()
declare @sql varchar(8000)
select @sql=ISNULL(@sql+',','')+课程 from tb group by 课程
print @sql
set @sql='select m.*,n.总分,n.平均分 from
(select * from tb pivot(max(分数) for 课程 in ('+@sql+')) a) m,
(select 姓名,SUM(分数) 总分,CAST(AVG(分数) as decimal(18,2)) 平均分 from tb group by 姓名) n
where m.姓名=n.姓名'
exec(@sql)
全套一起给你了!
WIKESOFT 2018-01-25
  • 打赏
  • 举报
回复


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Point]') AND type in (N'U'))
DROP TABLE [dbo].[Point]
GO


CREATE TABLE [dbo].[Point](
	[Id] [uniqueidentifier] NULL,
	[Name] [varchar](50) NULL,
	[ScoreName] [varchar](50) NULL,
	[Point] [decimal](18, 2) NULL
) ON [PRIMARY]

GO

 

insert into Point(Id,name,ScoreName,point)values(newid(),'张三','语文',71)
insert into Point(Id,name,ScoreName,point)values(newid(),'张三','数学',72)
insert into Point(Id,name,ScoreName,point)values(newid(),'李四','语文',73)
insert into Point(Id,name,ScoreName,point)values(newid(),'李四','数学',74)


declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',max(case ScoreName when '''+ScoreName+''' then point else 0 end) ['+ScoreName+']'
from (select ScoreName from POINT Group by ScoreName)  t
set @sql = @sql + ' from POINT group by name'
         +' '
print @sql
exec(@sql)
文盲老顾 2018-01-23
  • 打赏
  • 举报
回复
select * into #t from t  -- 获取字段列数据导入到临时表
--select name as 表名,[0] as 字段0 from t a
--pivot(max(val) for minor_id in ([0],[1])) b
declare @m int,@sql nvarchar(max),@n int
select @m = 150,@n=0,@sql = 'select name as 表名,[FieldAlias] from #t a pivot(max(val) for minor_id in ([FieldList])) b'
while @n<@m
	begin
		if exists(select top 1 1 from #t where minor_id=@n)
			begin
				set @sql=replace(replace(@sql,'[FieldList]','['+convert(varchar(4),@n)+'],[FieldList]'),'[FieldAlias]','['+convert(varchar(4),@n)+'] as 字段' +convert(varchar(4),@n)+ ',[FieldAlias]')
			end
		set @n=@n+1
	end
set @sql = replace(replace(@sql,',[FieldList]',''),',[FieldAlias]','')
--select @sql
exec(@sql)
drop table #t
中国风 2018-01-22
  • 打赏
  • 举报
回复

34,575

社区成员

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

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