如何与动态视图数字连接

Question

我正在使用调查数据的多个答案，每个品牌都有自己的变量，多个带有分隔格式的图像是受访者回答超过 1 张图像。

例：

RespondentsID | Q_Brand_1 | Q_Brand_2
--------------+-----------+----------
001      |    2      | 1;2;3;4
002      |    2;3    | 1
003      |    1;4    | 1;2

我需要的是将其以非堆叠(二进制)的形式应用于我们的内部软件。像这样：

RespID | [Brand 1 - Imagery 1] | [Brand 1 - Imagery 2] | [Brand 1 - Imagery 3] | [Brand 1 - Imagery 4] | [Brand 2 - Imagery 1] | [Brand 2 - Imagery 2] | [Brand 2 - Imagery 3] | [Brand 2 - Imagery 4] 
001 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 1
002 | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 0
003 | 1 | 0 | 0 | 1 | 1 | 1 | 0 | 0

我尝试首先使用fn_split函数堆叠数据，然后执行动态透视以将其解锁。但是我发现那些仅适用于 1 个品牌，要为多个品牌执行此操作，我需要通过第一个查询加入它

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME([Text]) 
from [dbo].[tbl_Imagery_Reference]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query 
= 'SELECT a.RespondentsID, ' + @cols + ' from 
(
SELECT a.SERIAL, b.Text as Imagery, 1 as Value
FROM [dbo].[Raw_Data_Imagery_Stacked] as a
left join [dbo].[tbl_Reference_Imagery] as b
on a.Items = b.Precode
where Brand = ''Brand A''
) x
pivot 
(
AVG(Value)
for Imagery in (' + @cols + ')
) p 
right join Raw_Data as a
on a.SERIAL = p.SERIAL
'
execute ('create view dbo.vw_Imagery_unstacked as ' + @Query)

我除了有一个查询来动态生成多个品牌的图像集。

Answer 1

我的脚本可以在sql server的任何version工作。

SELECT  RespondentsID, [Brand 1 - Imagery 1], [Brand 1 - Imagery 2], [Brand 1 - Imagery 3], [Brand 1 - Imagery 4],
ISNULL([Q21], 0) AS [Brand 2 - Imagery 1], ISNULL([Q22], 0) AS [Brand 2 - Imagery 2],
ISNULL([Q23], 0) AS [Brand 2 - Imagery 3], ISNULL([Q24], 0) AS [Brand 2 - Imagery 4]
FROM    ( SELECT    RespondentsID , ISNULL([1], 0) AS [Brand 1 - Imagery 1] , ISNULL([2], 0) AS [Brand 1 - Imagery 2] ,
ISNULL([3], 0) AS [Brand 1 - Imagery 3] , ISNULL([4], 0) AS [Brand 1 - Imagery 4] ,
'Q2' + Q2Value AS Q2Value
FROM      ( SELECT distinct RespondentsID ,
Q1.Value AS Q1Value ,
Q2.Value AS Q2Value
FROM      @ActivityLog BS
OUTER APPLY (SELECT Split.a.value('.', 'NVARCHAR(MAX)') Value 
FROM (SELECT CAST('<X>'+REPLACE(Q_Brand_1, ';', '</X><X>')+'</X>' AS XML) AS String) AS A
CROSS APPLY String.nodes('/X') AS Split(a)) Q1
OUTER APPLY (SELECT Split.a.value('.', 'NVARCHAR(MAX)') Value 
FROM (SELECT CAST('<X>'+REPLACE(Q_Brand_2, ';', '</X><X>')+'</X>' AS XML) AS String) AS A
CROSS APPLY String.nodes('/X') AS Split(a)) Q2
) Src PIVOT ( COUNT(Q1Value) FOR Q1Value IN ( [1], [2],
[3], [4] ) ) Pv
) S2 PIVOT ( COUNT(Q2Value) FOR Q2Value IN ( [Q21], [Q22], [Q23],
[Q24] ) ) Pv2;

检查查询输出数据库小提琴

如果您使用的是SqlServer 2016+ version则可以使用名为string_split的内置函数而不是Sub query。

代码，

OUTER APPLY string_split(Q_Brand_1, ';') Q1
OUTER APPLY string_split(Q_Brand_2, ';') Q2

而不是

OUTER APPLY (SELECT Split.a.value('.', 'NVARCHAR(MAX)') Value 
FROM (SELECT CAST('<X>'+REPLACE(Q_Brand_1, ';', '</X><X>')+'</X>' AS XML) AS String) AS A
CROSS APPLY String.nodes('/X') AS Split(a)) Q1
OUTER APPLY (SELECT Split.a.value('.', 'NVARCHAR(MAX)') Value 
FROM (SELECT CAST('<X>'+REPLACE(Q_Brand_2, ';', '</X><X>')+'</X>' AS XML) AS String) AS A
CROSS APPLY String.nodes('/X') AS Split(a)) Q2

Answer 2

你几乎走在正确的轨道上。您需要将其再透视一个级别。

如果Brand1和Brand2是固定的，并且图像数量将达到4个，那么您不需要动态查询。如果number of brand可以是任何东西，则相应地再增加一个示例数据。

我的脚本在Sql server 2016+，

drop table if exists #temp
create table #temp(RespondentsID varchar(10),Q_Brand_1 varchar(50),Q_Brand_2 varchar(40))
insert into #temp values
('001','2','1;2;3;4')
,('002','2;3','1')
,('003','1;4','1;2')
;with CTE as
(
select RespondentsID,oa.value1,Q_Brand_2 from #temp
outer apply(select value as value1 from string_split(Q_Brand_1,';'))oa
)
,CTE1 as
(
select RespondentsID,value1,value2 from cte
outer apply(select value as value2 from string_split(Q_Brand_2,';'))oa
)
select 
RespondentsID, [Brand_1_Imagery_1], [Brand_1_Imagery_2],[Brand_1_Imagery_3], [Brand_1_Imagery_4]
,cast(isnull([1],0) as bit) as [Brand_2_Imagery_1],cast(isnull([2],0) as bit) as [Brand_2_Imagery_2]
,cast(isnull([3],0) as bit) as [Brand_2_Imagery_3],cast(isnull([4],0) as bit) as [Brand_2_Imagery_4]
from
(
select distinct RespondentsID,cast(isnull([1],0) as bit) as [Brand_1_Imagery_1]
,cast(isnull([2],0) as bit) as [Brand_1_Imagery_2]
,cast(isnull([3],0) as bit) as [Brand_1_Imagery_3],cast(isnull([4],0) as bit) as [Brand_1_Imagery_4],value2
from
(select RespondentsID,value1,value2 from CTE1 c )src
pivot (max(value1) for value1 in([1],[2],[3],[4]))pvt
)src1
pivot(max(value2) for value2 in([1],[2],[3],[4]))pvt1

我有In Built string_split，但您可以使用自己的split string函数。这并不难理解。