建表语句如下CREATE TABLE [dbo].tableStu(
[Id] [int] IDENTITY(1,1) NOT NULL,
[tks_handle_people_userId] [nvarchar](4000),
[tks_handle_people_username] [nvarchar](4000))
INSERT INTO tableStu VALUES('22,35','章章,张合')怎么查询得到如下结果啊?
22 章章
35 张合
[Id] [int] IDENTITY(1,1) NOT NULL,
[tks_handle_people_userId] [nvarchar](4000),
[tks_handle_people_username] [nvarchar](4000))
INSERT INTO tableStu VALUES('22,35','章章,张合')怎么查询得到如下结果啊?
22 章章
35 张合
INSERT INTO tableStu VALUES('22','章章')
INSERT INTO tableStu VALUES('35','张合')
两条记录为什么整成一条记录
CREATE TABLE [dbo].tableStu(
[Id] [int] IDENTITY(1,1) NOT NULL,
[tks_handle_people_userId] [nvarchar](4000),
[tks_handle_people_username] [nvarchar](4000))
INSERT INTO tableStu VALUES('22,35,44','章章,张合,各个')--字符串处理函数
USE [u_ydzfnj_20121026]
GO
/****** Object: UserDefinedFunction [dbo].[f_split] Script Date: 11/07/2012 22:00:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[f_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
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
select c.id,c.col1 as col1,d.col1 as col2 from (
select id,b.col as col1,row_number() over(order by id) as newid from
tableStu a
cross apply (
select *
from [dbo].[f_split](a.[tks_handle_people_userId],','))
b)c
left join
(
select id,b.col as col1,row_number() over(order by id) as newid from
tableStu a
cross apply (
select *
from [dbo].[f_split](a.[tks_handle_people_username],','))b)d
on c.newid = d.newid
USE [u_ydzfnj_20121026]去掉
CREATE TABLE #tableStu(
[Id] [int] IDENTITY(1,1) NOT NULL,
[userId] [nvarchar](4000),
[username] [nvarchar](4000))
INSERT INTO #tableStu VALUES(N'22,35',N'章章,张合');With t as
(
select userId,username
,substring(userId,1,charindex(',',userId)-1) userId2
,stuff(userid,1,charindex(',',userId),'')+',' useridsplit
,substring(username,1,charindex(',',username)-1) username2
,stuff(username,1,charindex(',',username),'')+',' usernamesplit
from #tableStu
union all
select userId2 as userId
,username2 as username
,substring(useridsplit,1,charindex(',',useridsplit)-1) userId2
,stuff(useridsplit,1,charindex(',',useridsplit),'') useridsplit
,substring(usernamesplit,1,charindex(',',usernamesplit)-1) username2
,stuff(usernamesplit,1,charindex(',',usernamesplit),'') usernamesplit
from t
where charindex(',',useridsplit)>0
)
select userId2 as userid
,username2 as username
from t