troubleshooting Question

Self join and selecting "column as"

Avatar of andywyllie
andywyllie asked on
MySQL Server
1 Comment1 Solution463 ViewsLast Modified:
Hey,
I have a table which looks something like

RowId |AnalysisId | EmployeeId | Question    | QuestionType | Answer             |
---------------------------------------------------------------------------------------
  1       |         5           |     1            |Age               |       Number      |35
  2       |         5           |     7            |Age             |       Number      |21
  3       |         6           |     1            |University   |       Yes/No    |Yes
  4       |         6           |     7            |University   |       Yes/No    |No
  5       |         7           |     1            |StartDate    |       Date         |10/10/2007
  6       |         7           |     7            |StartDate    |       Date         |12/11/1994

I need the fields of the Question column to be columns in another table with the Answer being that columns actual data. I.e.

EmployeeId | Age | University | StartDate
  1               | 35    |    Yes       | 10/10/2007
  7                | 21    |    No         | 12/11/1994

All i can manage at the moment is the correct info but without the column headers using the sql below
EmployeeId | Q1   | Q2            | Q3
  1               | 35    |    Yes       | 10/10/2007
  7                | 21    |    No         | 12/11/1994


How can i specify the names. All the data will be generated dynamically.
SELECT DISTINCT a.EmployeeID, b.Answer AS Q1,c.Answer AS Q2, d.Answer AS Q3FROM #FormattedAnalysis a
 LEFT JOIN #FormattedAnalysis b
  ON a.EmployeeID = b.EmployeeID
  AND b.ANalysisTableID =5
 LEFT JOIN #FormattedAnalysis c
  ON a.EmployeeID = c.EmployeeID
  AND c.ANalysisTableID =6
 LEFT JOIN #FormattedAnalysis d
  ON a.EmployeeID = d.EmployeeID
  AND d.ANalysisTableID =7
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros