Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    15

    Smile Unanswered: Display_multiple_rows_on_1_line

    I have the following query that group by 2 variables (ac#, typ) and gives a count for each typ;


    select a.ac# , a.TYP, count(a.TYP)
    from ACCTSTATISTICHIST a
    where a.TYP LIKE 'PD%'
    and a.ac# in (123, 456)
    group by a.ac#, a.TYP
    order by a.ac#;

    results:

    ac# typ count(typ)
    123 PD 1
    123 PD12 1
    123 PD30 1
    123 PD60 1
    123 PD90 1
    456 PD 15
    456 PD30 8
    456 PD60 1


    However, I want all the rows for ac# 123 and 456 on one row with
    the following result:

    ac# PD PD12 PD30 PD60 PD90
    123 1 1 1 1 1
    456 15 0 8 1 0

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    search for "pivot" or "row to column"
    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
    Sep 2011
    Posts
    75
    Hello,

    Use pivot statement of sql server it will easily do it

Posting Permissions

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