February 20, 2017 at 5:16 am
Hi,
I have 3 tables: Question, Answer and Session
A user may answer all or some of the questions, which is associated to a sessionId
when I join the 3 tables, I will get the question, answer and sessionID (so non answered questions are not in the result even with left join)
So I added a way to re-organise my data, but it's static, because I should put all the question and I want to do it dynamically from question table.
I want an idea how not to add one by one in cases request, but it should be dynamique from question table so that I have one line
MAX(CASE WHEN question = '....' THEN Response END) AS '....',
instead of 23 in my case
this is my result
PS: in #temptable I joined the 3 tables (question, session and answer)
CREATE TABLE #temptable (sessionid INT, Question VARCHAR(50), Response VARCHAR(50))
SELECT
sessionid,
MAX(CASE WHEN question = '2825' THEN Response END) AS 'Invoice number',
MAX(CASE WHEN question = '2826' THEN Response END) AS 'Invoice date',
MAX(CASE WHEN question = '2827' THEN Response END) AS 'Vessel',
MAX(CASE WHEN question = '2828' THEN Response END) AS 'Counterparty',
MAX(CASE WHEN question = '2829' THEN Response END) AS 'Product',
MAX(CASE WHEN question = '2830' THEN Response END) AS 'QuantityMT',
MAX(CASE WHEN question = '2831' THEN Response END) AS 'Price USD',
MAX(CASE WHEN question = '2832' THEN Response END) AS 'Invoice amount USD',
MAX(CASE WHEN question = '2834' THEN Response END) AS 'Supporting documentation ( contract, etc.)',
MAX(CASE WHEN question = '2868' THEN Response END) AS 'Supporting documentation reference number',
MAX(CASE WHEN question = '2869' THEN Response END) AS 'Supporting documentation date',
MAX(CASE WHEN question = '2870' THEN Response END) AS 'Terms of sale',
MAX(CASE WHEN question = '2872' THEN Response END) AS 'Shipping document ',
MAX(CASE WHEN question = '2873' THEN Response END) AS 'Shipping document date',
MAX(CASE WHEN question = '2875' THEN Response END) AS 'Supporting documentation',
MAX(CASE WHEN question = '2876' THEN Response END) AS 'Reference start period',
MAX(CASE WHEN question = '2878' THEN Response END) AS 'Notes',
MAX(CASE WHEN question = '2879' THEN Response END) AS 'Please uplaod sales invoice',
MAX(CASE WHEN question = '2880' THEN Response END) AS 'Please upload contract',
MAX(CASE WHEN question = '2881' THEN Response END) AS 'Please upload shipping document',
MAX(CASE WHEN question = '2882' THEN Response END) AS 'Please unpload statement',
MAX(CASE WHEN question = '2883' THEN Response END) AS 'Reference end period',
MAX(CASE WHEN question = '3154' THEN Response END) AS 'Remarks'
FROM
#temptable
GROUP BY sessionid
thank you
February 20, 2017 at 6:48 am
Not sure exactly what you are looking for here. Your words didn't explain in much detail what you are looking for, so I made a guess based on what I did see. I suspect you may be looking to flatten out your results, but again, it's just a guess. A simple PIVOT might do the trick, but there's no avoiding mentioning all the possible values, and doing so twice.
Take a look at this and see if it's what you're looking for:
CREATE TABLE #temptable (
sessionid INT,
Question VARCHAR(50),
Response VARCHAR(50)
);
--=======================================================
-- INSERT CODE TO POPULATE #temptable here
--=======================================================
CREATE TABLE #QUESTION_CODES (
Question varchar(10) NOT NULL PRIMARY KEY CLUSTERED,
QuestionType varchar(50)
);
INSERT INTO #QUESTION_CODES (Question, QuestionType)
SELECT Question, QuestionType
FROM (
VALUES ('2825', 'Invoice number'),
('2826', 'Invoice date'),
('2827', 'Vessel'),
('2828', 'Counterparty'),
('2829', 'Product'),
('2830', 'QuantityMT'),
('2831', 'Price USD'),
('2832', 'Invoice amount USD'),
('2834', 'Supporting documentation ( contract, etc.)'),
('2868', 'Supporting documentation reference number'),
('2869', 'Supporting documentation date'),
('2870', 'Terms of sale'),
('2872', 'Shipping document'),
('2873', 'Shipping document date'),
('2875', 'Supporting documentation'),
('2876', 'Reference start period'),
('2878', 'Notes'),
('2879', 'Please upload sales invoice'),
('2880', 'Please upload contract'),
('2881', 'Please upload shipping document'),
('2882', 'Please upload statement'),
('2883', 'Reference end period'),
('3154', 'Remarks')
) AS X (Question, QuestionType);
WITH ALL_DATA AS (
SELECT T.*, Q.QuestionType
FROM #temptable AS T
INNER JOIN #QUESTION_CODES AS Q
ON T.Question = Q.Question
)
SELECT sessionid, [Invoice Number], [Invoice Date], [Vessel], [Counterparty], [Product], [QuantityMT],
[Price USD], [Invoice amount USD], [Supporting documentation ( contract, etc.)],
[Supporting documentation reference number], [Supporting documentation date],
[Terms of sale], [Shipping document], [Shipping document date], [Supporting documentation],
[Reference start period], [Notes], [Please upload sales invoice], [Please upload contract],
[Please upload shipping document], [Please upload statement], [Reference end period], [Remarks]
FROM ALL_DATA
PIVOT (MAX(Response) FOR QuestionType IN (
[Invoice Number], [Invoice Date], [Vessel], [Counterparty], [Product], [QuantityMT],
[Price USD], [Invoice amount USD], [Supporting documentation ( contract, etc.)],
[Supporting documentation reference number], [Supporting documentation date],
[Terms of sale], [Shipping document], [Shipping document date], [Supporting documentation],
[Reference start period], [Notes], [Please upload sales invoice], [Please upload contract],
[Please upload shipping document], [Please upload statement], [Reference end period], [Remarks])) AS PVT
ORDER BY sessionid;
DROP TABLE #QUESTION_CODES;
DROP TABLE #temptable;
I had to assume what the meaning of the values you listed in the query were, and that meaning (QuestionType) may be entirely wrong.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply