Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

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?


0
deckard666
Asked:
deckard666
  • 5
  • 4
  • 4
  • +1
1 Solution
 
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
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
will_scarlet7Commented:
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now