The background:
Think of an application that lets people make surveys with custom questions,In a particular case,interview families,An
interviewer goes toHouse 1and interviews two membersMember 1
andMember 2. He asks questions like. What is this house
address?,What is your name and age?. The answers for
that is common for the Members and the answers that are specific for
them are stored in the same table
在对某些表进行一些连接之后,转动结果,我最终得到以下表格结构.
迄今取得的成就
| ID | ADDRESS | MEMBER | AGE | SubformIteration | |----|---------|----------|--------|-------------------| | 1 | HOUSE 1 | (null) | (null) | (null) | | 1 | (null) | MEMBER h | 18 | s0 | | 1 | (null) | MEMBER i | 19 | s1 | | 2 | HOUSE 2 | (null) | (null) | (null) | | 2 | (null) | MEMBER x | 36 | s0 | | 2 | (null) | MEMBER y | 35 | s1 | | 3 | HOUSE 3 | (null) | (null) | (null) | | 3 | (null) | MEMBER a | 18 | s0 | | 3 | (null) | MEMBER b | 19 | s1 |
我正在尝试找到一种方法来使表格格式化如下：
所需输出
| ID | ADDRESS | MEMBER | AGE | SubformIteration | |----|---------|----------|--------|-------------------| | 1 | HOUSE 1 | MEMBER 1 | 18 | s0 | | 1 | HOUSE 1 | MEMBER 2 | 19 | s1 | | 2 | HOUSE 2 | MEMBER x | 36 | s0 | | 2 | HOUSE 2 | MEMBER y | 35 | s1 | | 3 | HOUSE 3 | MEMBER a | 18 | s0 | | 3 | HOUSE 3 | MEMBER b | 19 | s1 |
我没有足够的sql词汇来描述和搜索所需的操作/过程,因为我是sql的新手,如果有人能告诉我一个有效的方式来实现这一点,我将非常感激.
重要
不要依赖于QuestionText列,因为它会改变当有人决定改变问题时
编辑
源表
Sql fiddle link with all the below tables
根据答案中的建议,我发布源表和查询,希望对问题有更好的了解
问题表
+------------+--------------+---------+----------+---------------+ | QuestionID | QuestionText | type | SurveyID | IsIncremental | +------------+--------------+---------+----------+---------------+ | 3483 | subform | subform | 311 | 1 | | 3484 | MEMBER | text | 311 | 0 | | 3485 | AGE | number | 311 | 0 | | 3486 | ADDRESS | address | 311 | 0 | +------------+--------------+---------+----------+---------------+
结果表
+----------+-------------------------+----------+ | ResultID | DateSubmitted | SurveyID | +----------+-------------------------+----------+ | 2272 | 2017-04-12 05:11:41.477 | 311 | | 2273 | 2017-04-12 05:12:22.227 | 311 | | 2274 | 2017-04-12 05:13:02.227 | 311 | +----------+-------------------------+----------+
Chunks表,其中存储所有答案：
+---------+------------+----------+------------+------------------+ | ChunkID | Answer | ResultID | QuestionID | SubFormIteration | +---------+------------+----------+------------+------------------+ | 9606 | HOUSE 1 | 2272 | 3486 | NULL | | 9607 | MEMEBER 1 | 2272 | 3484 | NULL | | 9608 | 12 | 2272 | 3485 | NULL | | 9609 | MEMBER 2 | 2272 | 3484 | s1 | | 9610 | 10 | 2272 | 3485 | s1 | | 9611 | MEMEBER 1 | 2272 | 3484 | s0 | | 9612 | 12 | 2272 | 3485 | s0 | | 9613 | MEMBER 2 | 2272 | 3484 | s1 | | 9614 | 10 | 2272 | 3485 | s1 | | 9615 | HOUSE 2 | 2273 | 3486 | NULL | | 9616 | MEMBER A | 2273 | 3484 | NULL | | 9617 | 23 | 2273 | 3485 | NULL | | 9618 | MEMBER B | 2273 | 3484 | s1 | | 9619 | 25 | 2273 | 3485 | s1 | | 9620 | MEMBER A | 2273 | 3484 | s0 | | 9621 | 23 | 2273 | 3485 | s0 | | 9622 | MEMBER B | 2273 | 3484 | s1 | | 9623 | 25 | 2273 | 3485 | s1 | | 9624 | HOUSE 3 | 2274 | 3486 | NULL | | 9625 | MEMBER K | 2274 | 3484 | NULL | | 9626 | 41 | 2274 | 3485 | NULL | | 9627 | MEMBER J | 2274 | 3484 | s1 | | 9628 | 26 | 2274 | 3485 | s1 | | 9629 | MEMBER K | 2274 | 3484 | s0 | | 9630 | 41 | 2274 | 3485 | s0 | | 9631 | MEMBER J | 2274 | 3484 | s1 | | 9632 | 26 | 2274 | 3485 | s1 | +---------+------------+----------+------------+------------------+
我写了以下存储过程,它产生了这个问题中给出的第一个表：
ALTER PROCEDURE [dbo].[ResultForSurvey] @SurveyID int
AS
DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX),@colsAggregated as nvarchar(max);
IF OBJECT_ID('tempdb.dbo.#Temp','U') IS NOT NULL
DROP TABLE #Temp;
SELECT *
INTO #Temp
FROM (Select Answer=( case
When Questions.type='checkBoxes' or Questions.IsIncremental=1 THEN STUFF((SELECT distinct ',' + c.Answer
FROM Chunks c Where c.ResultID=Results.ResultID and c.QuestionID=Questions.QuestionID and (Chunks.SubFormIteration IS NULL )
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,'')
else Chunks.Answer end),Chunks.SubFormIteration,Questions.QuestionText,Questions.type,Questions.QuestionID,Chunks.ResultID,Results.ResultID as Action,Results.DateSubmitted,Results.Username,Results.SurveyID from Chunks Join Questions on Questions.QuestionID= Chunks.QuestionID Join Results on Results.ResultID=Chunks.ResultID Where Results.SurveyID=@SurveyID) as X
SET @colsAggregated = STUFF((SELECT distinct ','+ 'max('+ QUOTENAME(c.QuestionText)+') as '+ QUOTENAME(c.QuestionText)
FROM #Temp c
FOR XML PATH(''),'')
print @colsAggregated
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.QuestionText)
FROM #Temp c
FOR XML PATH(''),'')
set @query = 'SELECT ResultID,max(Username) as Username,max(DateSubmitted) as DateSubmitted,max(SubFormIteration) as SubFormIteration,' + @colsAggregated + ' from
(
select *
from #Temp
) as y
pivot
(
max(Answer)
for QuestionText in (' + @cols + ')
) as p GROUP BY
ResultID,SubFormIteration'
execute(@query)
解决方法
发布给您您的原始结果的查询可能是有益的;有可能重写原始查询以避免这种复杂性.根据给定的信息,这是解决这个问题的最简单的方法：
SELECT h1.Id,h2.Address,h1.Member,h1.Age,h1.MemberNo FROM House h1 INNER JOIN House h2 ON h1.Id = h2.Id WHERE h2.Address IS NOT NULL -- Eliminates the results whre the Address is NULL after the join AND h1.Member IS NOT NULL -- Eliminates the results that would show up from the original table (t1) where there is no Member field
更新：
下面是使用临时表的表结构的简单示例：
DROP TABLE #Questions
DROP TABLE #Results
DROP TABLE #Chunks
CREATE TABLE #Questions
(
QuestionId INT,QuestionText VARCHAR(MAX),type VARCHAR(MAX),SurveyID INT,IsIncremental INT
)
CREATE TABLE #Results
(
ResultId INT,DateSubmitted DATETIME,SurveyID INT
)
CREATE TABLE #Chunks
(
ChunkId INT,Answer VARCHAR(MAX),ResultId INT,QuestionId INT,SubFormIteration VARCHAR(20)
)
INSERT INTO #Results
VALUES (2272,'04-12-2017',311),(2273,(2274,311)
INSERT INTO #Chunks
VALUES (9606,'WhiteHouse',2272,3486,NULL),(9607,'MEMBER 1',3484,(9608,'12',3485,(9609,'MEMBER 2','s1'),(9610,'10',(9611,'s0'),(9612,(9613,(9614,(9615,'RpBhavan',2273,(9618,'MEMBER B',(9619,'25',(9620,'MEMBER A',(9621,'23','s0')
INSERT INTO #Questions
VALUES (3483,'subform',311,1),( 3484,'MEMBER','text',0 ),(3485,'AGE','number',0),(3486,'ADDRESS','address',0)
这是一种在不使用PIVOT和XML的情况下生成您的查找结果的方式：
; WITH Responses AS (
SELECT
c.ResultId,QuestionText,Answer,c.SubFormIteration
FROM #Chunks c
INNER JOIN #Results r
ON c.ResultId = r.ResultId
INNER JOIN #Questions q
ON q.QuestionId = c.QuestionId
WHERE c.SubFormIteration IS NOT NULL -- Removes the "Address" responses and duplicate Answers
),FindAddress AS (
-- Pulls ONLY the address for each ResultId
SELECT
ResultId,MAX(CASE WHEN QuestionText = 'ADDRESS' THEN Answer END) AS [Address]
FROM #Chunks c
INNER JOIN #Questions q
ON q.QuestionId = c.QuestionId
GROUP BY ResultId
)
-- Combines all responses and the address back together
SELECT
r.ResultId,fa.Address,MAX(CASE WHEN QuestionText = 'MEMBER' THEN Answer END) AS [MEMBER],MAX(CASE WHEN QuestionText = 'AGE' THEN Answer END) AS [Age],SubFormIteration
FROM Responses r
INNER JOIN FindAddress fa
ON fa.ResultId = r.ResultId
GROUP BY r.ResultId,SubFormIteration,fa.Address
本质上,我将一个相当大的查询打破了一个通用表表达式(CTE).每个查询有一个目的：a)响应提取除地址之外的所有响应,b)仅拉取基于ResultId的地址,c)将两个查询合并在一起.
MAX(CASE …)后跟GROUP BY是使用PIVOTS的替代方法,它们基本上执行相同.
