如何将列分隔值转换为列为动态的不同列 [英] How to convert a column separated value into different column where column are dynamic

查看：56
本文介绍了如何将列分隔值转换为列为动态的不同列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有复杂的查询，其中有两个表，第一个有列，如此ID，caller_id，datetime，part1，part2，session



现在我必须打破part1进入不同的列，并且part2进入另一个表的不同列lime_survey_897455 part1有最多15个值，他们将保存在这样的列中

[897455X7X35]，[897455X7X39]，897455X7X43，897455X7X47， 897455X7X51，897455X7X84，897455X7X94，897455X7X104，897455X7X114，897455X7X124，897455X7X134，897455X7X144，897455X7X154，

897455X7X164，897455X7X174

类似于第2部分

897455X9X55，897455X11X59，897455X13X63，897455X15X67，897455X24X71，897455X34X184，897455X44X194，897455X54X204 ，897455X64X214，897455X74X224，

897455X84X234，897455X94X244，897455X104X254，897455X114X264，897455X124X274





I have complex query which have two table first one have column like this ID,caller_id,datetime ,part1,part2,session

now I have to break part1 into different column and also part2 into different column of another table lime_survey_897455 part1 have max 15 value and they will save in column like this
[897455X7X35] ,[897455X7X39] ,"897455X7X43" ,"897455X7X47" ,"897455X7X51" ,"897455X7X84" ,"897455X7X94" ,"897455X7X104" ,"897455X7X114" ,"897455X7X124" ,"897455X7X134" ,"897455X7X144","897455X7X154",
"897455X7X164","897455X7X174"
similarlly part2
"897455X9X55","897455X11X59" ,"897455X13X63","897455X15X67" ,"897455X24X71" ,"897455X34X184","897455X44X194","897455X54X204" ,"897455X64X214" ,"897455X74X224" ,
"897455X84X234","897455X94X244" ,"897455X104X254","897455X114X264","897455X124X274"


CREATE TABLE [dbo].[PMOSurvey]
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Caller_id] [varchar](15) NOT NULL,
	[Date_Time] [datetime] NOT NULL,
	[Part1] [varchar](32) NOT NULL,
	[Part2] [varchar](32) NOT NULL,
	[Session_Id] [varchar](max) NOT NULL,
--sic
CREATE TABLE [dbo].[lime_survey_897455]
(
	[ID] [int] IDENTITY(1000,1) NOT NULL,
	[token] [varchar](35) NULL DEFAULT (NULL),
	[submitdate] [datetime] NULL DEFAULT (NULL),
	[lastpage] [int] NULL DEFAULT (NULL),
	[startlanguage] [varchar](20) NOT NULL,
	[startdate] [datetime] NOT NULL,
	[datestamp] [datetime] NOT NULL,
	[ipaddr] [text] NULL,
	[refurl] [text] NULL,
	[897455X7X35] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X39] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X43] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X47] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X51] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X84] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X94] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X104] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X114] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X124] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X134] [varchar](1) NULL DEFAULT (NULL),
	[897455X7X144] [varchar](1) NULL,
	[897455X7X154] [varchar](1) NULL,
	[897455X7X164] [varchar](1) NULL,
	[897455X7X174] [varchar](1) NULL,
	[897455X9X55] [varchar](1) NULL,
	[897455X11X59] [varchar](1) NULL,
	[897455X13X63] [varchar](1) NULL,
	[897455X15X67] [varchar](1) NULL,
	[897455X24X71] [varchar](1) NULL,
	[897455X34X184] [varchar](1) NULL,
	[897455X44X194] [varchar](1) NULL,
	[897455X54X204] [varchar](1) NULL,
	[897455X64X214] [varchar](1) NULL,
	[897455X74X224] [varchar](1) NULL,
	[897455X84X234] [varchar](1) NULL,
	[897455X94X244] [varchar](1) NULL,
	[897455X104X254] [varchar](1) NULL,
	[897455X114X264] [varchar](1) NULL,
	[897455X124X274] [varchar](1) NULL,
-- sic







