CODEWITHSUNDEEP
sqlt-sqlpivot
bphi
bphi

Reputation: 3195

SQL Pivot with subquery

I have a table that looks like this

value | effective_date
----------------------
  'A' | '2000-10-31' 
  'A' | '2000-11-30'
  'B' | '2000-10-31'
  'B' | '2000-11-30'
  'C' | '2001-10-31'
  'C' | '2001-12-31'

How can I pivot the effective_date column to show if a value is present for each date?

Output:

value | 2000-10-31 | 2000-11-30 | 2001-10-31 | 2001-12-31
  'A' |      1     |      1     |      0     |     0
  'B' |      1     |      1     |      0     |     0
  'C' |      0     |      0     |      1     |     1

I select the first table with 

SELECT DISTINCT value, effective_date
FROM table
WHERE condition

And the effective_date column with 

SELECT DISTINCT effective_date
FROM table
ORDER BY effective_date

Upvotes: 0

Views: 943

Answers (1)

Chris Albert
Chris Albert

Reputation: 2507

CREATE TABLE #Test ([value] char(1), effective_date date)

INSERT INTO #Test ([value], effective_date) VALUES
('A', '2000-10-31'), 
('A', '2000-11-30'),
('B', '2000-10-31'),
('B', '2000-11-30'),
('C', '2001-10-31'),
('C', '2001-12-31')

SELECT *
FROM
(
    SELECT [value] AS 'Display', [value], effective_date FROM #Test
) AS SRC
PIVOT
    (
        COUNT([value])
        FOR effective_date
        IN ([2000-10-31], [2000-11-30], [2001-10-31], [2001-12-31])
    ) AS PVT

DROP TABLE #Test

Upvotes: 2

Related Questions