How to create columns for each unique value found in a record

Question

A web application which allows users to create their own health questionnaire forms, each form in turns allows the user to customize the form and add as many input fields as needed for questions and answers. Now these results are stored in two tables

tblTransaction:

  ID  Person      Date        FormName
 -------------------------------------------
  1   John Smith 19/09/2017   FamilyHistory
  2   Pat White  18/07/2017   FamilyHistory
  2   Pat White  18/07/2017   Diabetes

tblContent:

  ID  TranID   FieldName               Value   
 --------------------------------------------
  1    1       Do you smoke            Yes
  2    1       How many a day          30
  3    2       Cancer in the family    No
  4    2       Blood pressure          No

I need to create an extraction per Date, Person, FormName and then list each unique FieldName found per FormName as a separate coloumn, taking into account that the count and value of FieldName is not known.

This is what the result should look like.

Date       Person      FormName         Do you smoke    How many a day      
----------------------------------------------------------------------
19/09/2017 John Smith  FamilyHistory    Y               30

Why do you need to export it in this way? This kind of data format is good for humans but terrible for computers. — Nick.Mc, Sep 20 '17 at 13:17

score 1 · Answer 1 · 2017-09-20T13:25:13.557

You can do this:

SELECT 
  t.Date AS Date,
  t.[Person],
  t.[FormName],
  MAX(CASE WHEN c.FieldName = 'Do you smoke' THEN c.Value END) AS 'Do you smoke',
  MAX(CASE WHEN c.FieldName = 'How many a day' THEN c.Value END) AS 'How many a day',
  MAX(CASE WHEN c.FieldName = 'Cancer in the family' THEN c.Value END) AS 'Cancer in the family',
  MAX(CASE WHEN c.FieldName = 'Blood pressure' THEN c.Value END) AS 'Blood pressure'
FROM tblContent AS c
INNER JOIN tblTransaction AS t ON c.[TranID] = t.[Id]
WHERE t.Person = 'John Smith' AND t.FormName = 'FamilyHistory'
GROUP BY  t.Date,
  t.[Person],
  t.[FormName];

demo

You can also use pivot:

SELECT *
FROM
(
    SELECT t.Date,
      t.[Person],
      t.[FormName],
      c.FieldName,
      c.Value
    FROM tblContent AS c
    INNER JOIN tblTransaction AS t ON c.[TranID] = t.[Id]
    WHERE t.Person = 'John Smith' AND t.FormName = 'FamilyHistory'
) AS c
PIVOT
(
   MAX(Value)
   FOR FieldName IN([Do you Smoke], [How many a Day])
) AS p;

pivot demo

How to create columns for each unique value found in a record

1 Answers1