sql 一行数据改成多行

Question

数据库
id title pictures
1 a 1.jpg|2.jpg|3.jpg
2 b 4.jpg|5.jpg
3 c 6.jpg

我要的记录集效果
id title pictures
1 a 1.jpg
1 a 2.jpg
1 a 3.jpg
2 b 4.jpg
2 b 5.jpg
3 c 6.jpg

求sql语句...求救
谢谢大家的帮助，问题已经解决
因为本人的picture的URL用的是时间格式，长度全部相同，呵呵，这回运气
view1
SELECT *, SUBSTRING(Picture, 32, 30) AS PICTUREV FROM dbo.products
WHERE (SUBSTRING(Picture, 32, 30) <> '')
view2
SELECT *, SUBSTRING(Picture, 63, 30) AS PICTUREV FROM dbo.products
WHERE (SUBSTRING(PictureB, 63, 30) <> '')
view3
SELECT *, SUBSTRING(Picture, 94, 30) AS PICTUREV FROM dbo.products
WHERE (SUBSTRING(PictureB, 94, 30) <> '')
...

一口气建了N个VIEW，觉得用用差不多了

再建一个最顶的VIEW
SELECT TOP 100 PERCENT *
FROM (SELECT *
FROM view1
UNION ALL
SELECT *
FROM view2
UNION ALL
SELECT *
FROM view3
...) DERIVEDTBL
ORDER BY ...

用起来感觉速度还可以
在VIEW里面还可以设置WHERE 展开=true,来控制单个ID是否展开多张图片
在这里谢谢各位的热心，才发布一天有就这么多人帮助。感动。。。

问题暂时保留，看有没有朋友提出更好的办法。。呵呵

Answer 1

