Is it possible to transpose data using a query?

Question

Edit: The title may be misleading, as transpose may be the wrong word for what I am trying to accomplish. I am open for suggestions.

I have two tables, tblBase and tblLinks. tblLinks contain URLs for the records in tblBase. The image below shows a basic version of these tables and the relationships.

Relationship

tblBase

tblBaseID
Title

tblLinks

tblLinksID
tblBaseIDFK
LinkURL

I am looking to develop a query or report that will display the records in tblBase and all of the related Links in a kind of flat table. Here is an example:

tblBase:

tblBase Sample Data

tblBaseID---Title
1-----------ABC
2-----------DEF
3-----------HIJ

tblLinks:

tblLinks Sample Data

tblLinksID---tblBaseIDFK---LinkURL
1------------1-------------ABCLink1
2------------1-------------ABCLink2
3------------2-------------DEFLink1
4------------2-------------DEFLink2
5------------2-------------DEFLink3

And this is the final result I am looking for, based on the sample data:

Result

Ideally, I would like to be able to create this using a query, but I do not know if that is possible. As a last resort, I can write VBA to populate some temporary table and go through each record in tblLinks, putting the data in it's proper place. If a query is not possible, perhaps there is a better solution than the temporary table that someone could point me towards.

Thank you.

possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Wolph, Jan 08 '15 at 15:51
@Wolph Why is sql server relevant to ms access? To the OP look at a crosstab query. — Fionnuala, Jan 08 '15 at 15:52
Thank you, @Wolph, but my example contains 2 linked tables, which makes for a very different case than the one you linked to. — Michael Blaustein, Jan 08 '15 at 15:53
@Fionnuala, while at first glance it does look like a crosstab query would be the answer, crosstabs appear to output aggregates, and I am not looking to aggregate here. — Michael Blaustein, Jan 08 '15 at 17:06
If you posted data rather than images, you might get more help. An image is not a lot of use in setting up a test. — Fionnuala, Jan 08 '15 at 17:11

score 1 · Answer 1 · answered Feb 02 '15 at 21:27

I suggest 2 different options.

First Option: Static query, this option has the advantage of be easy, but the disadvantage of is static for a N number of links per recrode in tblBase

SELECT B.tblBaseID
     , B.Title
     , MAX(IIF( L.BaseIdRank = 1, L.LinkURL, '')) AS Link1
     , MAX(IIF( L.BaseIdRank = 2, L.LinkURL, '')) AS Link2
     , MAX(IIF( L.BaseIdRank = 3, L.LinkURL, '')) AS Link3
FROM   tblBase AS B
    LEFT JOIN
       (SELECT L1.tblLinksID
             , L1.tblBaseIDFK
             , L1.LinkURL
             , COUNT(*) AS BaseIdRank
        FROM   tblLinks AS L1
            INNER JOIN
               tblLinks AS L2
               ON  L1.tblLinksID >= L2.tblLinksID
               AND L1.tblBaseIDFK = L2.tblBaseIDFK
        GROUP BY L1.tblLinksID
             , L1.tblBaseIDFK
             , L1.LinkURL
       ) AS L
       ON B.tblBaseID = L.tblBaseIDFK
GROUP BY B.tblBaseID, B.Title;

This is the resulting table

enter image description here

Second Option: The advantage is that this option is that it will be working fine with dinamic number of links, the disadvantage is that you need to create a function in one module.

Step1. Create the following function in a module

Public Function createLinksByBase() As String
    On Error Resume Next

    ' Delete resulting table if it exists
    CurrentDb.Execute "DROP TABLE tblLinksByBase"

    ' Variables
    Dim nMaxLinks As Integer
    Dim i As Integer
    Dim sSQL As String

    ' Get max number of links
    sSQL = "SELECT MAX(links) AS max_links " & _
           "FROM  (SELECT  tblBaseIDFK " & _
           "             , COUNT(*) AS links " & _
           "       FROM tblLinks " & _
           "       GROUP BY tblBaseIDFK " & _
           "       ) L"
    nMaxLinks = CurrentDb.OpenRecordset(sSQL)!max_links

    ' Prepare query for generate table
    sSQL = ""
    For i = 1 To nMaxLinks
        sSQL = sSQL & " , MAX(IIF( L.BaseIdRank = " & i & ", L.LinkURL, '')) AS Link" & i & " "
    Next i
    sSQL = "SELECT B.tblBaseID " & _
           "     , B.Title " & _
           sSQL & _
           "INTO   tblLinksByBase " & _
           "FROM   tblBase AS B " & _
           "    LEFT JOIN  " & _
           "       (SELECT L1.tblLinksID " & _
           "             , L1.tblBaseIDFK " & _
           "             , L1.LinkURL " & _
           "             , COUNT(*) AS BaseIdRank " & _
           "        FROM   tblLinks AS L1 " & _
           "            INNER JOIN " & _
           "               tblLinks AS L2 " & _
           "               ON  L1.tblLinksID >= L2.tblLinksID " & _
           "               AND L1.tblBaseIDFK = L2.tblBaseIDFK " & _
           "        GROUP BY L1.tblLinksID " & _
           "             , L1.tblBaseIDFK " & _
           "             , L1.LinkURL " & _
           "       ) AS L " & _
           "       ON B.tblBaseID = L.tblBaseIDFK " & _
           "GROUP BY B.tblBaseID, B.Title"

    ' Create resulting table
    CurrentDb.Execute (sSQL)

    createLinksByBase = "Ready"
End Function

Step 2: Call the function, for example, you can create a view with this

 SELECT createLinksByBase() AS status

Every time that you run the query, a table "tblLinksByBase" will be created / refreshed

enter image description here

I hope this options helps you

Is it possible to transpose data using a query?

1 Answers1