SQl query, subquery formatting issue

Question

I need someone to help me with some formatting. I have tried to get this into a Row using subqueries and UNION ALL etc with no success. It would seem to be rather easy but I am just beating my head against a wall with this. The table is laid out like so with data:

Date    pTypeCode   Location    Amt

1/1/2015    C   Store1  50.21

1/1/2015    C   Store1  125.62

1/1/2015    P   Store 1 250.1

1/1/2015    EB  Store 1 125.01

1/1/2015    C   Store 2 50.25

1/1/2015    C   Store 2 100.25

1/1/2015    EB  Store 2 125.25

1/3/2015    EB  Store 1 35.25

1/3/2015    C   Store 1 35.25

1/3/2015    C   Store 1 35.25

1/3/2015    P   Store 1 35.25

1/3/2015    C   Store 2 85.15

1/3/2015    C   Store 2 65.25

1/3/2015    P   Store 2 65.25

1/3/2015    EB  Store 2 65.25

What I need it to get counts of pTypeCode as columns per row. I can easily get the data I want in a simple query like this:

SELECT Date, LOCATION,
             pTypeCode,
             Count(pTypeCode),
             Sum(Amt) AS Prem
FROM [dbo].[CommisionEntry]
WHERE Date >=
    (SELECT DATEADD(DAY, 1, EOMONTH(GetDate(), -1)))
  AND Date <=
    (SELECT EOMONTH(GetDate()))
GROUP BY date, LOCATION,
               pTypeCode

But I need the count(pTypeCode) = P and C And EB in the row also like so:

Date  Count(pTypeCode) C    Count(pTypeCode) P  Count(pTypeCode) EB    SUM(Amt)

1/1/2015  2  1  1  550.94

Any help would be appreciated.

Thanks

formatting your code with the code block tool would make this post much more readable. — DrCord, May 08 '15 at 17:59
Sounds like you're looking for a `PIVOT`: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server/15745076#15745076 — Paul Griffin, May 08 '15 at 18:06

score 1 · Accepted Answer · answered May 08 '15 at 18:48

1

If you don't have something like pivot available (though you should, since it looks like you're using T-SQL), you can create columns for each value, like so:

select
  date
  ,sum(case pTypeCode when 'C' then 1 else null end) count_c
  ,sum(case pTypeCode when 'P' then 1 else null end) count_p
  ,sum(case pTypeCode when 'EB' then 1 else null end) count_eb
  ,sum(amt) sum_amt
from
  CommisionEntry
where date >=
  dateadd(day, 1, eomonth(GetDate(), -1))
    and date <= eomonth(GetDate())
group by
  date

answered May 08 '15 at 18:48

Jeremy Fortune

2,459
1
18
21

I will try this. I am also looking into PIVOT. I am not as strong in straight SQL as I am in c#, but I am learning. – jeff piper May 08 '15 at 19:04
Looks like this will work great. I knew it was easy for someone that knew what they was doing – jeff piper May 08 '15 at 20:47

score 0 · Answer 2 · answered May 08 '15 at 19:28

select  distinct a.date,
   a.Location,
   b.C,
   c.EB,
   d.p,
   e.amt

   from (select * from test1 group by date,location) as A
   left outer join
        (select date,count(*) as C, location from test1 where pTypeCode = "C" group by date,location) as b
   on a.date = b.date
   and a.location = b.location     
   left outer join
        (select date,count(*) as EB, location from test1 where pTypeCode = "EB" group by date,location) as c
   on a.date = c.date
   and a.location = c.location    
   left outer join
        (select date,count(*) as P, location from test1 where pTypeCode = "P" group by date,location) as d
   on a.date = d.date
   and a.location = d.location     
   left outer join
        (select date,location,sum(Amt) as Amt, location from test1 group by date, location) as e
   on a.date = e.date
   and a.location = e.location
   order by a.date,a.location;

SQl query, subquery formatting issue

2 Answers2