您的方法（不建视图）：
SELECT TOP 100 PERCENT *
FROM (SELECT *, SUBSTRING(Picture, 32, 30) AS PICTUREV FROM dbo.products
WHERE (SUBSTRING(Picture, 32, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 63, 30) AS PICTUREV FROM dbo.products
WHERE (SUBSTRING(PictureB, 63, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 94, 30) AS PICTUREV FROM dbo.products
WHERE (SUBSTRING(PictureB, 94, 30) <> '')
) DERIVEDTBL
ORDER BY ...

我觉得您的子串应该是这样计算的,：
SUBSTRING(Picture, 0*31+1,30)
SUBSTRING(Picture, 1*31+1,30)
SUBSTRING(Picture, 2*31+1,30)
SUBSTRING(Picture, 3*31+1,30)
所以，在语句中这样写也会更好写，更容易理解，和更不容易出错。也不影响效率。

你的方法除了多余建了那么多视图，应该说是不错的方法了，但是实现不了不等长文件名按分隔符分离字串。

如果是分隔符为字串分段，您就必须要用我的自定义函数加我的语句。
如果是您写的那样，是固定长度的字串分段，那么就完全用你的方法，便是稍作改动，不用建中间视图
另外，您喜欢视图的话，可以建一个视图：
create view DERIVEDTBL
as
create view
SELECT *, SUBSTRING(Picture, 0*31+1,30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(Picture, 32, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 1*31+1,30, 30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(PictureB, 63, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 2*31+1,30, 30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(PictureB, 94, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 3*31+1,30, 30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(PictureB, 94, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 4*31+1,30, 30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(PictureB, 94, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 5*31+1,30, 30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(PictureB, 94, 30) <> '')
--....向下任意延伸，只要改SUBSTRING中的序号就行了。
go

调用时这样就行了：
SELECT TOP N *
FROM DERIVEDTBL
ORDER BY ...

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
一句解决问题，这样：
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,1,'|') AS PICTURE,1 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,2,'|') AS PICTURE,2 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,3,'|') AS PICTURE,3 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,4,'|') AS PICTURE,4 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,5,'|') AS PICTURE,5 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,6,'|') AS PICTURE,6 AS SN from TABLENAME
-- . . . . . .
ORDER BY ID,TITLE,SN

还有，您的这个函数求等长字串，不知道为什么没有首字串？
SUBSTRING(Picture, 32, 30)
就是这个个？
SUBSTRING(Picture, 1, 30)

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
这里只给您写了6行,来满足您每ID最多6张图片的要求。写多少行都可以，即每ID最大图片数为6张。您可以写100行，1000行都行！

这里还给您多加了以个序号字段（SN），以保证图片顺序与原排列相同。如果不需要您可以去掉他。

下面的函数是一个非常有用的自定义数，是用来提取字串中某一部份的，请您执行一下，把他建立到您的数据库中，今后对您的编程会提供很大的方便。当然，本例所用的函数就是这个函数。

create FUNCTION mysplit--将以某分隔符分段的字串,按指定的顺序号提取子串
(@str nvarchar(2000),--源字串
@sn int, --提取序号
@Deli varchar(1) --分隔符
)
RETURNS varchar(100)
AS
BEGIN
declare @first int,@last int,@result varchar(1000),@sn0 int
select @sn0=0,@first=0,@LAST=1,@str=@str+REPLICATE(@DELI,1)
while @sn0!=@sn
begin
select @sn0=@sn0+1,@first=@LAST,@last=charindex(@DELI,@str,@LAST)+1
end
if @last-@first-1<0
set @result=''
else
SET @RESULT=SUBSTRING(@str,@FIRST,@LAST-@FIRST-1)
RETURN ( @RESULT )
END
GO

Answer 2

有难度，以前见过把多行分组后，放一列的。
你现在要把它的一行变多列。
看这里有高手没！！做个记录先。

Answer 3

--假定表名为Table1
declare @Num INT
SELECT @Num = 1
WHILE @Num > 0
BEGIN
INSERT Table1
(ID,Title,Picture)
SELECT ID,Title,SubString(Picture, 0, CharIndex(Picture, '|') - 1) FROM Table1 WHERE Picture LIKE '%|%'
UPDATE Table1
SET Picture = SubString(Picture, CharIndex(Picture, '|') + 1, Len(Picture) - CharIndex(Picture, '|')-1) WHERE Picture LIKE '%|%'
SELECT @Num = @@ROWCOUNT
END

Answer 4

/*=====================================================
* 2008-11-5
* charlif
* 以下脚本全部执行便可以看到效果
========================================================*/

--创建测试表
Declare @T Table(id int,title char(10),pictures varchar(100))
--加入测试数据
insert into @T
select 1,'a','1.jpg|2.jpg|3.jpg'
union all
select 2,'b','4.jpg|5.jpg'
union all
select 3,'c','6.jpg'

--以下是代码
select a.id,a.title,b.pic1 as pic from @T a
right join (
select id,P1 as pic1,pic2 = case when charindex('|',P2)>0 then substring(P2,1,charindex('|',P2)-1) else P2 end,
pic3 = case when charindex('|',P2)>0 then substring(P2,charindex('|',P2)+1,len(P2)) else null end
From (

select id,substring(pictures,1,charindex('|',pictures)-1) as P1,
substring(pictures,charindex('|',pictures)+1,len(pictures)) as P2
from @T where charindex('|',pictures) >0
union all
select id,pictures as P1,null from @T where charindex('|',pictures) = 0
) Tabel
) b
on a.id = b.id
where b.pic1 is not null
union all
select a.id,a.title,b.pic2 as pic from @T a
right join (
select id,P1 as pic1,pic2 = case when charindex('|',P2)>0 then substring(P2,1,charindex('|',P2)-1) else P2 end,
pic3 = case when charindex('|',P2)>0 then substring(P2,charindex('|',P2)+1,len(P2)) else null end
From (

select id,substring(pictures,1,charindex('|',pictures)-1) as P1,
substring(pictures,charindex('|',pictures)+1,len(pictures)) as P2
from @T where charindex('|',pictures) >0
union all
select id,pictures as P1,null from @T where charindex('|',pictures) = 0
) Tabel
) b
on a.id = b.id
where b.pic2 is not null
union all
select a.id,a.title,b.pic3 as pic from @T a
right join (
select id,P1 as pic1,pic2 = case when charindex('|',P2)>0 then substring(P2,1,charindex('|',P2)-1) else P2 end,
pic3 = case when charindex('|',P2)>0 then substring(P2,charindex('|',P2)+1,len(P2)) else null end
From (

select id,substring(pictures,1,charindex('|',pictures)-1) as P1,
substring(pictures,charindex('|',pictures)+1,len(pictures)) as P2
from @T where charindex('|',pictures) >0
union all
select id,pictures as P1,null from @T where charindex('|',pictures) = 0
) Tabel
) b
on a.id = b.id
where b.pic3 is not null
order by title,pic

/*结果
id title pic
1 a 1.jpg
1 a 2.jpg
1 a 3.jpg
2 b 4.jpg
2 b 5.jpg
3 c 6.jpg
*/

Answer 5

呵呵，感觉有点将问题复杂化了 ，
这个问题放在实际的程序语言中执行会更加方便些，
用SQL将数据取出来，得到数据后，对数据的pictures 字段进行判断，以.jpg为分隔标准，在具体的程序语言中实现起来比较容易，^_^
然后对分隔后的进行数据的重组，
最后将重组后的数据直接显示出来，或者是更新到数据库中去，
呵呵，上面是思路，希望能有帮助，^_^

Answer 6

真搞不懂上面各位是不是吃饱撑着！！！
这么间单的问题用得着写那么复杂么，汗～～～

declare @Result varchar(4000)
declare @Teststr nvarchar(4000)
declare @Tmp table
(
a1 nvarchar(4000)
)

insert into @Tmp
select 'select '+''''+cast(id as varchar(10))+''''+' as id,'+''''+title+''''+' as title,'''+replace(pictures,'|',''' as pictures union all select '+''''+cast(id as varchar(10))+''''+' as id,'+''''+title+''''+' as title,''')+''' as pictures'
from [表名]

select @Teststr=''
select @Teststr=@Teststr + a1 + ' Union all ' from @Tmp
select @Teststr=substring(@Teststr,1,len(@Teststr)-10)
exec(@Teststr)

测试通过，返回的结果：
id title pictures
1 a 1.jpg
1 a 2.jpg
1 a 3.jpg
2 b 4.jpg
2 b 5.jpg
3 c 6.jpg