Click here to Skip to main content
15,922,696 members
Search within:


How to convert a column separated value into different column where column are dynamic
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
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"

[EDIT - OP has supplied table schema]
SQL
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

[EDIT 2 - OP has supplied some sample data]
C#
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:

SQL
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
Posted
Updated 21-Jun-16 20:50pm
v4
Add a Solution
Comments
kumari567 21-Jun-16 3:09am    
thank you for edit
kumari567 21-Jun-16 3:14am    
part1-->1:1:1:1:1:1:2:2:1:2:1:1:2:1:1:
part2-->3:3:4:4:2:2:3:5:2:3:3:4:5:3:5:
CHill60 21-Jun-16 4:37am    
Are you able to change the table layouts - because that is the most awful database design ever.
kumari567 21-Jun-16 6:51am    
plaese reply if any solution is possible
CHill60 21-Jun-16 7:09am    
I can't actually work out what it is that you are trying to do. The schema for the tables and some actual sample data might help. What is the relevance of 30? (the loop)

1 solution

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:
SQL
-- 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:
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:
SQL
;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[^]

[EDIT]
Part2
SQL
;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:
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
 
Share this answer
 
Posted
Updated 21-Jun-16 21:40pm
v5
Comments
kumari567 22-Jun-16 3:05am    
sir please tell me that
;WITH Part1ToColumns AS
(
...
)
INSERT INTO lime_survey_897455 (<columns>)
SELECT
the above step will be done after part2 or before.....I am very beginner in sql
Maciej Los 22-Jun-16 3:06am    
Before...
kumari567 22-Jun-16 3:06am    
sir your code is working but how to do that for part2
Maciej Los 22-Jun-16 3:06am    
In the same way as i did it with Part1
kumari567 22-Jun-16 3:18am    
sir I am not able to do ....I did try
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Pd.to_datetime() not working on a column with different date formats
Column transform in excel
Populate a datagridview with a query that has dynamic columns
How to dynamically add columns to sqlite database?
Code to convert MS excel row column data into different layout
Merging different values in a column using a separator
Generate columns dynamically in RDLC
One column, multiple fields into separate columns..
Update the table column with different where conditions
Merge cells from different columns in a datagridview C#

Advertise
Privacy
Cookies
Terms of Use
Last Updated 22 Jun 2016
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web03 2.8:2024-06-13:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900