Prev: Whitepaper on Location Intelligence
Next: Fenced User Id Confusion
From: eliane on 3 Mar 2010 17:27
Hello All,

I have a query that returns the following results. We get multiple
rows back, with all the same info except for the Function.

ID NAME FUNCTION
001 Greg Johnson Administration
001 Greg Johnson Senior IT Specialist
002 Todd Thompson IT Specialist
002 Todd Thompson Project Manager


What shoudl I do to get the following result? Need to get Function
results for each contact within a single row, and separete them with
comma.

ID NAME FUNCTION
001 Greg Johnson Administration, Senior IT Specialist
002 Todd Thompson IT Specialist, Project Manager

Please, can anyone help?
Thanks!
From: Tonkuma on 4 Mar 2010 00:14
Search this group with keyword "XMLGROUP".
From: --CELKO-- on 5 Mar 2010 08:19
Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure? Normal forms are 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 is a more basic programming principle than just
SQL and RDBMS.

Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.
From: Tonkuma on 6 Mar 2010 07:58
> Search this group with keyword "XMLGROUP".
An example:
------------------------------ Commands Entered
------------------------------
WITH
/****************************************
***** Begin of Sample data **********
****************************************/
your_result(ID , NAME , FUNCTION) AS (
VALUES
('001' , 'Greg Johnson' , 'Administration')
, ('001' , 'Greg Johnson' , 'Senior IT Specialist')
, ('002' , 'Todd Thompson' , 'IT Specialist')
, ('002' , 'Todd Thompson' , 'Project Manager')
)
/****************************************
***** End of Sample data **********
****************************************/
SELECT id , name
, SUBSTR(
XMLCAST(
XMLGROUP( ', ' || function AS a )
AS VARCHAR(100) )
, 3 ) AS functions
FROM your_result
GROUP BY
id , name
;
------------------------------------------------------------------------------

ID NAME
FUNCTIONS
--- -------------
----------------------------------------------------------------------------------------------------
001 Greg Johnson Administration, Senior IT
Specialist
002 Todd Thompson IT Specialist, Project
Manager

2 record(s) selected.


 | 
Pages: 1
Prev: Whitepaper on Location Intelligence
Next: Fenced User Id Confusion