SQL help !!!

  • Thread starter Thread starter Walter
  • Start date Start date
W

Walter

Hi everyone,

Please help to find the solution:
I have the table with the folowing data:

id | value
--------
1 | 4
1 | 3
1 | 1
1 | 2
2 | 4
2 | 3
2 | 1

etc

how to get results in this format, so VALUE data is
concatenated.
desired results:
ID | value
-------------
1 | 4 3 1 2
2 | 4 3 1
etc

Thank you so much,

W
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This function isn't built into JET's SQL. Therefore, you'll have to use
a VBA function. Here is a site that has an example of that VBA
function:

Return a concatenated list of sub-record values:

http://www.mvps.org/access/modules/mdl0004.htm

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIgnsYechKqOuFEgEQJyiACghF3qBV0f3WZF1Yr2JwgfWU4VNr8AoOmr
DwVrwxKM8TBOnUVpFBIcQYm1
=D9C9
-----END PGP SIGNATURE-----
 
Thank you very much , that what I needed!

I also wonder how to separate those value into different
columns:
table with two columns (values in "value" column are
separated by |)
id # value
--------------------------
1 # test1 | test2 | test3
2 # test1 | test2 | test3

desired result: from two columns to 4 columns table
id | value1 | value2 | value3
------------------------------
1 test1 test2 test3
2 test1 test2 test3

Thanks a lot,

W
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not sure what you're trying to do. The solution I gave to your
original post put the values into a delimited list in one column, which
is what you asked for. Now you want to break those values into separate
columns again. Why?

That asked . . .

You can create a cross-tab (aka Pivot Table) on the original data.
Something like this:

TRANSFORM Sum(Value) AS daValue
SELECT ID
FROM TableName
GROUP BY ID
PIVOT "Value" & Value

I asked Why, above, because that will indicate how to use the TRANSFORM
clause: Sum, Avg, Count, or some other expression.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQImFh4echKqOuFEgEQLnIwCdETwS7lAwYC2C6nMt593OKBF5/W4AoMdm
7YRQe2LLT/5Ln2omVF4Y+iSK
=NBXs
-----END PGP SIGNATURE-----
 
What I am trying to accomplish is to create a column for a
particular part of a text string as represents a unique
value. First it was this:

id| value
-----------
1 2
1 3
1 1
1 4

then it was this
id | value
--------------
1 2-3-1-4

I wonder how to do this (that was my first goal):
id | value1 | value2 | value3| value4
--------------------------------------
1 2 3 1 4
etc

Always tons of thanks,

W
 
Back
Top