SQL Query to un-normalize data

Access 2003
Table
Name, Color
David, blue
David, red
David, yellow
Mike, red
Mike yellow
Mary, red

How do I make a query that will

Name, Color1, Color2, Color3, Color4
David, Blue, red, yellow
Mike, Red, Yellow
Mary, Red

This would be ok too
Name, Color1, Color2, Color3, Color4
David, Blue, red, yellow
Mike,, Red, Yellow
Mary, ,Red,

davidpmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
use left outer join...

select a.name,a.color,b.color,c.color,d.color
from table as a
left outer join table as b
on a.name=b.name
and b.color>a.color
left outer join table as c
on a.name=c.name
and c.color>b.color
left outer join table as d
on a.name=d.name
and d.color>c.color
order by 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
we can use a crosstab query here.
from your data create a query eg.,

SELECT TableA.Name, TableA.Color, TableA.Color AS Col
FROM TableA;
save as Q2

*note
  we need the third column for the crosstab query

now create a crosstab query using Q2

TRANSFORM Last(Q2.Col) AS LastOfCol
SELECT Q2.Name
FROM Q2
GROUP BY Q2.Name
PIVOT IIF(Q2.Color="Red","Color1",IIF(Q2.Color="Blue","Color2" ,IIF(Q2.Color="Yellow","Color3","Color4")));

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.