用sql把多个列以,分隔的转为多行，求各解决方案，感谢。

.NET快速开发框架
博客专家认证
2014-07-23 09:32:38
现在sql的样例数据如下：
共6列数据

贵人鸟 603555 11.8,12.2,12.6 2014-01-15 7100000,7100000,7100000 宋子明
贵人鸟 603555 10.38,10.88,10.58 2014-01-15 21000000,7000000,14000000 严琳

部分数据如下图所示：

现在我要用sql转换成下面的这个样子（列部分样例数据），数据库是Sqlserver

贵人鸟 603555 11.8 2014-01-15 7100000 宋子明
贵人鸟 603555 12.2 2014-01-15 7100000 宋子明
贵人鸟 603555 12.6 2014-01-15 7100000 宋子明
贵人鸟 603555 10.38 2014-01-15 21000000 严琳
贵人鸟 603555 10.88 2014-01-15 7000000 严琳
贵人鸟 603555 10.58 2014-01-15 14000000 严琳


看各位如何解决这个问题呀？多讨论讨论，寻最好的方式处理，注意数据库是Sqlserver，要用sql实现！
...全文
449 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
發糞塗牆 2014-07-24
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-07-23 11:19:32
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang](ID INT ,[VAR0] varchar(6),[VAR1] int,[VAR2] varchar(15),[VAR3] varchar(14),[VAR4] date,[VAR5] date,[VAR6] date,[VAR7] varchar(26))
insert [huang]
select 1,'贵人鸟',603555,'10.9,10.78,10.6','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,23000000,24000000' union all
select 2,'贵人鸟',603555,'10.7,11.,10.88','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select 3,'贵人鸟',603555,'10.8,11.1,10.98','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select 4,'贵人鸟',603555,'10.68,10.5,10.8','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,24000000,23000000'
--------------开始查询--------------------------
SELECT DISTINCT a.ID,a.var0,a.var1,a.var2,b.var3,a.var4,a.var5,a.var6,c.VAR7
FROM (
select id,
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4],	[VAR5],[VAR6],ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)id2
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR2])
    and type='p' 
    and substring(','+[VAR2],number,1)=',')a INNER JOIN (
SELECT 	id,
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR3],number,CHARINDEX(',',[VAR3]+',',number)-number) as [VAR3],ROW_NUMBER()OVER(PARTITION BY id  ORDER BY id)id2
from
    huang a,master..spt_values 
WHERE
    number >=1 and number<=len([VAR3])
    and type='p' 
    and substring(','+[VAR3],number,1)=',')b ON a.id=b.id AND a.id2=b.id2 INNER JOIN (
	select id,
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7],ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)id2 
	FROM
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR7])
    and type='p' 
    and substring(','+[VAR7],number,1)=',')c ON a.id=c.id AND a.id2=c.id2 

----------------结果----------------------------
/* 
ID          var0   var1        var2            var3           var4       var5       var6       VAR7
----------- ------ ----------- --------------- -------------- ---------- ---------- ---------- --------------------------
1           贵人鸟    603555      10.6            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
1           贵人鸟    603555      10.78           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
1           贵人鸟    603555      10.9            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
2           贵人鸟    603555      10.7            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
2           贵人鸟    603555      10.88           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
2           贵人鸟    603555      11.             10.6           2014-01-15 2014-01-14 2014-01-15 23000000
3           贵人鸟    603555      10.8            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
3           贵人鸟    603555      10.98           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
3           贵人鸟    603555      11.1            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
4           贵人鸟    603555      10.5            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
4           贵人鸟    603555      10.68           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
4           贵人鸟    603555      10.8            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
*/
.NET快速开发框架 2014-07-24
  • 打赏
  • 举报
 回复
引用 15 楼 DBA_Huangzj 的回复:
有ID就好办一点，列出来看看
专家ID值班就是从1依次增加的自增值。
.NET快速开发框架 2014-07-24
  • 打赏
  • 举报
 回复
