怎么用SQL语句将一行变成多行呢
如何用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');
------解决思路----------------------
你参考一下吧,有什么问题贴上来
------解决思路----------------------
写了个 你参考下。
如何用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');
------解决思路----------------------
你参考一下吧,有什么问题贴上来
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
------解决思路----------------------
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 行受影响)
写了个 你参考下。