Flip rows to Columns in MS Access

Hi,

I have data like this:

EMPLID      TITLE          NAME        TITLE2            NAME       TITLE3      NAME
001           Director       John          Sr Director      Alice        VP             Fred
002           Sr Director   Tom          VP                 Simon       NULL          NULL
003           NULL            NULL         Director          John         VP             Simon

and I need the table to be converted into this:

EMPLID         DIRECTOR     SR DIRECTOR    VP
001              John              Alice                 Fred              
002                                  Tom                  Simon
003              John                                       Simon

What sort of algorithm in SQL can I use to do this?


deckard666Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
will_scarlet7Connect With a Mentor Commented:
Modification on the above:

  SELECT
    EMPLID,
    IIf(TITLE='Director', NAME, IIf(TITLE2='Director', NAME2, IIf(TITLE3='Director', NAME3, Null))) AS DIRECTOR,
    IIf(TITLE='Sr Director', NAME, IIf(TITLE2='Sr Director', NAME2, IIf(TITLE3='Sr Director', NAME3, Null))) AS SR_DIRECTOR,
    IIf(TITLE='VP', NAME, IIf(TITLE2='VP', NAME2, IIf(TITLE3='VP', NAME3, Null))) AS VP
  FROM
    tblYourTable;
0
 
will_scarlet7Commented:
Do you mean that your table currently looks like this:

EMPLID      TITLE          NAME
001           Director       John
001           Sr Director   Alice
001           VP               Fred
002           Sr Director   Tom
002           VP               Simon
003           Director       John
003           VP               Simon

Or do your multible "Name" fields actually have different names?

0
 
will_scarlet7Commented:
Sorry that should have said:

>> Or do your multiple "Name" fields actually have different names?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
deckard666Author Commented:
Sorry, it has multiple name fields.

Like this:

EMPLID      TITLE          NAME        TITLE2           NAME2       TITLE3     NAME3
001           Director       John          Sr Director      Alice        VP             Fred
002           Sr Director   Tom          VP                 Simon       NULL          NULL
003           NULL            NULL         Director          John         VP             Simon



0
 
Gustav BrockCIOCommented:
Try this:

  SELECT
    EMPLID,
    IIf(TITLE='Director', NAME1, Null) AS DIRECTOR,
    IIf(TITLE='Sr Director', NAME2, Null) AS SR_DIRECTOR,
    IIf(TITLE='VP', NAME3, Null) AS VP
  FROM
    tblYourTable;

/gustav
0
 
Gustav BrockCIOCommented:
Hmmm, that was too easy:

  SELECT
    EMPLID,
    IIf(TITLE='Director', NAME, IIf(TITLE2='Director', NAME2, IIf(TITLE3='Director', NAME3, Null)))
      AS DIRECTOR,
    IIf(TITLE='Sr Director', NAME, IIf(TITLE2='Sr Director', NAME2, IIf(TITLE3='Sr Director', NAME3, Null)))
      AS SR_DIRECTOR,
    IIf(TITLE='VP', NAME, IIf(TITLE2='VP', NAME2, IIf(TITLE3='VP', NAME3, Null)))
      AS VP
  FROM
    tblYourTable;

/gustav
0
 
mcorrenteCommented:
You'll have to add to the query every time you add a different title if you do it like that.
0
 
mcorrenteCommented:
Try this... create two queries... the first is a union query:

SELECT YourTable.EmplID, YourTable.Title, YourTable.Name FROM YourTable WHERE (((YourTable.Title)>""))
UNION SELECT YourTable.EmplID, YourTable.Title2, YourTable.Name2 FROM YourTable WHERE (((YourTable.Title2)>""))
UNION SELECT YourTable.EmplID, YourTable.Title3, YourTable.Name3 FROM YourTable WHERE (((YourTable.Title3)>""));

0
 
mcorrenteCommented:
The second is a crosstab:

TRANSFORM First(FirstQuery.Name) AS FirstOfName
SELECT FirstQuery.EmplID
FROM FirstQuery
GROUP BY FirstQuery.EmplID
PIVOT FirstQuery.Title;
0
 
mcorrenteCommented:
then when you run the second query, it will create a column for every different title listed in your table.
0
 
deckard666Author Commented:
Thanks. First answer did it fine.
0
 
will_scarlet7Commented:
Glad I could help!
God bless!
Sam

All fine with you Gustav (since I was modifying your post)?
0
 
Gustav BrockCIOCommented:
No problem. I guess God left my side for a moment ...

Have a nice and quiet weekend all of you!

/gustav
0
 
will_scarlet7Commented:
Gustav,
I'm sure you know this, but God nevel leaves our side. He just spreads the blessings around :)
0
 
Gustav BrockCIOCommented:
OK, in that case he was just very modest with my dose! But as I said, no problem, I can handle it.

/gustav
0
All Courses

From novice to tech pro — start learning today.