kumari567 Ответов: 1

Как преобразовать значение, разделенное столбцом, в другой столбец, где столбцы являются динамическими

У меня есть сложный запрос, который имеет две таблицы, первая из которых имеет столбец типа этого ID, caller_id,datetime, part1, part2, session

теперь я должен разбить часть 1 на другой столбец, а также Часть 2 на другой столбец другой таблицы lime_survey_897455 часть 1 имеет максимальное значение 15, и они будут сохранены в столбце следующим образом
[897455X7X35] ,[897455X7X39] ,"897455X7X43" ,"897455X7X47" ,"897455X7X51" ,"897455X7X84" ,"897455X7X94" ,"897455X7X104" ,"897455X7X114" ,"897455X7X124" ,"897455X7X134" ,"897455X7X144","897455X7X154",
"897455X7X164", " 897455X7X174"
similarlly часть2
"897455X9X55", "897455X11X59" , "897455X13X63", "897455X15X67" , "897455X24X71" , "897455X34X184", "897455X44X194", "897455X54X204" , "897455X64X214", " 897455X74X224" ,
"897455X84X234", "897455X94X244", "897455X104X254", "897455X114X264", " 897455X124X274"

[EDIT - OP предоставил схему таблицы]
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 предоставил некоторые примеры данных]
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)


Что я уже пробовал:

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

kumari567

спасибо за правку

kumari567

часть 1-->1:1:1:1:1:1:2:2:1:2:1:1:2:1:1:
часть 2->3:3:4:4:2:2:3:5:2:3:3:4:5:3:5:

CHill60

Можете ли вы изменить макеты таблиц-потому что это самый ужасный дизайн базы данных когда-либо.

kumari567

нет, я не могу измениться

kumari567

plaese ответьте, если возможно какое-либо решение

CHill60

Я не могу понять, что именно вы пытаетесь сделать. Схема для таблиц и некоторые фактические примеры данных могут помочь. Какова значимость 30? (петля)

kumari567

это длина части 1

CHill60

Как я уже сказал, схема таблиц и некоторые фактические примеры данных могут помочь.

kumari567

Содержание было перенесено на вопрос

kumari567

Содержание было перенесено на вопрос

kumari567

этого будет вполне достаточно......как я могу дать образец данных...потому что я пытаюсь взять snnipping изображения инструмента, но где atached

CHill60

Вы не можете прикрепить сюда изображения, а изображения не очень полезны, если кто-то пытается использовать ваши данные для решения вашей проблемы.
Просто вставьте несколько строк данных для каждой таблицы в свой вопрос

kumari567

Содержание было перенесено на вопрос

kumari567

Содержание было перенесено на вопрос

CHill60

Ладно, я попробую еще раз. Что вы на самом деле пытаетесь сделать?
Код, который вы опубликовали, содержит по крайней мере 5 синтаксических ошибок и много бессмысленного кода (а также много закомментированного материала). Невозможно определить, что вы пытаетесь сделать ... объясните словами или ожидаемыми результатами

Maciej Los

Что такое part_1 и part_2? Где находятся значения, разделенные запятыми?
Извините, я вас не понимаю...

kumari567

Я уже приводил пример.part1 и part2 - это значения, разделенные двоеточием

kumari567

Содержание было перенесено на вопрос

1 Ответов

Рейтинг:
11

Maciej Los

Я не уверен, что хорошо вас понимаю, но ... кажется, вы хотите расстаться Part1 и Part2 данные разделяются двоеточием на столбцы.

Что ж, вам нужно использовать КТОС[^] разбить данные, разделенные двоеточием, на строки, а затем Стержень[^] данные, как показано ниже:

-- 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


Результат:
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


Чтобы иметь возможность вставлять вышеуказанные данные в lime_survey_897455, вы должны использовать это утверждение:
;WITH Part1ToColumns AS
(
 ...
)
INSERT INTO lime_survey_897455 (<columns>)
SELECT ...


Вы должны сделать то же самое с Part2 столбец, используя похожие КТР. Наконец, вы должны обновить данные с помощью ID и Caller_id ценности.


Надеюсь, это поможет!

Для получения дополнительной информации о CTE и PIVOT, пожалуйста, смотрите:
Рекурсивные Запросы, Использующие Обобщенные Табличные Выражения[^]
С common_table_expression (Transact-SQL)[^]
CP статьи о CTE[^]
CP статьи о Pivot[^]

[РЕДАКТИРОВАТЬ]
Часть2
;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


Результат:
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

kumari567

Сэр пожалуйста скажите мне это
;С Part1ToColumns как
(
...
)
Вставить в lime_survey_897455 (<columns>)
ВЫБИРАТЬ
вышеприведенный шаг будет выполнен после части 2 или до нее.....Я очень новичок в sql

Maciej Los

До...

kumari567

сэр ваш код работает но как это сделать для части 2

Maciej Los

Точно так же, как я сделал это с частью 1

kumari567

сэр, я не могу этого сделать ....Я пытался

Maciej Los

Проверьте обновленный ответ. Пожалуйста, обратите внимание, что я не могу посвятить вам больше времени. Вы должны внимательно прочитать мой ответ, прочитать все ссылки на статьи. Наконец, вы должны попытаться решить свою проблему самостоятельно, используя приведенные примеры!

kumari567

да я знаю сэр но все же я застрял в соединении этих двух избранных запросов

потому что перед вставкой я должен соединить выше два оператора select

(Выберите ID, Caller_Id, [1] Как [897455X7X39], [2] как [897455X7X43], [3] как [897455X7X47], [4] как [897455X7X51], [5] как [897455X7X84],
[6] КАК [897455X7X94], [7] КАК [897455X7X104], [8] КАК [897455X7X114], [9] КАК [897455X7X124], [10] КАК [897455X7X134],
[11] КАК [897455X7X144], [12] КАК [897455X7X154], [13] КАК [897455X7X164], [14] КАК [897455X7X174]
ОТ (
Выберите MyCounter, удостоверение личности, Caller_Id, случай, когда P1 = '1' потом 'y' еще 'Н' конец как P1
Из части 1tocolumns
) КАК DT
PIVOT (MAX(P1) для MyCounter IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14])) как PT) внутреннее соединение
(Выберите ID, Caller_Id, [1] Как [897455X9X55], [2] как [897455X11X59], [3] как [897455X13X63], [4] как [897455X15X67], [5] как [897455X24X71],
[6] КАК [897455X34X184], [7] КАК [897455X44X194], [8] КАК [897455X54X204], [9] КАК [897455X64X214], [10] КАК [897455X74X224],
[11] КАК [897455X84X234], [12] КАК [897455X94X244], [13] КАК [897455X7X164], [14] КАК [897455X104X254], [15] КАК [897455X114X264], [16] КАК [897455X124X274]
ОТ (
Выберите MyCounter, ID, Caller_Id, P2
Из Part2ToColumns
) КАК DT
PIVOT (MAX(P2) для MyCounter IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16])) как PT) b on a.ID=b.ID

kumari567

thanku так muchh сэр .....вы мне очень помогли

Maciej Los

Всегда пожалуйста.