sql server字符串转表列:
分割字符串函数: CREATE FUNCTION F_StrListToTable ( @StrList NVARCHAR(4000), @PosStr NVARCHAR(1) ) RETURNS @ListTable table(ListID int) AS BEGIN DECLARE @strListTemp NVARCHAR(4000), @lsListID NVARCHAR(200), @Pos INT SET @strListTemp = @StrList WHILE @strListTemp <> N'' BEGIN SET @Pos = CHARINDEX(@PosStr,@strListTemp) IF @Pos > 0 BEGIN SET @lsListID = SUBSTRING(@strListTemp,1,@Pos -1) SET @strListTemp = SUBSTRING(@strListTemp,@Pos + 1,LEN(@strListTemp)-@Pos) END ELSE BEGIN SET @lsListID = @strListTemp SET @strListTemp = N'' END IF @lsListID <> N'' BEGIN INSERT INTO @ListTable VALUES(CONVERT(INT,@lsListID)) END END RETURN END
调用分割字符串:
declare str vchar(100)
set @str='1,2,3'
select * from F_strlisttotable(@str,',')
sql server
表列转字符串:
从一个表读取数据列表,之后用什么方法可以把那个列表变成一个以,逗号分隔的字符串(数组)
DECLARE @arryStr VARCHAR(1000)='';
SELECT @arryStr+=CONVERT(VARCHAR(3),id)+',' FROM table
SELECT @arryStr;
mysql 表列转字符串:
1、列数据转行,使用GROUP_CONCAT将列转换为用逗号分隔的字符串
SELECT TYPE,GROUP_CONCAT(NAME),GROUP_CONCAT(VALUE) FROM BaseInfo WHERE TYPE=1 GROUP BY TYPE;
2.分隔符自定义,默认是以“,”作为分隔符,若要改为“|||”,则使用SEPARATOR来指定,例如:
SELECT TYPE,GROUP_CONCAT(NAME SEPARATOR '|||'),GROUP_CONCAT(CAST(VALUE AS CHAR) SEPARATOR '|||') FROM BaseInfo WHERE TYPE=1 GROUP BY TYPE;
3.排序,还可以对这个组的值来进行排序再连接成字符串,例如按id降序来排:
SELECT TYPE,GROUP_CONCAT(NAME ORDER BY NAME DESC SEPARATOR '|||'),GROUP_CONCAT(CONVERT(VALUE,CHAR) ORDER BY VALUE DESC) FROM BaseInfo WHERE TYPE=1 GROUP BY TYPE;
mysql 字符串转表列:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[zoe_split]
(
@c VARCHAR(8000) ,
@split VARCHAR(2)
)
RETURNS @t TABLE ( col VARCHAR(MAX) )
AS
BEGIN
WHILE ( CHARINDEX(@split, @c) <> 0 )
BEGIN
INSERT @t
( col
)
VALUES ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1)
)
SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '')
END
INSERT @t
( col )
VALUES ( @c )
RETURN
END
GO
select col from [dbo].[qsf_split]('one/two/three','/')
oracle表列转字符串:
concat函数只能有两个参数,如有两个以上的参数需要循环调用
select concat(materialid,concat(materialname,materialunitid)) materialname from k_ims_material;
oracle字符串转表列:
使用decode 函数或者case when 函数