Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

i need to select grouped, but with the differing rows in group to be displayed as a semicolon or comma separated array string.

hi therE,
i am selecting from the current two tables in MySQL 5.0.91-community:
TABLE 3_serviceattributes:
bigint_AttributeID 	text_AttributeDescription 	text_AttributeValue 	bigint_AttributeServiceID
1			Make				FORD			1
12			Model				AVALANCHE		1
40			Year				1997-2004		1
66			CabSize				REGULARCAB		1

TABLE 36_serviceattributegroups:
bigint_OwnerKeyID 	text_AttributeGroupDescription 	bigint_AttributeIncludedID
1			No Parents			0
12			Single Parent			1
40			Double Parents			1
40			Double Parents			12
66			Triple Parents			1
66			Triple Parents			12
66			Triple Parents			40

Open in new window

currently the sql query is as follows:
SELECT 
	36_serviceattributegroups.text_AttributeGroupDescription, 
	36_serviceattributegroups.bigint_OwnerKeyID, 
	3_serviceattributes.text_AttributeDescription, 
	36_serviceattributegroups.bigint_AttributeIncludedID, 
	IF(
		36_serviceattributegroups.bigint_AttributeIncludedID, 
		(SELECT text_AttributeDescription FROM 3_serviceattributes WHERE bigint_AttributeID = 36_serviceattributegroups.bigint_AttributeIncludedID), 
		""
	) AS text_AttributeParentDescription, 
	3_serviceattributes.bigint_AttributeServiceID 
FROM 
	3_serviceattributes, 
	36_serviceattributegroups 
WHERE 
	3_serviceattributes.bigint_AttributeServiceID = 1 AND 
	3_serviceattributes.bigint_AttributeID = 36_serviceattributegroups.bigint_OwnerKeyID;

Open in new window

this currently selects from the tables as follows:
text_AttributeGroupDescription	bigint_OwnerKeyID	
No Parents			1
Single Parent			12
Double Parents			40
Double Parents			40
Triple Parents			66
Triple Parents			66
Triple Parents			66

text_AttributeDescription	bigint_AttributeIncludedID
Make				0
Model				1
Year				1
Year				12
CabSize				1
CabSize				12
CabSize				40

text_AttributeParentDescription	bigint_AttributeServiceID
NULL				1
Make				1
Make				1
Model				1
Make				1
Model				1
Year				1

Open in new window

now i am wondering if it is at all possible to select grouping by bigint_OwnerKeyID or text_AttributeGroupDescription, the multiple differing values for bigint_AttributeIncludedID and text_AttributeParentDescription being imploded into a string array seperated by commas (,) or semicolons (;).
and if it is possible - how in THE hell would i get it right??? so as to only have 4 rows in the selection as follows:
text_AttributeGroupDescription	bigint_OwnerKeyID	
No Parents			1
Single Parent			12
Double Parents			40
Triple Parents			66

text_AttributeDescription	bigint_AttributeIncludedID
Make				0
Model				1
Year				1, 12
CabSize				1, 12, 40

text_AttributeParentDescription	bigint_AttributeServiceID
NULL				1
Make				1
Make, Model			1
Make, Model, Year		1

Open in new window

This has been giving me a headache all day - but it would be best if i could perform the select to output the last result mentioned - as i need to resolve this issue in a mysql select if at all possible. doing it in php would be too easy, and would be overkill afaik, especially keeping in mind possible future changes.
0
intellisource
Asked:
intellisource
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for the second part, you shall check out the GROUP_CONCAT() function...
0
 
intellisourceAuthor Commented:
thanks a million, angellll!!! XD
i was never even aware of this function - so let alone how to spell it :P heh
0
 
intellisourceAuthor Commented:
ummm... stuck a bit - i do not understand how to use the GROUP_CONCAT function in the context i need to perform the crosstable select in. please refer to this post.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now