insert into [PMOSurvey] values
('7023123025', '2016-06-08 19:06:14.283', '1:1:1:1:1:1:2:2:1:2:1:1:2:1:1:', '3:3:4:4:2:2:3:5:2:3:3:4:5:3:5:', '0ac72f5e_00002244_57581d1e_42a7_0059'),
('7023225749', '2016-06-08 16:05:10.863', '2:1:1:1:1:2:2:1:2:1:2:1:2:1:1:' ,'1:2:1:2:1:2:5:2:5:2:3:4:2:4:4:', 'ac72f5e_00002244_5757f2a1_1022_005d'),
('7023225745', '2016-06-08 16:05:10.863', '2:1:1:1:1:2:2:1:2:1:2:1:2:1:1:', '1:2:1:2:1:2:5:2:', 'ac72f5e_00002244_5757f2a1_1022_005ee')

insert into [lime_survey_897455] values
('7023123025', '2016-06-08 19:06:14.283', 30, 'hi-IN', '2016-06-08 19:06:14.283', '2016-06-08 19:06:14.283', '10.10.10.10', 'aaaaaaa', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'N', 'Y', 'Y', 'N', 'Y', 'Y', '3', '3', '4', '4', '2', '2', '3', '5', '2', '3', '3', '4', '5', '3', '5'),
('7023225749', '2016-06-08 16:05:10.863', 30, '1',     '2016-06-08 16:05:10.863', '2016-06-08 16:05:10.863', '10.10.10.10', 'aaaaaaa', 'N', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'N', 'Y', 'N', 'Y', 'N', 'Y', 'Y', '1', '2', '1', '2', '1', '2', '5', '2', '5', '2', '3', '4', '2', '4', '4'),
('7023225745', '2016-06-08 16:05:10.863', 23, 'en-IN', '2016-06-08 16:05:10.863', '2016-06-08 16:05:10.863', '10.10.10.10', 'aaaaaaa', 'N', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'N', 'Y', 'N', 'Y', 'N', 'Y', 'Y', '1', '2', '1', '2', '1', '2', '5', '2', null, null, null, null, null, null, null)





我尝试过：





What I have tried:

sp_helptext INSERTData_lime_survey_897455

 alter proc INSERTData_lime_survey_897455
 as 
 begin
 truncate table lime_survey_897455
 DECLARE @cnt INT = 1;
 declare @cnt1 int;
 declare @loop1 int=1;
 declare @loop2 int=1;
 set    @cnt1 =(select    max(ID)   from PMOSurvey)
 while @cnt<=@cnt1
 begin
DECLARE @question  VARCHAR(100)
set    @question =(select    part1   from PMOSurvey where  ID=@cnt )
DECLARE @question1  VARCHAR(100)
set    @question1 =(select    part2   from PMOSurvey where  ID=@cnt) 
 insert into lime_survey_897455([897455X7X35] ,[897455X7X39] ,"897455X7X43" ,"897455X7X47" ,"897455X7X51" ,"897455X7X84" ,"897455X7X94" ,"897455X7X104" ,"897455X7X114" ,"897455X7X124" ,"897455X7X134" ,"897455X7X144","897455X7X154","897455X7X164","897455X7X174" ,"897455X9X55","897455X11X59" ,"897455X13X63","897455X15X67" ,"897455X24X71" ,"897455X34X184","897455X44X194","897455X54X204" ,"897455X64X214" ,"897455X74X224","897455X84X234","897455X94X244" ,"897455X104X254","897455X114X264","897455X124X274",token,submitdate,lastpage,startlanguage,startdate,
 datestamp,ipaddr,refurl)
 
 while @loop1<30
 begin
SELECT   case when (SUBSTRING(@question,@loop1, CHARINDEX(':', @question) - 1))=1 then 'Y' else 'N' end  
  set @loop1=@loop1+2;
