如何用SQL语句将一行变成多行呢？

如何用SQL查询语句将一行变成多行呢？
如下记数据中TICKET_ID 为“PO14100001”的记录，因为有三个人员(EMP_ID分别为:000001,000002,000003)，
我想查询出来的结果是三个记录:第一条是'PO14100001','000001';第二条是'PO14100001','000002'; 第三条是 'PO14100001','000003' .


表结构如下：
create table T (TICKET_ID VARCHAR(10),
                EMP_ID    VARCHAR(50),
                PRIMARY KEY (TICKET_ID ));

INSERT INTO T(TICKET_ID,EMP_ID)
VALUES ('PO14100001','000001;000002;000003');               

INSERT INTO T(TICKET_ID,EMP_ID)
VALUES ('PO14100002','000001;000004');
                
INSERT INTO T(TICKET_ID,EMP_ID)
VALUES ('PO14100003','000003;000004');    

INSERT INTO T(TICKET_ID,EMP_ID)
VALUES ('PO14100004','000001;000002');

INSERT INTO T(TICKET_ID,EMP_ID)
VALUES ('PO14100005','000001;000002;000003');

INSERT INTO T(TICKET_ID,EMP_ID)
VALUES ('PO14100006','000001');

17 个解决方案

#1

你参考一下吧，有什么问题贴上来

SELECT
	TICKET_ID,EMP_ID
FROM 
	(SELECT TICKET_ID, CONVERT(XML , '<v>' + REPLACE(EMP_ID, ';', '</v><v>') + '</v>')EMP_IDS FROM T) T0
	OUTER APPLY (SELECT N.v.value('.' , 'VARCHAR(100)')EMP_ID FROM T0.EMP_IDS.nodes('/v') N(v)) T1

#2

CONVERT(XML 参数)  这个函数不是标准的SQL语句函数.
能否用通用一点的SQL语句呢. 因为我想在其他数据库中也能正常使用的.

#3

SELECT
	a.TICKET_ID,
	EMP_ID=substring(a.EMP_ID,b.number,charindex(';',a.EMP_ID+';',b.number)-b.number)
FROM T a
	join master..spt_values b on b.type='P'
WHERE
	charindex(';',';'+a.EMP_ID,b.number)=b.number

要不试下这个

#4

你先试下，其它数据库，是啥? 有些函数要替换,  因为有些函数无法通用

#5

还是借助了spt_values 这个微软件SQL中独有的东西啊. 还不够 标准!
能否用纯 SQL 标准语句呢?

#6

其实我公司使用的是DB2的数据库,但用DB2的人太少了,所以就借助MS SQL这个平台来提问了. 
呵呵!

#7

我想到了一个思路,用递归查询去解决. 不知能否实现.

#8

引用 7 楼 JYSVIP 的回复:

我想到了一个思路,用递归查询去解决. 不知能否实现.

可以的，但是性能比较差。其实3楼写法，DB也能实现。你自己建立一个数字表就OK 了。

#9

引用 6 楼 JYSVIP 的回复:

其实我公司使用的是DB2的数据库,但用DB2的人太少了,所以就借助MS SQL这个平台来提问了. 
呵呵!

用函数可以吗~~

#10

引用 8 楼 alimake 的回复:

Quote: 引用 7 楼 JYSVIP 的回复:

我想到了一个思路,用递归查询去解决. 不知能否实现.

可以的，但是性能比较差。其实3楼写法，DB也能实现。你自己建立一个数字表就OK 了。

好的,我试试3楼的写法,也去DB2中找一个这样的系统表.谢谢啦!

#11

引用 9 楼 ky_min 的回复:

Quote: 引用 6 楼 JYSVIP 的回复:

其实我公司使用的是DB2的数据库,但用DB2的人太少了,所以就借助MS SQL这个平台来提问了. 
呵呵!

用函数可以吗~~

我试试你3楼的写法,到DB2中也找一个这样的系统表.非常感谢你哦!

#12

