Prev: How to adjust physical lengths of fields
Next: Load query with outdata until search
From: Blondee on 14 Apr 2010 16:26
I've created a crosstab from a table with data laid out like this:
Group Section Title
A One John - Manager
A One Joe - Analyst
A Two Jane - Director
B Three Sue - Director
B One Bill - Manager
C Two Bob - Analyst
C Two Tom - Director
C Three Jim - President
D One Linda - Analyst
D Two Lucy - Manager

I'd like to end up with a cross tab like this (you may have to use your
imagination on layout since couldn't paste a picture) basically trying to get
multiple items to display in the value field.
Group One Two Three
A John - Manager
Joe - Analyst Jane - Director
B Bill - Manager
Sue - Director
C Bob - Analyst
Tom - Director
Jim - President
D Linda - Analyst Lucy - Manager

In my data above, I have two people in Group A and Section One.
I'm able to get the first item to appear using First, but can't get all of
them to appear. Is this possible?
Thanks for any assistance.

From: KARL DEWEY on 14 Apr 2010 17:55
Try this --
TRANSFORM First(TableA.[Title]) AS FirstOfTitle
SELECT TableA.[Group], [Group] & [Title] AS Expr1
FROM TableA
GROUP BY TableA.[Group], [Group] & [Title]
PIVOT TableA.[Section] IN("One", "Two", "Three");

--
Build a little, test a little.


"Blondee" wrote:

> I've created a crosstab from a table with data laid out like this:
> Group Section Title
> A One John - Manager
> A One Joe - Analyst
> A Two Jane - Director
> B Three Sue - Director
> B One Bill - Manager
> C Two Bob - Analyst
> C Two Tom - Director
> C Three Jim - President
> D One Linda - Analyst
> D Two Lucy - Manager
>
> I'd like to end up with a cross tab like this (you may have to use your
> imagination on layout since couldn't paste a picture) basically trying to get
> multiple items to display in the value field.
> Group One Two Three
> A John - Manager
> Joe - Analyst Jane - Director
> B Bill - Manager
> Sue - Director
> C Bob - Analyst
> Tom - Director
> Jim - President
> D Linda - Analyst Lucy - Manager
>
> In my data above, I have two people in Group A and Section One.
> I'm able to get the first item to appear using First, but can't get all of
> them to appear. Is this possible?
> Thanks for any assistance.
>
From: Blondee on 15 Apr 2010 10:55
Thanks - I'll give it a try.

"KARL DEWEY" wrote:

> Try this --
> TRANSFORM First(TableA.[Title]) AS FirstOfTitle
> SELECT TableA.[Group], [Group] & [Title] AS Expr1
> FROM TableA
> GROUP BY TableA.[Group], [Group] & [Title]
> PIVOT TableA.[Section] IN("One", "Two", "Three");
>
> --
> Build a little, test a little.
>
>
> "Blondee" wrote:
>
> > I've created a crosstab from a table with data laid out like this:
> > Group Section Title
> > A One John - Manager
> > A One Joe - Analyst
> > A Two Jane - Director
> > B Three Sue - Director
> > B One Bill - Manager
> > C Two Bob - Analyst
> > C Two Tom - Director
> > C Three Jim - President
> > D One Linda - Analyst
> > D Two Lucy - Manager
> >
> > I'd like to end up with a cross tab like this (you may have to use your
> > imagination on layout since couldn't paste a picture) basically trying to get
> > multiple items to display in the value field.
> > Group One Two Three
> > A John - Manager
> > Joe - Analyst Jane - Director
> > B Bill - Manager
> > Sue - Director
> > C Bob - Analyst
> > Tom - Director
> > Jim - President
> > D Linda - Analyst Lucy - Manager
> >
> > In my data above, I have two people in Group A and Section One.
> > I'm able to get the first item to appear using First, but can't get all of
> > them to appear. Is this possible?
> > Thanks for any assistance.
> >
 | 
Pages: 1
Prev: How to adjust physical lengths of fields
Next: Load query with outdata until search