引用 17 楼 DBA_Huangzj 的回复:
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-07-23 11:19:32
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang](ID INT ,[VAR0] varchar(6),[VAR1] int,[VAR2] varchar(15),[VAR3] varchar(14),[VAR4] date,[VAR5] date,[VAR6] date,[VAR7] varchar(26))
insert [huang]
select 1,'贵人鸟',603555,'10.9,10.78,10.6','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,23000000,24000000' union all
select 2,'贵人鸟',603555,'10.7,11.,10.88','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select 3,'贵人鸟',603555,'10.8,11.1,10.98','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select 4,'贵人鸟',603555,'10.68,10.5,10.8','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,24000000,23000000'
--------------开始查询--------------------------
SELECT DISTINCT a.ID,a.var0,a.var1,a.var2,b.var3,a.var4,a.var5,a.var6,c.VAR7
FROM (
select id,
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4],	[VAR5],[VAR6],ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)id2
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR2])
    and type='p' 
    and substring(','+[VAR2],number,1)=',')a INNER JOIN (
SELECT 	id,
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR3],number,CHARINDEX(',',[VAR3]+',',number)-number) as [VAR3],ROW_NUMBER()OVER(PARTITION BY id  ORDER BY id)id2
from
    huang a,master..spt_values 
WHERE
    number >=1 and number<=len([VAR3])
    and type='p' 
    and substring(','+[VAR3],number,1)=',')b ON a.id=b.id AND a.id2=b.id2 INNER JOIN (
	select id,
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7],ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)id2 
	FROM
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR7])
    and type='p' 
    and substring(','+[VAR7],number,1)=',')c ON a.id=c.id AND a.id2=c.id2 

----------------结果----------------------------
/* 
ID          var0   var1        var2            var3           var4       var5       var6       VAR7
----------- ------ ----------- --------------- -------------- ---------- ---------- ---------- --------------------------
1           贵人鸟    603555      10.6            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
1           贵人鸟    603555      10.78           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
1           贵人鸟    603555      10.9            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
2           贵人鸟    603555      10.7            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
2           贵人鸟    603555      10.88           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
2           贵人鸟    603555      11.             10.6           2014-01-15 2014-01-14 2014-01-15 23000000
3           贵人鸟    603555      10.8            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
3           贵人鸟    603555      10.98           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
3           贵人鸟    603555      11.1            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
4           贵人鸟    603555      10.5            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
4           贵人鸟    603555      10.68           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
4           贵人鸟    603555      10.8            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
*/
哥们这个是正解呀，感谢哥们的解答，辛苦你了～！ 随后会给全分。
.NET快速开发框架 2014-07-23
  • 打赏
  • 举报
 回复
引用 6 楼 DBA_Huangzj 的回复:
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-07-23 11:19:32
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([VAR0] varchar(6),[VAR1] int,[VAR2] varchar(15),[VAR3] varchar(14),[VAR4] date,[VAR5] date,[VAR6] date,[VAR7] varchar(26))
insert [huang]
select '贵人鸟',603555,'10.9,10.78,10.6','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,23000000,24000000' union all
select '贵人鸟',603555,'10.7,11.,10.88','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select '贵人鸟',603555,'10.8,11.1,10.98','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select '贵人鸟',603555,'10.68,10.5,10.8','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,24000000,23000000'
--------------开始查询--------------------------

SELECT DISTINCT a.[VAR0],a.[VAR1],a.[VAR2],b.[VAR3],a.VAR4,a.[VAR5],[VAR6],c.[VAR7]
FROM (select
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4],	[VAR5],[VAR6]
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR2])
    and type='p' 
    and substring(','+[VAR2],number,1)=',')a INNER JOIN (
select
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR3],number,CHARINDEX(',',[VAR3]+',',number)-number) as [VAR3]
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR3])
    and type='p' 
    and substring(','+[VAR3],number,1)=',')b ON a.var0=b.var0 AND a.var1=b.var1
INNER JOIN (
	select
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7]
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR7])
    and type='p' 
    and substring(','+[VAR7],number,1)=',') c ON a.var0=c.var0 AND a.var1=c.var1

