mHelpMe mHelpMe - 10 days ago 5
SQL Question

query table to reorganise data

I have the following table, tblCPDates

cDate date
cp nvarchar(10)


Example of the data

cDate cp
2016-01-01 AB
2016-01-01 MN
2016-02-01 EF
2016-03-01 AB
2016-04-01 MN


What I would like

cDate AB MN EF
2016-01-01 1 1 0
2016-02-01 0 0 1
2016-03-01 1 0 0
2016-04-01 0 1 0


Is this possible?

I tried the following but obviously only return the last date

select * from
(
select distinct cDate, cp from tblCPDDates
)source pivot(max(cDate) for cp in ([AB], [MN], [EF])) as pvt

Answer

You could do this with an aggregated CASE;

Sample Data

CREATE TABLE #tblCPDates (cDate date, cp nvarchar(10))
INSERT INTO #tblCPDates (cDate, cp)
VALUES
 ('2016-01-01','AB')
,('2016-01-01','MN')
,('2016-02-01','EF')
,('2016-03-01','AB')
,('2016-04-01','MN')

Query

SELECT
     cDate
    ,SUM(CASE WHEN cp = 'AB' THEN 1 ELSE 0 END) AB
    ,SUM(CASE WHEN cp = 'MN' THEN 1 ELSE 0 END) MN
    ,SUM(CASE WHEN cp = 'EF' THEN 1 ELSE 0 END) EF
FROM #tblCPDates
GROUP BY cDate

Output

cDate       AB  MN  EF
2016-01-01  1   1   0
2016-02-01  0   0   1
2016-03-01  1   0   0
2016-04-01  0   1   0
Comments