SQL combine into one line?

Posted on 2011-10-12
Last Modified: 2012-05-12
hi - -I have single table with following fields
id, code, code_type, amount
each id can have 1 - n codes over time but at the moment no id will have more than 20 codes
most of the codes will fall into a single code_type and sum(amount) can be grouped for all those to lessen the number of instances of code displayed - however this is only true for CT_1 in the below example - -  CT_2 and CT_3 each need to show separate sum(amount)
i have listed only one Id  but I think it is enough
where data looks like this (in the order of ID, CODE, CODE_TYPE, AMOUNT
123    CODEA    CT_1    50
123    CODEA    CT_1    52
123    CODEA    CT_1    44
123    CODEB     CT_1   20
123    CODEB     CT_1   33
123    CODEC     CT_1   10
123    CODEC     CT_1   15
123    CODEC     CT_1   33
123    CODED     CT_2    20
123    CODED     CT_2    21
123    CODEE      CT_2   60
123    CODEF     CT_3    20
123    CODEF     CT_3    24
123    CODEF     CT_3    28
123    CODEG    CT_3     34
123    CODEG    CT_3     77

i need output to look like this:

123 CODEA   146    CODEB   53   CODEC  58   CODED  41  CODEE 60  CODEF 44  CODEG 111

OR if we can combine all the CT_1 into a single sum like this:

123  HISTORY  257   CODED   41    CODEE  60    CODEF  44   CODEG  111

I could have sworn I did something similar several years ago, and could take it into Access or even excel but I really would like to do it in SQL.

Question by:COBOLforever

    Author Comment

    I have to run to a long testing session at noon so if you can please be patient with my responses after that...
    LVL 73

    Expert Comment

    you can't have dynamic columns in sql.  
    The number of columns to be returned must be known at the time the sql statement is parsed.

    If you'll never have more than 5 codes (including history) then we can do it, but if it'll vary then no
    LVL 73

    Accepted Solution

    assuming your codes are fixed,  you can try something like this...

    if you need additional codes, it should be easy to see the pattern to extend it

    select id,
    'HISTORY',sum(case when code ='HISTORY' then amount end) history_amount,
    'CODED',sum(case when code ='CODED' then amount end) coded_amount,
    'CODEE',sum(case when code ='CODEE' then amount end) codee_amount,
    'CODEF',sum(case when code ='CODEF' then amount end) codef_amount,
    'CODEG',sum(case when code ='CODEG' then amount end) codeg_amount
    (select id, case when code_type = 'CT_1' then 'HISTORY' else code end code, amount from yourtable)
    group by id

    Author Comment

    I get what you're doing with most of this but what is the syntax at the end of each statement where it ends in   _amount,    doing?

    I am getting a FROM keyword not found where expected on line 3. Here is my actual code:
    select id,
    'CONVERSION', sum(case when a_term < '1100' then item_amt end) Conversion_Bal,
    '1102', sum(case when a_term = '1102' then item_amt end) 1102 Bal,
    '1106', sum(case when a_term = '1106' then item_amt end) 1106 Bal,
    '1109', sum(case when a_term = '1109' then item_amt end) 1109 Bal,
    '1112', sum(case when a_term = '1112' then item_amt end) 1112 Bal,
    '1116', sum(case when a_term = '1116' then item_amt end) 1116 Bal,
    '1119', sum(case when a_term = '1119' then item_amt end) 1119 Bal,
    '1122', sum(case when a_term = '1122' then item_amt end) 1122 Bal,
    '1126', sum(case when a_term = '1126' then item_amt end) 1126 Bal,
    '1129', sum(case when a_term = '1129' then item_amt end) 1129 Bal
    from (select id, case when a_term < '1100' then 'CONVERSION' else a_term end a_term, item_amt from my_items)
    group by id
    LVL 73

    Expert Comment


    those are just aliases to give the column a name.  Otherwise "sum(case when code ='CODED' then amount end) " will be turned into a name which isn't very helpful.

    This...   1102 Bal  

    is an illegal column name,  you can enclose it in double quotes to use it though

    '1102', sum(case when a_term = '1102' then item_amt end) "1102 Bal",

    Author Comment

    sort of what I thought but when I tried to enclose them in single quotes - no go of course.

    works like a charm - - any way to get rid of the (null) when there is no amount in a particular cell?

    Thanks greatly.

    Author Comment

    Ah shoot - - it is not providing a sum when a_term < '1100' - it just provides (null). I verified that one of teh id's had a_term records < '1100' and in second SQL can sum those A_terms for them.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now