Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bkleen
Starting Member
5 Posts |
 Posted - 2010-02-25 : 14:01:32
|
| I have the following table:Inv#......Vend......Store#12345.....ABC.......99912345.....ABC.......88812345.....ABC.......77723456.....DEF.......66623456.....DEF.......55534567.....GHI.......444I want to create a query that will display the data as follows:Inv#......Vend......Store#'s12345.....ABC.......999,888,77723456.....DEF.......666,55534567.....GHI.......444I need to show one row for each distinct combo of Inv# and Vend and then list each store related to that combo in a separate column. I can use the "select first" and "select last" statements to show the first and last occurrence of stores per combination but not the 2nd, 3rd, etc if there are more than 2. |
|
|
Sachin.Nand
2937 Posts |
|
|
bkleen
Starting Member
5 Posts |
Posted - 2010-02-26 : 08:04:38
|
| I tried the suggested code but got a syntax error. I didn't mention that I'm using Access2007, is that code supported in Access? Also, it appears that the example is for a situation where there is data in 2 different tables, all of my data is in one single table. |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-02-26 : 08:06:59
|
| The link I had sent will work even for single table with a little bit workaround.But xml path is not supported for any version of Access.PBUH |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
bkleen
Starting Member
5 Posts |
Posted - 2010-02-26 : 08:17:09
|
| I assume that the XML Path content is needed for the statement to work so is there a way for me to accomplish this in Access? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-26 : 08:24:52
|
| Are you asking for Access query? then post in Access forum.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
transpose