473,320 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query distinct and concat

Hi,

I have the following table called "tests" :
id WeekNbr name test hours
--------------------------------------------------------------------------
1 2007/26 John "testA" 5
2 2007/26 John "testB" 6
3 2007/26 David "testA" 3
4 2007/28 David "testC" 2
5 2007/30 Victor "testD" 1

I want to write a query so that I have as a result one row per person
and per week, as followed

WeekNbr name test hours
--------------------------------------------------------------------------------
2007/26 John "testA, testB" 11
2007/26 David "testA" 3
2007/28 David "testC" 2
2007/30 Victor "testD" 1

This means that I need to concatenate the values of the test column if
in the same week and same person.
For now, I have only managed to do the job without the test
concatenation the following way:

SELECT DISTINCT WeekNbr, name, SUM(hours) as [Total of hours]
FROM tests
GROUP BY WeekNbr, name

and I get the following:
WeekNbr name hours
-------------------------------------------------------
2007/26 John 11
2007/26 David 3
2007/28 David 2
2007/30 Victor 1
Anyone could help me please?

Thanks so much in advance

Pierrot
Jan 30 '08 #1
1 3598
fo******@gmail.com wrote:
Hi,

I have the following table called "tests" :
id WeekNbr name test hours
--------------------------------------------------------------------------
1 2007/26 John "testA" 5
2 2007/26 John "testB" 6
3 2007/26 David "testA" 3
4 2007/28 David "testC" 2
5 2007/30 Victor "testD" 1

I want to write a query so that I have as a result one row per person
and per week, as followed

WeekNbr name test hours
--------------------------------------------------------------------------------
2007/26 John "testA, testB" 11
2007/26 David "testA" 3
2007/28 David "testC" 2
2007/30 Victor "testD" 1

This means that I need to concatenate the values of the test column if
in the same week and same person.
For now, I have only managed to do the job without the test
concatenation the following way:

SELECT DISTINCT WeekNbr, name, SUM(hours) as [Total of hours]
FROM tests
GROUP BY WeekNbr, name

and I get the following:
WeekNbr name hours
-------------------------------------------------------
2007/26 John 11
2007/26 David 3
2007/28 David 2
2007/30 Victor 1
Anyone could help me please?

Thanks so much in advance

Pierrot
I would write a function. In the query builder create a column like
ConcatTestStr : ConcatStuff([ID])

Now create a public function that selects the field test for the id and
loop thru the recordset concatenating field Test.
Public Function ConcatStuff(lngIDID As Long) As String
strSQL = "select ..."
Do while not rst.Eof
ConcatStuff = ConcatStuff & rst!Test
Loop
End Function

Clean Shirt
http://www.youtube.com/watch?v=LbcIF1J_jnI
Jan 30 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: ben | last post by:
This is a PHP / MySQL kind of question. I am making a script which simply pulls information from a database and displays it on screen. BUT there will be entries where fields could be the same, and...
0
by: Karam Chand | last post by:
Greetings I have a table with the following table structure - mysql> desc email_table; +---------+---------------+-------------------+------+-----+---------+-------+ | Field | Type ...
0
by: B | last post by:
Hello I have written the below query: select CONCAT_WS(" ",m.c_salutation, UPPER(left(m.c_firstname,1)),'&', f.c_salutation, UPPER(left(f.c_firstname,1)), f.c_lastname), ad1.ad_address1,...
4
by: kristofera | last post by:
I am trying to do a distinct grouping of some nodes sorted by a numeric value but for some reason the distinct (preceding-sibling filter) is applied to the result as if not sorted. If I don't use...
1
by: Eugen | last post by:
Hi All! I need your help. I have XML with structure like this: <dataset> <node><category>C1</category><desc>D1</desc></node> <node><category>C2</category><desc>D2</desc></node>...
7
by: Riley DeWiley | last post by:
I am continually amazed by SQL's ability to humble me .... I have a toy query into a toy database that looks just like this: SELECT . FROM f, fw, w WHERE f.id = fw.fid and fw.wid = w.id and...
4
by: Pasquale | last post by:
Is the problem with the query below in the SELECT?? Should I be using PHP to do some of things I am trying to get the SELECT to do? What is happening is it opens 3 or 4 of the same query/process...
1
by: Chris | last post by:
I have a problem with grouping elements in my XSL. What I want to do is select all of the distinct SCE_CGPC records, then by the FSG_SNAM records for each SCE_CGPC and then again by MOA_NAME for...
2
by: cubix | last post by:
I'm trying to run a query in MySQL Query Browser that will concat 3 fields together to form a single date format. This part I don't have a problem with: SELECT CONCAT(`Field12`, '/', `Field13`,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.