select * from t
;with cte as 
(select TICKET_ID,CHARINDEX(';',emp_id+';') as station,
SUBSTRING(EMP_ID,1,CHARINDEX(';',emp_id+';')-1) as emp_id from t 
union all
 select a.ticket_id,
 CHARINDEX(';',a.emp_id+';',b.station+1) 
 ,SUBSTRING(a.EMP_ID,b.station+1,CHARINDEX(';',a.emp_id+';',b.station+1)-b.station-1) from t
as a join cte as b  on a.TICKET_ID=b.ticket_id
where CHARINDEX(';',a.emp_id+';',b.station+1)>0)
select * from cte
--结果
TICKET_ID  EMP_ID
---------- --------------------------------------------------
PO14100001 000001;000002;000003
PO14100002 000001;000004
PO14100003 000003;000004
PO14100004 000001;000002
PO14100005 000001;000002;000003
PO14100006 000001

(6 行受影响)

TICKET_ID  station     emp_id
---------- ----------- --------------------------------------------------
PO14100001 7           000001
PO14100002 7           000001
PO14100003 7           000003
PO14100004 7           000001
PO14100005 7           000001
PO14100006 7           000001
PO14100005 14          000002
PO14100005 21          000003
PO14100004 14          000002
PO14100003 14          000004
PO14100002 14          000004
PO14100001 14          000002
PO14100001 21          000003

(13 行受影响)

写了个 你参考下。

#13

引用 12 楼 alimake 的回复:

select * from t
;with cte as 
(select TICKET_ID,CHARINDEX(';',emp_id+';') as station,
SUBSTRING(EMP_ID,1,CHARINDEX(';',emp_id+';')-1) as emp_id from t 
union all
 select a.ticket_id,
 CHARINDEX(';',a.emp_id+';',b.station+1) 
 ,SUBSTRING(a.EMP_ID,b.station+1,CHARINDEX(';',a.emp_id+';',b.station+1)-b.station-1) from t
as a join cte as b  on a.TICKET_ID=b.ticket_id
where CHARINDEX(';',a.emp_id+';',b.station+1)>0)
select * from cte
--结果
TICKET_ID  EMP_ID
---------- --------------------------------------------------
PO14100001 000001;000002;000003
PO14100002 000001;000004
PO14100003 000003;000004
PO14100004 000001;000002
PO14100005 000001;000002;000003
PO14100006 000001

(6 行受影响)

TICKET_ID  station     emp_id
---------- ----------- --------------------------------------------------
PO14100001 7           000001
PO14100002 7           000001
PO14100003 7           000003
PO14100004 7           000001
PO14100005 7           000001
PO14100006 7           000001
PO14100005 14          000002
PO14100005 21          000003
PO14100004 14          000002
PO14100003 14          000004
PO14100002 14          000004
PO14100001 14          000002
PO14100001 21          000003

(13 行受影响)

写了个 你参考下。

我搞错了不好意思。原来是我的CHARINDEX 效率高。我把我们2个代码对比了下。大概是1:9效率我的效率是9倍 竟然。

#14

引用 9 楼 ky_min 的回复:

Quote: 引用 6 楼 JYSVIP 的回复:

其实我公司使用的是DB2的数据库,但用DB2的人太少了,所以就借助MS SQL这个平台来提问了. 
呵呵!

用函数可以吗~~

你测试下我们2中写法那个好，我测试了下。我的好，是8%：92% 递归应该很慢的啊 无语了

#15

这个数据量看不出区别，我这边毫秒级都是0
递归效率慢，是体现在多次递归的前提下~~
而我们这个数据只需要两三次，所以效率上不会有太大的差别

你那边测试各要多久~~

#16

引用 15 楼 ky_min 的回复:

这个数据量看不出区别，我这边毫秒级都是0
递归效率慢，是体现在多次递归的前提下~~
而我们这个数据只需要两三次，所以效率上不会有太大的差别

你那边测试各要多久~~

我看的执行计划占比

#17

引用 15 楼 ky_min 的回复:

这个数据量看不出区别，我这边毫秒级都是0
递归效率慢，是体现在多次递归的前提下~~
而我们这个数据只需要两三次，所以效率上不会有太大的差别

你那边测试各要多久~~

哥们加个QQ 775163923