Pivot From ROWS to Columns

[nirakar]

Hello,


I need a urgent help to pivot the data from rows to columns uisng MS Acces SQL


Hi I have a requirement to pivot the data from rows to columns.
APPLICANT_NAME,License_Renewal_NAME,License_NUMBER __C,License_TYPE__C,LICENSE_RSUBTYPE__C,APPLICANT_ ETHNICITY__C, License_Renewal.APPLICANT_GENDER__C,APPLICANT_ZIP_ CODE__C,QUESTION_NAME,ANSWER_TEXT__C,QUESTION_BODY __C
"JAN","LR-001352966","LPN.039005.MEDS-IV","Licensed Practical",,"White","Female","43824","Q-000559","Yes",
"JAN","LR-001352966","LPN.039005.MEDS-IV","Licensed Practical",,"White","Female","43824","Q-000686","Yes",
"JAN","LR-001352966","LPN.039005.MEDS-IV","Licensed Practical",,"White","Female","43824","Q-001483","No",


and I want output like this
APPLICANT_NAME,License_Renewal_NAME,License_NUMBER __C,License_TYPE__C,LICENSE_RSUBTYPE__C,APPLICANT_ ETHNICITY__C, License_Renewal.APPLICANT_GENDER__C,APPLICANT_ZIP_ CODE__C,QUESTION_1,ANSWER_TEXT__C 1,QUESTION_BODY__1,QUESTION_2,ANSWER_TEXT__C 2,QUESTION_BODY__2,QUESTION_3,ANSWER_TEXT__C 3,QUESTION_BODY__3
"JAN","LR-001352966","LPN.039005.MEDS-IV","Licensed Practical",,"White","Female","43824","Q-000559","Yes","Q-000686","Yes","Q-001483","No"


Please help

[nirakar]

I tried with this but getting an syntax error. Can anyone pls help?

TRANSFORM First(QUESTION_NAME,ANSWER_TEXT__C,QUESTION_BODY__ C) AS QUESTION
SELECT APPLICANT_NAME, License_Renewal_NAME,
License_NUMBER__C, License_TYPE__C, License_RSUBTYPE__C,
APPLICANT_ETHNICITY__C, LicenseRenewalAPPLICANT_GENDER__C,
APPLICANT_ZIP_CODE__C
FROM
(
SELECT APPLICANT_NAME, License_Renewal_NAME,
License_NUMBER__C, License_TYPE__C, License_RSUBTYPE__C,
APPLICANT_ETHNICITY__C, LicenseRenewalAPPLICANT_GENDER__C,
APPLICANT_ZIP_CODE__C,
QUESTION_NAME ,
ANSWER_TEXT__C,
QUESTION_BODY__C
FROM report
)
GROUP BY APPLICANT_NAME, License_Renewal_NAME,
License_NUMBER__C, License_TYPE__C, License_RSUBTYPE__C,
APPLICANT_ETHNICITY__C, LicenseRenewalAPPLICANT_GENDER__C,
APPLICANT_ZIP_CODE__C
PIVOT (QUESTION_NAME,ANSWER_TEXT__C,QUESTION_BODY__C
)

[CJ_London]

sometimes you are using single underlines, sometimes multiple underlines. Perhaps you have them mixed up somewhere?

Also looks like you have two '(' and three ')'

And looks like you have three column headings - you can only have one in a crosstab. If using crosstabs you will need to create three, one for each column then use a 4th query to link them together

Have you tried using a pivot query?

[nirakar]

Hi Ajax,
Can you please help jow to use pivot query?

[nirakar]

I tried and getting an error too much cross tab columns. Please help

[CJ_London]

This works for overlapping windows, don't know about tabbed windows (see settings in file>options>current database)

Create your basic select query and open it. Then right click on the window top (where the query name appears) and select Pivot Table view. You can now drag and drop fields as required into rows, columns and values.

You can also create a form by highlighting the query, then in the ribbon, select create>more forms>pivot table. Once saved you can use it as a subform in a form.

You have some basic formatting ability

[nirakar]

I tried bu getting an error too many cross tab fields

[CJ_London]

you can only have one crosstab column. Suggest start by doing a pivot on your report table until you understand how it works