CODEWITHSUNDEEP
sqlms-access-2010
Birinder
Birinder

Reputation: 21

Convert row values to columns in Access SQL query

I have two columns in an Access table 

Idea_Code, "Dependency_Name" 
I66666       TEST1
I66666       TEST2
I66666       TEST3
I77777       TEST34

I would like to see the results as: 

Idea_Code  Dependency_Name1  Dependency_Name2  Dependency_Name3 ------ETC
I66666         TEST1               TEST2          TEST3 
I77777         TEST34

One idea code could have many dependency name. In order to kill duplication I need to show dependency names as columns which could be one to many relationships. Thanks for your time on it.

I used this code but it doesn't work the way I would like it to in the access query.

TRANSFORM First(DA.dependency_name) AS dependency_name
SELECT DA.idea_code
FROM tbl_IdeasDependencies AS DA

GROUP BY DA.idea_code

PIVOT DA.dependency_name;

Upvotes: 2

Views: 462

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

First you create a new colum row_number, here is a SAMPLE how

You will have

Idea_Code, "Dependency_Name", row_number
I66666       TEST1                1
I66666       TEST2                2
I66666       TEST3                3
I77777       TEST34               1

Second you create a category column

SELECT 'Dependency_Name' + row_number as category

Now you have

Idea_Code, "Dependency_Name", row_number, category
I66666       TEST1                1       Dependency_Name1
I66666       TEST2                2       Dependency_Name2        
I66666       TEST3                3       Dependency_Name3
I77777       TEST34               1       Dependency_Name1

Last you do the PIVOT using category and values from original "Dependency_Name"

Upvotes: 2

Related Questions