----------------结果----------------------------
/* 
VAR0   VAR1        VAR2            VAR3           VAR4       VAR5       VAR6       VAR7
------ ----------- --------------- -------------- ---------- ---------- ---------- --------------------------
贵人鸟    603555      10.5            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.5            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.6            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.6            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.68           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.68           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.7            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.7            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.78           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.78           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.8            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.8            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.88           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.88           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.9            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.9            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.98           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.98           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      11.             10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      11.             10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      11.1            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      11.1            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
*/
万分感谢。
發糞塗牆 2014-07-23
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-07-23 11:19:32
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([VAR0] varchar(6),[VAR1] int,[VAR2] varchar(15),[VAR3] varchar(14),[VAR4] date,[VAR5] date,[VAR6] date,[VAR7] varchar(26))
insert [huang]
select '贵人鸟',603555,'10.9,10.78,10.6','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,23000000,24000000' union all
select '贵人鸟',603555,'10.7,11.,10.88','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select '贵人鸟',603555,'10.8,11.1,10.98','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select '贵人鸟',603555,'10.68,10.5,10.8','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,24000000,23000000'
--------------开始查询--------------------------

SELECT DISTINCT a.[VAR0],a.[VAR1],a.[VAR2],b.[VAR3],a.VAR4,a.[VAR5],[VAR6],c.[VAR7]
FROM (select
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4],	[VAR5],[VAR6]
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR2])
    and type='p' 
    and substring(','+[VAR2],number,1)=',')a INNER JOIN (
select
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR3],number,CHARINDEX(',',[VAR3]+',',number)-number) as [VAR3]
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR3])
    and type='p' 
    and substring(','+[VAR3],number,1)=',')b ON a.var0=b.var0 AND a.var1=b.var1
INNER JOIN (
	select
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7]
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR7])
    and type='p' 
    and substring(','+[VAR7],number,1)=',') c ON a.var0=c.var0 AND a.var1=c.var1

----------------结果----------------------------
/* 
VAR0   VAR1        VAR2            VAR3           VAR4       VAR5       VAR6       VAR7
------ ----------- --------------- -------------- ---------- ---------- ---------- --------------------------
贵人鸟    603555      10.5            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.5            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.6            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.6            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.68           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.68           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.7            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.7            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.78           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.78           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.8            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.8            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.88           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.88           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.9            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.9            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.98           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.98           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      11.             10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      11.             10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      11.1            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      11.1            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
*/
.NET快速开发框架 2014-07-23
  • 打赏
  • 举报
 回复
实际情况还有点点小麻烦，还有几个字段也是这种情况。帮我修改下哟，我把分全给你。感谢哥们。
数据如下，我再给你截图看下

贵人鸟 603555 10.9,10.78,10.6 10.6,10.6,10.6 2014-01-15 2014-01-14 2014-01-15 23000000,23000000,24000000
贵人鸟 603555 10.7,11.,10.88 10.6,10.6,10.6 2014-01-15 2014-01-14 2014-01-15 24000000,23000000,23000000
贵人鸟 603555 10.8,11.1,10.98 10.6,10.6,10.6 2014-01-15 2014-01-14 2014-01-15 24000000,23000000,23000000
贵人鸟 603555 10.68,10.5,10.8 10.6,10.6,10.6 2014-01-15 2014-01-14 2014-01-15 23000000,24000000,23000000



麻烦哥们再帮修改下，以满足上面这个要求！
.NET快速开发框架 2014-07-23
  • 打赏
  • 举报
 回复
引用 2 楼 DBA_Huangzj 的回复:
----------------------------------------------------------------

-- Author  :DBA_HuangZJ(發糞塗牆)

-- Date    :2014-07-23 10:18:08

-- Version:

--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 

--	May 14 2014 18:34:29 

--	Copyright (c) Microsoft Corporation

--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

--

----------------------------------------------------------------

--> 测试数据：[hunag]

if object_id('[hunag]') is not null drop table [hunag]

go 

create table [hunag]([VAR0] varchar(6),[VAR1] int,[VAR2] varchar(17),[VAR4] datetime,[VAR7] varchar(25),[VAR8] varchar(6))

insert [hunag]

select '贵人鸟',603555,'11.8,12.2,12.6','2014-01-15','7100000,7100000,7100000','宋子明' union all

select '贵人鸟',603555,'10.38,10.88,10.58','2014-01-15','21000000,7000000,14000000','严琳'

--------------开始查询--------------------------

SELECT DISTINCT  A.[VAR0],A.[VAR1],A.[VAR2],A.[VAR4],B.[VAR7],A.[VAR8]

