Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    12

    Question Unanswered: Aggregating Data

    I've got a situation where I have data that, due to the design of the table, is scattered across not only multiple rows, but also multiple columns within those rows.

    I've tried to provide an illustrative example below of what I'm working with - it's not exactly the same, but hopefully it'll give you an idea:


    Code:
    account id	location id	prb1	prb2	prb3	prb4	prb5	prb6	prb7	prb8	fix1	fix2	fix3	fix4	fix5	fix6
    ---------------------------------------------------------------------------------------------------------------------------------------------
    123456		1		25	66	75						125	325	145
    123456		2		66	99	125						74	25
    123456		7		74								
    123456		60		19	25							22
    
    desired output:
    
    Account id	problem				fix
    --------------------------------------------------------------
    123456		25,66,75,99,125,74,19	125,325,145,74,25,22
    The primary key on this table is made up of 5 columns, four make up the "account id" and the 5th makes up the "location id". Not sure if that's relevant, but there you have it.

    What I need to do is find some way of consolidating or aggregating all the relevant "prb" and "fix" codes for each instance of the "account id" into one row that I can include on my output.

    I'd prefer not to have duplicate values for the problem or fix on the output, but if there's no way around that then it's no big deal - my users will have to live with it.

    I cannot use any custom PL/SQL functions because there is no writable userspace. I've looked at LISTAGG, but it seems to only work well for a single column on multiple rows, and if there's a way to use it on multiple columns on multiple rows, I've not figured it out.

    I appreciate any suggestions, help, or advice you can give me.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    writing (valid & correct) SQL is easier & better when tables & data actually exist.
    please post CREATE TABLE statement
    please post INSERT statements to provide sample test data
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2012
    Posts
    12
    Quote Originally Posted by anacedent View Post
    writing (valid & correct) SQL is easier & better when tables & data actually exist.
    please post CREATE TABLE statement
    please post INSERT statements to provide sample test data
    I'd love to do that, but I like having a job more.

    What I can do is provide the full table structure - you can randomly fill with whatever data floats your boat, though I've included my original examples and a few more to demonstrate uniqueness.
    Code:
    MARKETID	ADDRESSID	INSTID		ORDERID		LOCATIONID	SERLID		RETRNID		SERLOC		PTS		CHNGPRG		PRB1	PRB2	PRB3	PRB4	PRB5	PRB6	PRB7	PRB8	FIX1	FIX2	FIX3	FIX4	FIX5	FIX6
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Number(5)	Number(6)	Number(2)	Char(1)		Number(1)	Varchar2(14)	Char(1)		Char(1)		Number		Char(1)		Char(3)	Char(3)	Char(3)	Char(3)	Char(3)	Char(3)	Char(3)	Char(3)	Char(3)	Char(3)	Char(3)	Char(3)	Char(3)	Char(3)	
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    123		45		6		A		1		AHGHA256AA			Z		10		A		25	66	75						125	325	145
    123		45		6		A		2		6625FGTA56			F		5		A		66	99	125						74	25
    123		45		6		A		7						H		1		A		74								
    123		45		6		A		60		55257825			S		10		A		19	25							22
    123		45		6		g		1		AHGHA256AA			Z		2		B		1	32	65						425	72
    123		45		6		g		10						Q		3		B		98	33	45						12	
    998		26		12		Z		1						W		1		A		1								12
    998		26		12		Z		11						D		2		A		25	74	26						74	98	66
    552		45		33		V		12						G		1		S		45	47	52						50
    I should note that this is an ancillary table to the main work order table - it's joined based on MarketID, AddressID, InstID and OrderID to the main work order table.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by N.B. View Post
    I'd love to do that, but I like having a job more.
    ... Etc ...
    Please note that what anacedent is requesting is "SAMPLE DATA" and the statements to CREATE the TABLE and INSERT the sample data.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Mar 2012
    Posts
    12
    Quote Originally Posted by LKBrwn_DBA View Post
    Please note that what anacedent is requesting is "SAMPLE DATA" and the statements to CREATE the TABLE and INSERT the sample data.
    Hmmm, I supplied sample data, unless of course the example I provided didn't show up properly - in that case, I'll be happy to dump it in pastebin.

    I also provided a 2nd example including all fields - relevant and not - and what the data type definitions were for each field.

    I don't have the CREATE or INSERT statements because my Oracle access is READ ONLY.

  6. #6
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by N.B. View Post
    Quote Originally Posted by anacedent View Post
    writing (valid & correct) SQL is easier & better when tables & data actually exist.
    please post CREATE TABLE statement
    please post INSERT statements to provide sample test data
    I'd love to do that, but I like having a job more.

    What I can do is provide the full table structure - you can randomly fill with whatever data floats your boat, though I've included my original examples and a few more to demonstrate uniqueness.
    This is a forum of volunteers which provide assistance in their spare time and for free.
    If you are not willing to do your homework and cannot provide simple create table statements with sample data, expecting people in this forum to do your work,
    maybe it would be easier if you hired a professional and payed him for creating this SQL query for you ?

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You could try this which does a union all to merge the columns into one

    i.e.

    Code:
    select 
    ...
    stragg(distinct case when type='PRB' then val end) prb,
    ...
    from
    (
        select 'PRB' type, prb1 val from table...
        union all
        select 'PRB' type, prb2 from table...
        ...
        union all
        select 'FIX' type, fix1 from table...
        ...
    )
    group by...
    In 11G you could use unpivot and listagg to do the same thing.

    Alan

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •