MS Access or MSSQL query to combine multiple rows of data based on common value in a single column (data exported from survey monkey)

Question

I have the following data and would like to find a way using either MS Access SQL or MS SQL Server to create a query to consolidate everything to a single line.

RespondentID  Name             EID       Phone      Why     How          Contact
1809593812                                                  Testing      0
1809593812                                          Testing              0
1809593812                     19091193                                  0
1809593812    Jennifer                                                   0
1809593812                               8885555555                      0

I would like it to look like:

RespondentID   Name             EID       Phone       Why     How      Contact
1809593812     Jennifer         19091193  8885555555  Testing Testing  0

Is this possible using either MS Access SQL Query or MS SQL Server Query?

There are many more records like this. I do not have control over its layout as it is a daily export from an external source.

What I have so far in MS Access Query is:

SELECT DISTINCT dbo_ResponsesText.RespondentID,
IIf(dbo_ResponsesText.Key1=4383976121,ResponseText,Null) AS Name, 
IIf(dbo_ResponsesText.Key1=4383976120,ResponseText,Null) AS EID,
IIf(dbo_ResponsesText.Key1=4388819402,ResponseText,Null) AS Phone, 
IIf(dbo_ResponsesText.QuestionID=340372755,ResponseText,Null) AS Why, 
IIf(dbo_ResponsesText.QuestionID=340372805,ResponseText,Null) AS How, 
IIf(dbo_Responses.Key1=4305593988,-1,0) AS Contact
FROM dbo_ResponsesText
INNER JOIN dbo_Responses ON dbo_ResponsesText.RespondentID = dbo_Responses.RespondentID
ORDER BY dbo_ResponsesText.RespondentID

Actual Table Structure:

dbo_ResponsesText Table
ID   RespondentID  CollectorID  QuestionID  Key1  ResponseText   DateAdded
1    1821607396    25982810     340372755   0     Name,EID,etc.  5/1/2012 3:29:00 PM

dbo_Responses Table:
RespondentID   CollectorID  QuestionID  Key1         Key2  Key3
1809593812     25982810     340372567   4308039090   0     0

Sandr · Answer 1 · 2013-10-30T17:50:35.943

0

You can try the following:

You can try the following:

select x.RespondentID, max(x.Name) as Name, max(x.EID) as EID,
       max(x.Phone) as Phone, max(x.Why) as Why, max(x.How) as How, x.Contact
from
(
  SELECT r.RespondentID,
    IIf(t.Key1=4383976121, ResponseText, Null) AS Name, 
    IIf(t.Key1=4383976120 ,ResponseText, Null) AS EID,
    IIf(t.Key1=4388819402, ResponseText, Null) AS Phone, 
    IIf(t.QuestionID=340372755, ResponseText, Null) AS Why, 
    IIf(t.QuestionID=340372805, ResponseText, Null) AS How, 
    IIf(r.Key1=4305593988,-1,0) AS Contact
    FROM dbo_ResponsesText t
    INNER JOIN dbo_Responses r ON t.RespondentID = r.RespondentID
) x
GROUP BY x.RespondentID, x.Contact
ORDER BY x.RespondentID

I have checked the above code in the MS Access 2010 and it works fine (tables were created, data filled). Screenshots attached: initial query with data (yours) and result query with data (mine) Original query - initial state Modified query

edited Oct 30 '13 at 17:50

answered Oct 30 '13 at 16:38

Sandr

776
1
6
10

did not work. I believe max(Name) as Name, was supposed to be max(x.Name) AS name, However, even with that change I get "Reserved error (-1038); there is no message for this error." – Brad Charboneau Oct 30 '13 at 16:58
ok that part works but i have 1 more issue. it seems as though i get double records when the Contact field is supposed to equal -1. I get 1 record with all of the data Contact = -1 and another record witgh all of the data where Contact = 0. – Brad Charboneau Oct 30 '13 at 19:41
in this case the Contact field in the inner select need to be aggregated with function (max, min, etc. - according to your logic). In this case the "doubles" of rows can be eliminated. Among this, the formula for the Contract evaluation can be changed to use null instead of 0. – Sandr Nov 01 '13 at 14:07

score 0 · Answer 2 · answered Oct 30 '13 at 23:46

the suggestion Sandr Posted is Valid the only change I needed to make was to change the following converting x.Contact to max(x.Contact) as Contact

Change

select x.RespondentID, max(x.Name) as Name, max(x.EID) as EID, max(x.Phone) as Phone, max(x.Why) as Why, max(x.How) as How, x.Contact

to

select x.RespondentID, max(x.Name) as Name, max(x.EID) as EID, max(x.Phone) as Phone, max(x.Why) as Why, max(x.How) as How, max(x.Contact) As Contact

so that the final query looks like:

SELECT 
    x.RespondentID
    , Max(x.Name) AS Name
    , Max(x.EID) AS EID
    , Max(x.Phone) AS Phone
    , Max(x.Why) AS Why
    , Max(x.How) AS How
    , Max(x.Contact) As Contact
FROM (
     SELECT 
        r.RespondentID
            , IIf(t.Key1=4383976121,ResponseText, Null) AS Name
            , IIf(t.Key1=4383976120 ,ResponseText, Null) AS EID
            , IIf(t.Key1=4388819402,ResponseText, Null) AS Phone
            , IIf(t.QuestionID=340372755,ResponseText, Null) AS Why
            , IIf(t.QuestionID=340372805,ResponseText, Null) AS How
            , IIf(r.Key1=4305593988,-1,NULL) AS Contact 
     FROM dbo_ResponsesText AS t 
     INNER JOIN dbo_Responses AS r ON t.RespondentID = r.RespondentID
)  AS x
GROUP BY x.RespondentID
ORDER BY x.RespondentID;

which gave me the following desired output:

RespondentID  Name     EID       Phone         Why            How            Contact
1811504405    Jenn     123456    456456        Because        Nothing        -1
1820992008    ANDRIA   19289935  909-437-XXXX  Long Response  Long Response   0

For Additional Reference I have included the same type of Query in MSSQL Server format:

SELECT 
x.RespondentID
    , Max(x.Name) AS Name
    , Max(x.EID) AS EID
    , Max(x.Phone) AS Phone
    , Max(x.Why) AS Why
    , Max(x.How) AS How
    , Max(x.Contact) As Contact
FROM (
    SELECT
     RespondentID = r.RespondentID
     ,Name = 
              CASE WHEN t.[Key1] = '4383976121' THEN [ResponseText] 
              ELSE NULL END

     ,EID = 
              CASE WHEN t.[Key1] = '4383976120' THEN [ResponseText] 
              ELSE NULL END

     ,Phone = 
              CASE WHEN t.[Key1] = '4388819402' THEN [ResponseText] 
              ELSE NULL END

     ,Why = 
              CASE WHEN t.[QuestionID] = '340372755' THEN [ResponseText] 
              ELSE NULL END

     ,How = 
              CASE WHEN t.[QuestionID] = '340372805' THEN [ResponseText] 
              ELSE NULL END

     ,Contact = 
              CASE WHEN r.[Key1] = '4305593988' THEN 'True'
              ELSE 'False' END

     FROM [NPS].[dbo].[ResponsesText] t

     Join [NPS].[dbo].[Responses] r ON t.RespondentID=r.RespondentID
)  AS x
GROUP BY x.RespondentID
ORDER BY x.RespondentID;

MS Access or MSSQL query to combine multiple rows of data based on common value in a single column (data exported from survey monkey)

2 Answers2