FROM 

(select

    [VAR0], 

    a.[VAR1], 

    SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4],

	--SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number)[VAR7],

	[VAR8]

from

    [hunag] a,master..spt_values 

where

    number >=1 and number<=len([VAR2])

    and type='p' 

    and substring(','+[VAR2],number,1)=',')A

INNER JOIN (select

    [VAR0], 

    a.[VAR1], 

    SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7], [VAR4],

	[VAR8]

FROM

    [hunag] a,master..spt_values 

where

    number >=1 and number<=len([VAR7])

    and type='p' 

    and substring(','+[VAR7],number,1)=',')B ON A.VAR0=B.VAR0 AND A.VAR1=B.VAR1 AND A.VAR4=B.VAR4 AND A.VAR8=B.VAR8



----------------结果----------------------------

/* 

VAR0   VAR1        VAR2              VAR4                    VAR7                      VAR8

------ ----------- ----------------- ----------------------- ------------------------- ------

贵人鸟    603555      10.38             2014-01-15 00:00:00.000 14000000                  严琳

贵人鸟    603555      10.38             2014-01-15 00:00:00.000 21000000                  严琳

贵人鸟    603555      10.38             2014-01-15 00:00:00.000 7000000                   严琳

贵人鸟    603555      10.58             2014-01-15 00:00:00.000 14000000                  严琳

贵人鸟    603555      10.58             2014-01-15 00:00:00.000 21000000                  严琳

贵人鸟    603555      10.58             2014-01-15 00:00:00.000 7000000                   严琳

贵人鸟    603555      10.88             2014-01-15 00:00:00.000 14000000                  严琳

贵人鸟    603555      10.88             2014-01-15 00:00:00.000 21000000                  严琳

贵人鸟    603555      10.88             2014-01-15 00:00:00.000 7000000                   严琳

贵人鸟    603555      11.8              2014-01-15 00:00:00.000 7100000                   宋子明

贵人鸟    603555      12.2              2014-01-15 00:00:00.000 7100000                   宋子明

贵人鸟    603555      12.6              2014-01-15 00:00:00.000 7100000                   宋子明

*/


你好，哥们，你的回复对我非常有帮助，实际情况还有点点小麻烦，还有几个字段也是这种情况。帮我修改下哟，我把分全给你。感谢哥们。
数据如下，我再给你截图看下

贵人鸟 603555 10.9,10.78,10.6 10.6,10.6,10.6 2014-01-15 2014-01-14 2014-01-15 23000000,23000000,24000000
贵人鸟 603555 10.7,11.,10.88 10.6,10.6,10.6 2014-01-15 2014-01-14 2014-01-15 24000000,23000000,23000000
贵人鸟 603555 10.8,11.1,10.98 10.6,10.6,10.6 2014-01-15 2014-01-14 2014-01-15 24000000,23000000,23000000
贵人鸟 603555 10.68,10.5,10.8 10.6,10.6,10.6 2014-01-15 2014-01-14 2014-01-15 23000000,24000000,23000000



麻烦哥们再帮修改下，以满足上面这个要求！
Q315054403 2014-07-23
  • 打赏
  • 举报
 回复
如果数据量小，写个游标+循环处理也行 如果数据量稍大，那就得设计处理了
發糞塗牆 2014-07-23
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-07-23 10:18:08
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[hunag]
if object_id('[hunag]') is not null drop table [hunag]
go 
create table [hunag]([VAR0] varchar(6),[VAR1] int,[VAR2] varchar(17),[VAR4] datetime,[VAR7] varchar(25),[VAR8] varchar(6))
insert [hunag]
select '贵人鸟',603555,'11.8,12.2,12.6','2014-01-15','7100000,7100000,7100000','宋子明' union all
select '贵人鸟',603555,'10.38,10.88,10.58','2014-01-15','21000000,7000000,14000000','严琳'
--------------开始查询--------------------------
SELECT DISTINCT  A.[VAR0],A.[VAR1],A.[VAR2],A.[VAR4],B.[VAR7],A.[VAR8]
FROM 
(select
    [VAR0], 
    a.[VAR1], 
    SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4],
	--SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number)[VAR7],
	[VAR8]
