creating single record from multiple ones

[James Poole]

I have a data source with the format:



Names Feature 1 Feature 2 Feature 3 ...

bob 1
bob -
bob 0
Linda 0
Linda 1
Linda -
Mary -
Mary 1
Mary 1
...

What I am trying to create is:
Names Feature 1 Feature 2 Feature 3 ...

bob 1 - 0
Linda 0 1 -
Mary - 1 1
...


Can anyone point me in the right direction?

Thanks

James

[Ron Weiner]

Post by James Poole
Names Feature 1 Feature 2 Feature 3 ...
bob 1
bob -
bob 0
Linda 0
Linda 1
Linda -
Mary -
Mary 1
Mary 1
...
Names Feature 1 Feature 2 Feature 3 ...
bob 1 - 0
Linda 0 1 -
Mary - 1 1
...
Can anyone point me in the right direction?
Thanks
James

OK, I'll give it a whirl!

If you had a table named tblJPoole with the following columns:

UserName, Feature1, Feature2, Feature3

And it had the following Values:

UserName Feature1 Feature2 Feature3
Bob 1
Bob -
Bob 0
Linda 0
Linda 1
Linda -
Mary -
Mary 1
Mary 1

Then this query will do what you want:

TRANSFORM Min(theData.TheValue) AS MinOfTheValue
SELECT theData.UserName
FROM (SELECT tblJPoole.UserName, tblJPoole.Feature1 As TheValue,
'Feature1' AS TheFeature
FROM tblJPoole
WHERE (((tblJPoole.Feature1) Is Not Null))
Union
SELECT tblJPoole.UserName, tblJPoole.Feature2 As TheValue, 'Feature2'
AS TheFeature
FROM tblJPoole
WHERE (((tblJPoole.Feature2) Is Not Null))
Union
SELECT tblJPoole.UserName, tblJPoole.Feature3 As TheValue, 'Feature3'
AS TheFeature
FROM tblJPoole
WHERE (((tblJPoole.Feature3) Is Not Null))
) AS theData
GROUP BY theData.UserName
ORDER BY theData.UserName
PIVOT theData.TheFeature;

I got this:
UserName Feature1 Feature2 Feature3
Bob 1 - 0
Linda 0 1 -
Mary - 1 1

Rdub