--测试数据
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab VALUES ( '"状态" "1000107917101001";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"押出胶水";120;0;"延迟" "1000107917101002";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"复押胶水";240;0;"延迟" "1000004317101101";"贵州省化工研究院";17/10/11;"15:00";?;"";"植绒胶水";240;0;"延迟";3000;0;"计划"')
--测试数据结束
IF OBJECT_ID('tempdb..#t') Is NOT NULL
DROP TABLE #t
SELECT b.id AS title,IDENTITY(INT,1,1) AS rn into #t FROM #tab a
OUTER APPLY dbo.ufn_SplitStringToTable(a.id,' ') b
IF OBJECT_ID('tempdb..#t2') Is NOT NULL
DROP TABLE #t2
;WITH cte AS (
SELECT title,b.id, rn,ROW_NUMBER() OVER( partition by rn ORDER BY title) AS num FROM #t a
OUTER APPLY dbo.ufn_SplitStringToTable(replace(a.title,'"',''),';') b
WHERE rn<>1
)
SELECT rn,num,id into #t2 FROM cte WHERE num BETWEEN 1 AND 10
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+cast(num AS VARCHAR(20))+']' from #t2 for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] from #t2 pivot(max(id)for num in('+@name+'))a'
PRINT @sql
EXEC( @sql)
#2
CREATE FUNCTION [dbo].[ufn_SplitStringToTable]
(
@str VARCHAR(MAX) ,
@split VARCHAR(10)
)
RETURNS TABLE
AS
RETURN
( SELECT B.id
FROM ( SELECT [value] = CONVERT(XML , '<v>' + REPLACE(@str , @split , '</v><v>')
+ '</v>')
) A
OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')
FROM A.[value].nodes('/v') N ( v )
) B
)
with cte1
as
(SELECT REPLACE(REPLACE(REPLACE(content,'"状态" ',''),' ',';'),'"','') as content_new FROM 中间表),
cte2
as
(select A.*,b.number,
SUBSTRING(content_new,number,CHARINDEX(';',content_new+';',number)-number) as single_item,
((ROW_NUMBER() over (order by number)-1)/10) as group_seq
from cte1 A
join master..spt_values B on CHARINDEX(';',';'+content_new,number)=number
where type='P'),
cte3
as
(select *,COUNT(1) over (partition by group_seq) as count_by_item,
ROW_NUMBER() over (PARTITION by group_seq order by number) as single_seq from cte2)
select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
from (select group_seq,single_item,count_by_item,single_seq from cte3) as A
pivot (max(single_item) for single_seq in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) as B
where count_by_item=10 --如果导入的数据最终拆分的字段数目始终是10的整数倍,或者一条记录不满10个字段也进行插入,那可以忽略这一条件.
#4
[code=sql]
--测试数据
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab VALUES ( '"状态" "1000107917101001";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"押出胶水";120;0;"延迟" "1000107917101002";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"复押胶水";240;0;"延迟" "1000004317101101";"贵州省化工研究院";17/10/11;"15:00";?;"";"植绒胶水";240;0;"延迟";3000;0;"计划"')
--测试数据结束
---------------------------------------------------------------这个地方是 select zt from [表] 如何体现?
#5
直接
INSERT INTO #tab
select zt from [表]
然后执行上面的代码就ok了,等于把你现在的表里zt的内容插入临时表#tab中了
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab select zt from kbtale
这样写出来的数据不对的啊。是不是哪里有点小问题。
#9
你把你select zt from kbtale的数据贴一部分出来看看
#10
#11
我说你把原始数据贴出来,就是你select zt from kbtale的结果复制粘贴出来看看,不是叫你上面代码的结果贴出来,这个结果你上面不是说了有问题了啊
--测试数据
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtale
--测试数据结束
IF OBJECT_ID('tempdb..#t') Is NOT NULL
DROP TABLE #t
SELECT b.id AS title,IDENTITY(INT,1,1) AS rn into #t
FROM #tab a
OUTER APPLY dbo.ufn_SplitStringToTable(a.id,'" "') b
--SELECT * FROM #t
IF OBJECT_ID('tempdb..#t2') Is NOT NULL
DROP TABLE #t2
;WITH cte AS (
SELECT title,b.id, rn,ROW_NUMBER() OVER( partition by rn ORDER BY title) AS num FROM #t a
OUTER APPLY dbo.ufn_SplitStringToTable(replace(a.title,'"',''),';') b
WHERE title<>'"状态"'
)
SELECT rn,num,id into #t2 FROM cte WHERE num BETWEEN 1 AND 10
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+cast(num AS VARCHAR(20))+']' from #t2 for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] from #t2 pivot(max(id)for num in('+@name+'))a'
PRINT @sql
EXEC( @sql)
这样可以,你直接复制代码过去执行一下看,可以的话记得结帖
#14
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
#15
下面的代码改过了,你没有发现吗?上面的代码我贴出来的代码你执行了没啊?
#16
执行了,只能查询出来一行
#17
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
IF OBJECT_ID('tempdb..#t') Is NOT NULL
DROP TABLE #t
SELECT b.id AS title,IDENTITY(INT,1,1) AS rn into #t
FROM #tab a
OUTER APPLY dbo.ufn_SplitStringToTable(a.id,'" "') b
--SELECT * FROM #t
IF OBJECT_ID('tempdb..#t2') Is NOT NULL
DROP TABLE #t2
;WITH cte AS (
SELECT title,b.id, rn,ROW_NUMBER() OVER( partition by rn ORDER BY title) AS num FROM #t a
OUTER APPLY dbo.ufn_SplitStringToTable(replace(a.title,'"',''),';') b
WHERE rn<>1
)
SELECT rn,num,id into #t2 FROM cte WHERE num BETWEEN 1 AND 10
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+cast(num AS VARCHAR(20))+']' from #t2 for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] from #t2 pivot(max(id)for num in('+@name+'))a'
PRINT @sql
EXEC( @sql)
上面insert into #tab的数据就是你复制粘贴出来的select zt from kbtable的原始数据,你看看能运行出来不,反正我这是可以出来哈,一共58行记录。
#18
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
IF OBJECT_ID('tempdb..#t') Is NOT NULL
DROP TABLE #t
SELECT b.id AS title,IDENTITY(INT,1,1) AS rn into #t
FROM kbtable a
OUTER APPLY dbo.ufn_SplitStringToTable(a.zt,'" "') b
IF OBJECT_ID('tempdb..#t2') Is NOT NULL
DROP TABLE #t2
;WITH cte AS (
SELECT title,b.id, rn,ROW_NUMBER() OVER( partition by rn ORDER BY title) AS num FROM #t a
OUTER APPLY dbo.ufn_SplitStringToTable(replace(a.title,'"',''),';') b
WHERE rn<>1
)
SELECT rn,num,id into #t2 FROM cte WHERE num BETWEEN 1 AND 10
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+cast(num AS VARCHAR(20))+']' from #t2 for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] from #t2 pivot(max(id)for num in('+@name+'))a'
PRINT @sql
EXEC( @sql)
--测试数据
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab VALUES ( '"状态" "1000107917101001";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"押出胶水";120;0;"延迟" "1000107917101002";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"复押胶水";240;0;"延迟" "1000004317101101";"贵州省化工研究院";17/10/11;"15:00";?;"";"植绒胶水";240;0;"延迟";3000;0;"计划"')
--测试数据结束
IF OBJECT_ID('tempdb..#t') Is NOT NULL
DROP TABLE #t
SELECT b.id AS title,IDENTITY(INT,1,1) AS rn into #t FROM #tab a
OUTER APPLY dbo.ufn_SplitStringToTable(a.id,' ') b
IF OBJECT_ID('tempdb..#t2') Is NOT NULL
DROP TABLE #t2
;WITH cte AS (
SELECT title,b.id, rn,ROW_NUMBER() OVER( partition by rn ORDER BY title) AS num FROM #t a
OUTER APPLY dbo.ufn_SplitStringToTable(replace(a.title,'"',''),';') b
WHERE rn<>1
)
SELECT rn,num,id into #t2 FROM cte WHERE num BETWEEN 1 AND 10
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+cast(num AS VARCHAR(20))+']' from #t2 for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] from #t2 pivot(max(id)for num in('+@name+'))a'
PRINT @sql
EXEC( @sql)
#2
CREATE FUNCTION [dbo].[ufn_SplitStringToTable]
(
@str VARCHAR(MAX) ,
@split VARCHAR(10)
)
RETURNS TABLE
AS
RETURN
( SELECT B.id
FROM ( SELECT [value] = CONVERT(XML , '<v>' + REPLACE(@str , @split , '</v><v>')
+ '</v>')
) A
OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')
FROM A.[value].nodes('/v') N ( v )
) B
)
with cte1
as
(SELECT REPLACE(REPLACE(REPLACE(content,'"状态" ',''),' ',';'),'"','') as content_new FROM 中间表),
cte2
as
(select A.*,b.number,
SUBSTRING(content_new,number,CHARINDEX(';',content_new+';',number)-number) as single_item,
((ROW_NUMBER() over (order by number)-1)/10) as group_seq
from cte1 A
join master..spt_values B on CHARINDEX(';',';'+content_new,number)=number
where type='P'),
cte3
as
(select *,COUNT(1) over (partition by group_seq) as count_by_item,
ROW_NUMBER() over (PARTITION by group_seq order by number) as single_seq from cte2)
select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
from (select group_seq,single_item,count_by_item,single_seq from cte3) as A
pivot (max(single_item) for single_seq in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) as B
where count_by_item=10 --如果导入的数据最终拆分的字段数目始终是10的整数倍,或者一条记录不满10个字段也进行插入,那可以忽略这一条件.
#4
[code=sql]
--测试数据
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab VALUES ( '"状态" "1000107917101001";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"押出胶水";120;0;"延迟" "1000107917101002";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"复押胶水";240;0;"延迟" "1000004317101101";"贵州省化工研究院";17/10/11;"15:00";?;"";"植绒胶水";240;0;"延迟";3000;0;"计划"')
--测试数据结束
---------------------------------------------------------------这个地方是 select zt from [表] 如何体现?
#5
[code=sql]
--测试数据
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab VALUES ( '"状态" "1000107917101001";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"押出胶水";120;0;"延迟" "1000107917101002";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"复押胶水";240;0;"延迟" "1000004317101101";"贵州省化工研究院";17/10/11;"15:00";?;"";"植绒胶水";240;0;"延迟";3000;0;"计划"')
--测试数据结束
---------------------------------------------------------------这个地方是 select zt from [表] 如何体现?
直接
INSERT INTO #tab
select zt from [表]
然后执行上面的代码就ok了,等于把你现在的表里zt的内容插入临时表#tab中了
[code=sql]
--测试数据
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab VALUES ( '"状态" "1000107917101001";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"押出胶水";120;0;"延迟" "1000107917101002";"广州泉铭贸易有限公司";17/10/10;"15:00";?;"";"复押胶水";240;0;"延迟" "1000004317101101";"贵州省化工研究院";17/10/11;"15:00";?;"";"植绒胶水";240;0;"延迟";3000;0;"计划"')
--测试数据结束
---------------------------------------------------------------这个地方是 select zt from [表] 如何体现?
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab select zt from kbtale
这样写出来的数据不对的啊。是不是哪里有点小问题。
#9
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab select zt from kbtale
这样写出来的数据不对的啊。是不是哪里有点小问题。
你把你select zt from kbtale的数据贴一部分出来看看
#10
#11
我说你把原始数据贴出来,就是你select zt from kbtale的结果复制粘贴出来看看,不是叫你上面代码的结果贴出来,这个结果你上面不是说了有问题了啊
--测试数据
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtale
--测试数据结束
IF OBJECT_ID('tempdb..#t') Is NOT NULL
DROP TABLE #t
SELECT b.id AS title,IDENTITY(INT,1,1) AS rn into #t
FROM #tab a
OUTER APPLY dbo.ufn_SplitStringToTable(a.id,'" "') b
--SELECT * FROM #t
IF OBJECT_ID('tempdb..#t2') Is NOT NULL
DROP TABLE #t2
;WITH cte AS (
SELECT title,b.id, rn,ROW_NUMBER() OVER( partition by rn ORDER BY title) AS num FROM #t a
OUTER APPLY dbo.ufn_SplitStringToTable(replace(a.title,'"',''),';') b
WHERE title<>'"状态"'
)
SELECT rn,num,id into #t2 FROM cte WHERE num BETWEEN 1 AND 10
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+cast(num AS VARCHAR(20))+']' from #t2 for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] from #t2 pivot(max(id)for num in('+@name+'))a'
PRINT @sql
EXEC( @sql)
这样可以,你直接复制代码过去执行一下看,可以的话记得结帖
#14
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
#15
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
下面的代码改过了,你没有发现吗?上面的代码我贴出来的代码你执行了没啊?
#16
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
下面的代码改过了,你没有发现吗?上面的代码我贴出来的代码你执行了没啊?
执行了,只能查询出来一行
#17
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
IF OBJECT_ID('tempdb..#t') Is NOT NULL
DROP TABLE #t
SELECT b.id AS title,IDENTITY(INT,1,1) AS rn into #t
FROM #tab a
OUTER APPLY dbo.ufn_SplitStringToTable(a.id,'" "') b
--SELECT * FROM #t
IF OBJECT_ID('tempdb..#t2') Is NOT NULL
DROP TABLE #t2
;WITH cte AS (
SELECT title,b.id, rn,ROW_NUMBER() OVER( partition by rn ORDER BY title) AS num FROM #t a
OUTER APPLY dbo.ufn_SplitStringToTable(replace(a.title,'"',''),';') b
WHERE rn<>1
)
SELECT rn,num,id into #t2 FROM cte WHERE num BETWEEN 1 AND 10
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+cast(num AS VARCHAR(20))+']' from #t2 for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] from #t2 pivot(max(id)for num in('+@name+'))a'
PRINT @sql
EXEC( @sql)
上面insert into #tab的数据就是你复制粘贴出来的select zt from kbtable的原始数据,你看看能运行出来不,反正我这是可以出来哈,一共58行记录。
#18
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
帅哥,现在的问题就是
IF OBJECT_ID('tempdb..#tab') Is NOT NULL
DROP TABLE #tab
CREATE TABLE #tab
(
id nVARCHAR(max)
)
INSERT INTO #tab
select zt from kbtable
这样查询出来的不行的啊
就是会出现我上面说的问题。
IF OBJECT_ID('tempdb..#t') Is NOT NULL
DROP TABLE #t
SELECT b.id AS title,IDENTITY(INT,1,1) AS rn into #t
FROM kbtable a
OUTER APPLY dbo.ufn_SplitStringToTable(a.zt,'" "') b
IF OBJECT_ID('tempdb..#t2') Is NOT NULL
DROP TABLE #t2
;WITH cte AS (
SELECT title,b.id, rn,ROW_NUMBER() OVER( partition by rn ORDER BY title) AS num FROM #t a
OUTER APPLY dbo.ufn_SplitStringToTable(replace(a.title,'"',''),';') b
WHERE rn<>1
)
SELECT rn,num,id into #t2 FROM cte WHERE num BETWEEN 1 AND 10
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+cast(num AS VARCHAR(20))+']' from #t2 for xml PATH('')),1,1,'')
PRINT @name
set @sql ='SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] from #t2 pivot(max(id)for num in('+@name+'))a'
PRINT @sql
EXEC( @sql)