from
    [hunag] a,master..spt_values 
where
    number >=1 and number<=len([VAR2])
    and type='p' 
    and substring(','+[VAR2],number,1)=',')A
INNER JOIN (select
    [VAR0], 
    a.[VAR1], 
    SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7], [VAR4],
	[VAR8]
FROM
    [hunag] a,master..spt_values 
where
    number >=1 and number<=len([VAR7])
    and type='p' 
    and substring(','+[VAR7],number,1)=',')B ON A.VAR0=B.VAR0 AND A.VAR1=B.VAR1 AND A.VAR4=B.VAR4 AND A.VAR8=B.VAR8

----------------结果----------------------------
/* 
VAR0   VAR1        VAR2              VAR4                    VAR7                      VAR8
------ ----------- ----------------- ----------------------- ------------------------- ------
贵人鸟    603555      10.38             2014-01-15 00:00:00.000 14000000                  严琳
贵人鸟    603555      10.38             2014-01-15 00:00:00.000 21000000                  严琳
贵人鸟    603555      10.38             2014-01-15 00:00:00.000 7000000                   严琳
贵人鸟    603555      10.58             2014-01-15 00:00:00.000 14000000                  严琳
贵人鸟    603555      10.58             2014-01-15 00:00:00.000 21000000                  严琳
贵人鸟    603555      10.58             2014-01-15 00:00:00.000 7000000                   严琳
贵人鸟    603555      10.88             2014-01-15 00:00:00.000 14000000                  严琳
贵人鸟    603555      10.88             2014-01-15 00:00:00.000 21000000                  严琳
贵人鸟    603555      10.88             2014-01-15 00:00:00.000 7000000                   严琳
贵人鸟    603555      11.8              2014-01-15 00:00:00.000 7100000                   宋子明
贵人鸟    603555      12.2              2014-01-15 00:00:00.000 7100000                   宋子明
贵人鸟    603555      12.6              2014-01-15 00:00:00.000 7100000                   宋子明
*/
俺是大菠萝 2014-07-23
  • 打赏
  • 举报
 回复
看你的表中，主键应该是VAR0,VAR1,VAR4,VAR8，创建一个表值function，用来分割字符串，然后和原表按照主键关联join；由于VAR2和VAR7可能同时存在多值情况，因此要多做一次full join
發糞塗牆 2014-07-23
  • 打赏
  • 举报
 回复
有ID就好办一点，列出来看看
.NET快速开发框架 2014-07-23
  • 打赏
  • 举报
 回复
引用 13 楼 DBA_Huangzj 的回复:
你的表是不是还应该有个主键啊？
专家，有个ID字段是主键，或GUID，这个就是主键。
發糞塗牆 2014-07-23
  • 打赏
  • 举报
 回复
你的表是不是还应该有个主键啊？
.NET快速开发框架 2014-07-23
  • 打赏
  • 举报
 回复
不能这么理解呀。
如下图我选中的部分，我搞到库中后就要拆分成三条记录。其他情况类似，就是上面我说的那个问题

發糞塗牆 2014-07-23
  • 打赏
  • 举报
 回复
只看你最后给出的那4行数据，VAR0 VAR1 VAR2 这三列就应该有11种，var3目前只有一种，var7有2种，那么最少也是11*2=22种情况，这样理解对不？
.NET快速开发框架 2014-07-23
  • 打赏
  • 举报
 回复
引用 9 楼 DBA_Huangzj 的回复:
也就是说你这个不需要组合？VAR2,3,7这三个一定只有3行是吧？
不一定某列是三个数据，有的是两个 VAR2,3,7这三个一定只有3行在上面的实例中是3行，有的也有是两个值的情况，那么就对应的是两行，不过VAR2,3,7这三个字段对应的行数都是一样多的。
發糞塗牆 2014-07-23
  • 打赏
  • 举报
 回复
也就是说你这个不需要组合？VAR2,3,7这三个一定只有3行是吧？
.NET快速开发框架 2014-07-23
  • 打赏
  • 举报
 回复
