Convert row values to columns in Access SQL query

Question

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;

http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server Try that. — Duston, Oct 15 '15 at 17:06
Thanks Duston, My qry is in Access Database, that solution won't work for me. — Birinder, Oct 15 '15 at 18:21

score 2 · Answer 1 · edited May 23 '17 at 11:58

2

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"

edited May 23 '17 at 11:58

Community

1
1

answered Oct 15 '15 at 20:09

Juan Carlos Oropeza

47,252
12
78
118

This is what i have so far Juan.SELECT DA.idea_code, DA.dependency_name, COUNT(*) AS row_number FROM tbl_IdeasDependencies AS DA INNER JOIN tbl_IdeasDependencies AS DA2 ON (DA2.idea_code = DA.idea_code) AND (DA2.dependency_name <= DA.dependency_name) WHERE DA.Load_Date = (Select Max(Load_Date) from tbl_IdeasDependencies) GROUP BY DA.idea_code, DA.dependency_name ORDER BY 1, 3; – Birinder Oct 15 '15 at 20:48
did you made step 1 and 2 in my answer? I cant make the whole code for you because i dont have all the data. I will answer any question you have to make it work. – Juan Carlos Oropeza Oct 15 '15 at 20:50
Thanks Juan Carlos Oropeza I was able to fix it.And your solution works great. – Birinder Oct 19 '15 at 14:50
@Birinder Please remember upvote and accept as the correct answer – Juan Carlos Oropeza Oct 19 '15 at 15:50

Convert row values to columns in Access SQL query

1 Answers1