Subquery & Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with the following fields

(ID, Role, Resource)

All three fields can have repeating values.

ex:
1|Manager| John M
1| Advisor | Peter L
1| Reviewer| Samuel T
2| Manager| John M
2| Advisor| Susan P
2| Advisor| Kelly R

I need an ability that if the ID and Role Repeat that I concatenate the
Names together and then express in a PIVOT table.

Eg. (Data Behind Pivot)
1| Manager| John M
1| Advisor | Peter L
1| Reviewer| Samuel T
2| Manager| John M
2| Advisor| Susan P, Kelly R

Manager Advisor Reviewer
1 John M Peter L Samuel T
2 John M Susan P, Kelly R

Any assistance would be greatfully accepted!

Jeff
 
Hi,


Start with an empty table Temp, three fields: id, Role, concat. The last
field is varchar(255) and allow nulls

INSERT INTO Temp(id, role) SELECT DISTINCT id, role FROM myTable

will fill it with the different (id, role) we can encounter in myTable


UPDATE Temp INNER JOIN myTable
ON Temp.id=myTable.id AND Temp.role = myTable.role
SET Temp.concat = (Temp.Contat + ", ") & myTable.resource


would make the table Temp as you want it. It will be a matter to run the
query Crosstab wizard to get the final result.



Hoping it may help,
Vanderghast, Access MVP
 
Thank you Michel, normally I think this would work, but unfortunately my
database permissions don't let me create tables, and our portability policies
don't let me do this work via VBA functions. (Data is actually on an SQL
server with linked tables) Is there a way to do the whole thing via a query?

Any other thoughts??
 
Hi,

You may be able to create the temp table in the Front End part of a spitted
application (Front end - Back End) so the restriction as not being able to
create a table, in the backend part, does not bother your, since you are in
the front end part! Is it possible solution?


An alternative would probably be to concatenate the strings within a stored
proc, on the server. I think Joe Celko supplied a solution in one of his
book, but sure, other solutions have probably been proposed too, for MS SQL
Server, specifically, if that is a possible alternative for you to have a
sproc on the SQL server.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top