0

I have a table that has the following layout:

RecordID    OptionID    StudentID   ClassID DEMOGRAPHIC     DemographicOption
74276205    1283131     20348031    1766586 REGION          East
74276205    866932      20348031    1766586 OFFICE          Boston
74276205    867044      20348031    1766586 CAREER_LEVEL    Manager
74276205    867016      20348031    1766586 FSS             Con
74276205    867073      20348031    1766586 SERVICE_AREA    Human Capital
74276205    1196052     20348031    1766586 SERVICE_LINE    HR Transformation
74276205    1264928     20348031    1766586 INDUSTRY        Life Sciences

I need to take the demographic column and make separate columns from the results that correspond to the labels in the demographic column.

How can do this I have tried a couple of things that end up with the results being on separate rows but I need the data on one row like below:

RecordID    OptionID    StudentID   ClassID DEMOGRAPHIC REGION  OFFICE  CAREER_LEVEL    FSS SERVICE_AREA    SERVICE_LINE        INDUSTRY
74276205    1283131     20348031    1766586 REGION      East    Boston  Manager         Con Human Capital   HR Transformation   Life Sciences

Any help with this is greatly appreciated.

jpw
  • 44,361
  • 6
  • 66
  • 86
Nelson Bradley
  • 55
  • 2
  • google `sql pivot` - some db's have built in pivoting functionality so you should tell us which db you're using – FuzzyTree May 05 '15 at 00:36
  • Like @jpw, I can't tell what the role of OptionID is. In particular, it almost seems as though there is some relation between it and Demographic and DemographicOption. In any event, as a general approach, irrespective of database, this sort of thing can be done by joining the table to itself, perhaps through subqueries or some other means. If we can get a slightly better understanding of the data, we can most likely fashion a query that will work. – Jerry Oberle May 05 '15 at 01:12

1 Answers1

0
SELECT
    t.RecordID,
    t.OptionID,
    t.StudentID,
    t.ClassID,
    t.Region,
    o.Office,
    c.Career_Level,
    f.FSS,
    sa.Service_Area,
    sl.Service_Line,
    i.Industry
FROM tablename AS t
JOIN tablename AS o
    ON t.recordid = o.recordid AND demographic = 'OFFICE'
JOIN tablename AS c
    ON t.recordid = c.recordid AND demographic = 'CAREER_LEVEL'
JOIN tablename AS f
    ON t.recordid = f.recordid AND demographic = 'FSS'
JOIN tablename AS sa
    ON t.recordid = sa.recordid AND demographic = 'SERVICE_AREA'
JOIN tablename AS sl
    ON t.recordid = sl.recordid AND demographic = 'SERVICE_LINE'
JOIN tablename AS i
    ON t.recordid = i.recordid AND demographic = 'INDUSTRY'
WHERE demographic = 'REGION'
John Hodge
  • 1,645
  • 1
  • 13
  • 13