We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Flatten and Concatenate data

rgrimm
rgrimm asked
on
Medium Priority
1,157 Views
Last Modified: 2008-03-17
My goal is to take data that looks like this:
ID  Value
1      A
1      B
2      A
3      D
4      A
4      Z

and make it look like this:

ID  Values
1   A, B
2   A
3   D
4   A, Z

In other words, I want to flatten and concatenate the data for reporting purposes using queries (not code). The first listing is the result of a query.  I can use this query as the recordsource for a crosstab query and get to an intermediate result that looks something like this:

ID  A  B   D   Z
1   A   B
2   A
3            D
4   A           Z

My problem is getting to the final form. If I could predetermine what the ultimate values will be (the values are actually strings, but I am using alphas here for ease of description) the answer would be simple. I could hard-code a calculated field like this:

=IIF(ISNULL(A),"",A) +", " + IIF(ISNULL(B),"",B) +", " + IIF(ISNULL(D),"",D) +", " + IIF(ISNULL(Z),"",Z)

However I don't know what the values will be beforehand, so I need a solution that will work on the fly in Access. Thoughts?


SIDE NOTE:
In SQL Server I can do this using code a SELECT statement like this:

SET @outRoleIDList = ''

SELECT @outRoleIDList = @outRoleIDList + ',' + cast(RoleID as varchar(10))
FROM LoginsRoles
WHERE LoginID = @inLoginID

SET @outRoleIDList = STUFF(@outRoleIDList, 1, 1, '')  --remove leading comma

Comment
Watch Question

Commented:
The final form is exactly the output you get from a crosstable query.

Just place the table in the graphical query editor and place the ID field and the Value field twice.
Change the query type into crosstable and make the ID the rowheader, the first Value field the Column header and the second the value.
Finally change the last Value from GroupBy into Value

Clear ?

Nic;o)
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
TRANSFORM COUNT(*) > 0
SELECT LoginID
FROM LoginsRoles
GROUP BY LoginID
PIVOT RoleID In ("A","B","D","Z")

Author

Commented:
I didn't really get the answer I was hoping for, but it was a failure of Access (the Jet database engine, actually), not the experts!

I was looking for a 100% query-based solution, and it turns out that it can't be done that way. You must use code. The code isn't difficult (see the code link in the Accepted Answer), but I was hoping for a query-based solution so that the end user coud deal with it more easily in the future.

The point that makes a query-based solution not doable is that you cannot pre-determine what all the values that would make up the column headers in the crosstab will be. Each time this query is run it will have a different set of values and number of values that must then be concatenated together into a comma-delimited fashion.

What Access/Jet needs is what SQL Server already has: the ability to create a variable, and then use that variable in a SELECT and keep appending values to it during the looping that naturally occurs in the SELECT process, as the Side Note above shows. This would obviously work in an Access Project (adp file) which uses SQL Server as the backend, but isn't included functionality in the Jet database engine (mdb file).

Commented:
I thought the SQL I posted returned exactly what you're looking for :(

To go from that format to the exact format you're looking for is simply this:

SELECT LoginID, LEFT(IIF(a,"A,","") + IIF(b,"B,","") + IIF(d,"D,","") + IIF(z,"Z,",""), Len(IIF(a,"A,","") + IIF(b,"B,","") + IIF(d,"D,","") + IIF(z,"Z,","")) - 1) FROM Query1

Author

Commented:
The last sentence of my original post stated, "However I don't know what the values will be beforehand, so I need a solution that will work on the fly in Access." I don't know what values I will get, nor the number of values. Therefore I couldn't do as you suggested because I have no idea what to hard code. It needs to be created on the fly, like the SQL Server aside shows.

But thanks for taking the time! I appreciate it your giving it a shot!

The code that is in the Accepted answer link works only if the fields to be concatenated are integers. What should be changed to make it work for all data types?

Author

Commented:
I guess I don't see where the code is only for concatenating integers. It uses the variant datatype, which will handle just about anything. Regardless, if you are having problems try surrounding the the section in the loop that fetches the value from the recordset with a CVar function to convert it to a variant. It would look something like this:

With rs
        If .RecordCount <> 0 Then
            'start concatenating records
            Do While Not rs.EOF
                varConcat = varConcat & CVar(rs(strFldConcat)) & ";"
                .MoveNext
            Loop
        End If
    End With

Hopefully that will work for you. You shouldn't have to do anything with the first part of the code example, since that is just dynamically building an SQL string. Any conversion problems you might experience would happen in the above section which is where the flattening takes place.
okie...got this one done : had to include DAO objects, nothing wrong with the code at all)

one more question:

say my query gives an answer for the tables I've created like this :
id    children    dogs
1     a,b,c        x,y
2     d,e,f        x,xx
3     a,p,q       w,ww

the tables are tusers, tdogs, tchildren and the query is :SELECT fconcatchild("tdogs","id","dog","long",[id]) AS dogs, fconcatchild("tchildren","id","child","long",[id]) AS children
FROM tusers;



say I want all details of the id's who have a dog named x i.e i want the query result as:

id  children  dogs
1  a,b,c       x,y
2  d,e,f       x,xx

how should i modify the above query?

Author

Commented:
I see a few alternative solutions:

* Add a WHERE clause: WHERE dogs LIKE "*x*"
* Use the table resulting from your SELECT fconcatchild query as the foundation for a second query, and then filter the results using the WHERE clause in the first alternative.

The first option should work, as Access' JET database engine will construct a result set with all records, and then use the WHERE clause to filter out all the unwanted records.

The easiest way to construct the second option is to build the query in the Access Query Designer and get the results you want, switch to SQL view, and then copy/paste the SQL into your VB code. Or, if you are like me, just use the query and forget putting it in code.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.