end
--AS [897455X7X35] ,
   --      case when ( SUBSTRING(@question,3,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS [897455X7X39] ,
		 --case when ( SUBSTRING(@question,5,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X43" ,
		 --case when (SUBSTRING(@question,7,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X47" ,
		 --case when (SUBSTRING(@question,9,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X51" ,
		 --case when (SUBSTRING(@question,11,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X84" ,
		 --case when (SUBSTRING(@question,13,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X94", 
		 --case when ( SUBSTRING(@question,15,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X104",
		 --case when (SUBSTRING(@question,17, CHARINDEX(':', @question) - 1))=1 then 'Y' else 'N' end  AS "897455X7X114",
		 --case when (SUBSTRING(@question,19,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X124" ,
		 --case when (SUBSTRING(@question,21,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X134" ,
		 --case when (SUBSTRING(@question,23,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X144" ,
		 --case when ( SUBSTRING(@question,25,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X154" ,
		 --case when (SUBSTRING(@question,27,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X164" ,
		 --case when ( SUBSTRING(@question,29,CHARINDEX(':', @question)-1))=1 then 'Y' else 'N' end  AS "897455X7X174" ,

 while @loop2<30
 begin
select    SUBSTRING(@question1,@loop2, CHARINDEX(':', @question1) - 1)
 set @loop2=@loop2+2;
end
	 --select    SUBSTRING(@question1,1, CHARINDEX(':', @question1) - 1)  AS "897455X9X55" ,
  --       SUBSTRING(@question1,3, CHARINDEX(':', @question1) - 1)  AS "897455X11X59" ,
	 --    SUBSTRING(@question1,5, CHARINDEX(':', @question1) - 1)  AS "897455X13X63" ,
	 --    SUBSTRING(@question1,7, CHARINDEX(':', @question1) - 1)  AS "897455X15X67" ,
	 --    SUBSTRING(@question1,9, CHARINDEX(':', @question1) - 1)  AS "897455X24X71",  
		--SUBSTRING(@question1,11, CHARINDEX(':', @question1) - 1)  AS "897455X34X184" , 
		--SUBSTRING(@question1,13, CHARINDEX(':', @question1) - 1)  AS "897455X44X194" ,
		--SUBSTRING(@question1,15, CHARINDEX(':', @question1) - 1)  AS "897455X54X204" ,
		--SUBSTRING(@question1,17, CHARINDEX(':', @question1) - 1)  AS "897455X64X214" ,
		--SUBSTRING(@question1,19, CHARINDEX(':', @question1) - 1)  AS "897455X74X224" ,
		--SUBSTRING(@question1,21, CHARINDEX(':', @question1) - 1)  AS "897455X84X234" ,
	 --   SUBSTRING(@question1,23, CHARINDEX(':', @question1) - 1)  AS "897455X94X244",
		--SUBSTRING(@question1,25, CHARINDEX(':', @question1) - 1)  AS "897455X104X254" ,
	 --   SUBSTRING(@question1,27, CHARINDEX(':', @question1) - 1)  AS "897455X114X264" ,
	 --   SUBSTRING(@question1,29, CHARINDEX(':', @question1) - 1)  AS "897455X124X274",
	(select Caller_id from PMOSurvey where ID=@cnt)
	(select Date_time from  PMOSurvey where ID=@cnt )
	(select (LEN(part1)+LEN(part2))/2 from PMOSurvey where ID=@cnt)
	--(select Caller_id,Date_time, (LEN(part1)+LEN(part2))/2 from PMOSurvey where ID=@cnt),
	(ISnull(select s.Lang_Mode from Saved_Lang s  join PMOSurvey p  on s.Phone_No=p.Caller_id  where    s.App_Name='RATE_My_GOV'  and p.ID=@cnt),1)
	(select Date_time from  PMOSurvey where ID=@cnt )
	(select Date_time from  PMOSurvey where ID=@cnt ),
	'10.10.10.10' as ipaddr,
	'aaabbbb' as refurl
	--set @cnt1=@cnt1-1
	set @cnt=@cnt+1
	end
	end

推荐答案

I’m not sure i understand you well, but... seems, you want to split Part1 and Part2 colon-separated data into columns.



Well, you need to use CTE[^] to split colon-separated data into rows, then to Pivot[^] data as is is shown below:

I'm not sure i understand you well, but... seems, you want to split Part1 and Part2 colon-separated data into columns.

Well, you need to use CTE[^] to split colon-separated data into rows, then to Pivot[^] data as is is shown below:
-- replace @PMOSurvey with [dbo].[PMOSurvey]
;WITH Part1ToColumns AS
(
        --get first portion of data: first sign from Part1 column
	SELECT 1 AS MyCounter, ID, Caller_Id, LEFT(Part1, CHARINDEX(':', Part1)-1) AS P1, RIGHT(Part1, LEN(Part1) - CHARINDEX(':', Part1)) AS Remainder
	FROM @PMOSurvey 
	WHERE CHARINDEX(':', Part1)>0
	UNION ALL
	--recursive part
        --get next sign from Remainder(earlier Part1) till you find last colon
	SELECT MyCounter +1 As MyCounter, ID, Caller_Id, LEFT(Remainder, CHARINDEX(':', Remainder)-1) AS P1, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(':', Remainder)) AS Remainder
	FROM Part1ToColumns 
	WHERE CHARINDEX(':', Remainder)>0
)
SELECT ID, Caller_Id, [1] AS [897455X7X39], [2] AS [897455X7X43], [3] AS [897455X7X47], [4] AS [897455X7X51], [5] AS [897455X7X84],
		[6] AS [897455X7X94], [7] AS [897455X7X104], [8] AS [897455X7X114], [9] AS [897455X7X124], [10] AS [897455X7X134],
		[11] AS [897455X7X144], [12] AS [897455X7X154], [13] AS [897455X7X164], [14] AS [897455X7X174]
FROM (
	SELECT MyCounter, ID, Caller_Id, CASE WHEN P1 = '1' THEN 'Y' ELSE 'N' END AS P1 
	FROM Part1ToColumns 
) AS DT
PIVOT(MAX(P1) FOR MyCounter IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14])) AS PT





Result:



Result:

ID	Caller_Id	897455X7X39	897455X7X43	897455X7X47	897455X7X51	897455X7X84	897455X7X94	897455X7X104	897455X7X114	897455X7X124	897455X7X134	897455X7X144	897455X7X154	897455X7X164	897455X7X174
1	7023123025	Y	Y	Y	Y	Y	Y	N	N	Y	N	Y	Y	N	Y
3	7023225745	N	Y	Y	Y	Y	N	N	Y	N	Y	N	Y	N	Y
2	7023225749	N	Y	Y	Y	Y	N	N	Y	N	Y	N	Y	N	Y





To be able to insert above data into lime_survey_897455, you have to use this statement:



To be able to insert above data into lime_survey_897455, you have to use this statement:

;WITH Part1ToColumns AS
(
 ...
)
INSERT INTO lime_survey_897455 (<columns>)
SELECT ...





You have to do the same with Part2 column using similar CTE. Finally, you have to update data using ID and Caller_id values.





I hope this help!



For further information about CTE and PIVOT, please see:

Recursive Queries Using Common Table Expressions[^]

WITH common_table_expression (Transact-SQL)[^]

CP articles about CTE[^]

CP articles about Pivot[^]





Part2



You have to do the same with Part2 column using similar CTE. Finally, you have to update data using ID and Caller_id values.


I hope this help!

For further information about CTE and PIVOT, please see:
Recursive Queries Using Common Table Expressions[^]
WITH common_table_expression (Transact-SQL)[^]
CP articles about CTE[^]
CP articles about Pivot[^]


Part2

;WITH Part2ToColumns AS
(
	SELECT 1 AS MyCounter, ID, Caller_Id, LEFT(Part2, CHARINDEX(':', Part2)-1) AS P2, RIGHT(Part2, LEN(Part2) - CHARINDEX(':', Part2)) AS Remainder
	FROM @PMOSurvey 
	WHERE CHARINDEX(':', Part2)>0
	UNION ALL
	--recursive part
	SELECT MyCounter +1 As MyCounter, ID, Caller_Id, LEFT(Remainder, CHARINDEX(':', Remainder)-1) AS P2, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(':', Remainder)) AS Remainder
	FROM Part2ToColumns 
	WHERE CHARINDEX(':', Remainder)>0
)
SELECT ID, Caller_Id, [1] AS [897455X9X55], [2] AS [897455X11X59], [3] AS [897455X13X63], [4] AS [897455X15X67], [5] AS [897455X24X71],
		[6] AS [897455X34X184], [7] AS [897455X44X194], [8] AS [897455X54X204], [9] AS [897455X64X214], [10] AS [897455X74X224],
		[11] AS [897455X84X234], [12] AS [897455X94X244], [13] AS [897455X7X164], [14] AS [897455X104X254], [15] AS [897455X114X264], [16] AS [897455X124X274]
FROM (
	SELECT MyCounter, ID, Caller_Id, P2 
	FROM Part2ToColumns 
) AS DT
PIVOT(MAX(P2) FOR MyCounter IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16])) AS PT





Result:



Result:

ID	Caller_Id	897455X9X55	897455X11X59	897455X13X63	897455X15X67	897455X24X71	897455X34X184	897455X44X194	897455X54X204	897455X64X214	897455X74X224	897455X84X234	897455X94X244	897455X7X164	897455X104X254	897455X114X264	897455X124X274
1	7023123025	3	3	4	4	2	2	3	5	2	3	3	4	5	3	NULL	NULL
3	7023225745	1	2	1	2	1	2	5	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	7023225749	1	2	1	2	1	2	5	2	5	2	3	4	2	4	NULL	NULL


这篇关于如何将列分隔值转换为列为动态的不同列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