引用 6 楼 DBA_Huangzj 的回复:
----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-07-23 11:19:32
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([VAR0] varchar(6),[VAR1] int,[VAR2] varchar(15),[VAR3] varchar(14),[VAR4] date,[VAR5] date,[VAR6] date,[VAR7] varchar(26))
insert [huang]
select '贵人鸟',603555,'10.9,10.78,10.6','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,23000000,24000000' union all
select '贵人鸟',603555,'10.7,11.,10.88','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select '贵人鸟',603555,'10.8,11.1,10.98','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select '贵人鸟',603555,'10.68,10.5,10.8','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,24000000,23000000'
--------------开始查询--------------------------

SELECT DISTINCT a.[VAR0],a.[VAR1],a.[VAR2],b.[VAR3],a.VAR4,a.[VAR5],[VAR6],c.[VAR7]
FROM (select
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4],	[VAR5],[VAR6]
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR2])
    and type='p' 
    and substring(','+[VAR2],number,1)=',')a INNER JOIN (
select
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR3],number,CHARINDEX(',',[VAR3]+',',number)-number) as [VAR3]
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR3])
    and type='p' 
    and substring(','+[VAR3],number,1)=',')b ON a.var0=b.var0 AND a.var1=b.var1
INNER JOIN (
	select
    [VAR0], 
    [VAR1], 
    SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7]
from
    huang a,master..spt_values 
where
    number >=1 and number<=len([VAR7])
    and type='p' 
    and substring(','+[VAR7],number,1)=',') c ON a.var0=c.var0 AND a.var1=c.var1

----------------结果----------------------------
/* 
VAR0   VAR1        VAR2            VAR3           VAR4       VAR5       VAR6       VAR7
------ ----------- --------------- -------------- ---------- ---------- ---------- --------------------------
贵人鸟    603555      10.5            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.5            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.6            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.6            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.68           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.68           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.7            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.7            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.78           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.78           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.8            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.8            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.88           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.88           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.9            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.9            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      10.98           10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      10.98           10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      11.             10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      11.             10.6           2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟    603555      11.1            10.6           2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟    603555      11.1            10.6           2014-01-15 2014-01-14 2014-01-15 24000000
*/
专家你好，你这个还是有个问题，你看下，应该只有12条数据才对，你这个搞出来就是20多条了。
MySQL 列以逗号分隔转成多行
MySQL 列转行、MySQL逗号拆分转多行
Mysql来帮忙：多行合并成一列
先还是来说下业务场景 现在有个业务需求，需要根据父类ID(pid)，分别找出3条最新的数据展示出来。 如果使用group by发现每个只能显示一条。 那么怎么办？ 接下来就是解决现场。 解决 解决思路 1、把符合条件的id串在一起组合成一个新的列 2、从列里面截取3个子项 整体SQL 先直接上方案-SQL： SELECT pid, SUBSTRING_INDEX( GROUP_CONCAT(id) ,',' ,-3) as ids FROM t_info..
Mysql中一行转多行字符串转多行
这两天解决了一个比较麻烦的问题，在此记录一下。 问题，select * from table where table.key in (’${权限值}’) 这个sql传入的参数是一个字符串根据情况需要拆分。 因为mysql无法直接一行拆多行，所以百度一下，下面是可行的解决方案。 SELECT substring_index(substring_index( a.rn,',',b.help_topic_id + 1 ),',' ,- 1) AS rn FROM (select '1,2,
【Hive-基础】行转列，列转行，窗口函数
行转列，列转行 文章目录行转列，列转行行转列列转行 行转列 1）函数说明(可以是一行转一列，多行转一列) CONCAT(string A, string B...)：返回输入字符串连接后的结果，支持任意个输入字符串; CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 concat()。第一个参数是其余参数，的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。 注意：CONCAT_WS must be “string o
个人永久性免费-Excel催化剂功能第73波-数据转换：单行多项目转多行单项目
数据分析的前半部分数据处理、转换等工作是一个又脏又累的活，默默地干着，却又不出彩让人看到过程的艰辛和成果。如何让这个过程可以更加轻松一点，是Excel催化剂为大家所想的，今天带来一大刚需的数据转换功能，将多个项目列转多行单项目。 业务场景 在一些系统导出的数据里，或者一些表单采集到的多选项目的数据，很常见到的是将某一列的内容，多个项目合并成一行，如下图所示。 数据...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章