SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Friday, November 24, 2006

query question...

SQL Apprentice Question
hi, not sure if db2 has a aggregate function to 'sum (concat)' a string
column in a group by?
i have a table like this:
myCategory, mySubCategory, myGroup, mySubGroup, myString
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string1'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string2'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string3'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string4'
......

i want to group CatKye, SubCatKey, GrpKey, SubGrpKey
so, the result set to be like this (in sorted order):
myCategory, mySubCategory, myGroup, mySubGroup, myString
CatKey1, SubCatKey1, GrpKey1, SubGrpKey1, 'string1,string4'
CatKey1, SubCatKey1, GrpKey1, SubGrpKey2, 'string2,string3'


any idea? i heard there is something like ROWCONCAT... didn't find it
in db2 reference though.
thanks a lot.



Celko Answers
What you are looking for exists in Sybase as LIST() and one of the open
source products (Posttgres? I cannot remember). But that is not the
real question. Why do you wish to destroy First Normal Form (1NF) with
a concatendated list structure? It is the foundation of RDBMS, after
all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.


Yes, there are kludges in SQL to do this. You can also still write
procedural code with GOTOs and get "spaghetti code", but it does not
mean you should.

No comments: