I have a table like this:
Column = NAME
Column = DATE
NAME | DATE | Y/N
John | 01/01/2012 | bit
Mary | 01/01/2012 | bit
James | 01/01/2012 | bit
John | 01/02/2012 | bit
Mary | 01/02/2012 | bit
James | 01/02/2012 | bit
John | 01/03/2012 | bit
Mary | 01/03/2012 | bit
James | 01/03/2012 | bit
I want to create some form of matrix or pivot so I end up with this:
NAME | 01/01/2012 | 01/02/2012 | 01/03/2012
John | bit | bit | bit
Mary | bit | bit | bit
James | bit | bit | bit
I have seen some pivot examples that have a small amount of column items (like Banana, Apple, Orange) I need to have an indeterminate number of names and an indeterminate number of dates (so, no hard-coded column names). I was thinking of splitting into multiple tables but I will always need to dynamically create either date columns or name columns.
Can anyone help?
Maybe something like this:
Test data
CREATE TABLE Table1
(
NAME VARCHAR(100),
[DATE] DATE,
[Y/N] BIT
)
INSERT INTO Table1
VALUES
('John','01/01/2012',1),
('Mary','01/01/2012',0),
('James','01/01/2012',1),
('John','01/02/2012',0),
('Mary','01/02/2012',1),
('James','01/02/2012',1),
('John','01/03/2012',1),
('Mary','01/03/2012',0),
('James','01/03/2012',0)
Finding unique columns
DECLARE @cols VARCHAR(MAX)
;WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY [DATE] ORDER BY [DATE]) AS RowNbr,
convert(varchar, [DATE], 103) AS [Date]
FROM
Table1
)
SELECT @cols=STUFF
(
(
SELECT
',' +QUOTENAME([Date])
FROM
CTE
WHERE
CTE.RowNbr=1
FOR XML PATH('')
)
,1,1,'')
Declare and executing dynamic sql
DECLARE @query NVARCHAR(4000)=
N'SELECT
*
FROM
(
SELECT
Table1.NAME,
CAST(Table1.[Y/N] AS INT) AS [Y/N],
convert(varchar, Table1.[DATE], 103) AS [Date]
FROM
Table1
) AS p
PIVOT
(
MAX([Y/N])
FOR [Date] IN ('+@cols+')
) AS pvt'
EXECUTE(@query)
Cleaning up after myself
DROP TABLE Table1
Result
Name 01/01/2012 02/01/2012 03/01/2012
James 1 1 0
John 1 0 1
Mary 0 1 0
If you have just a few columns you can use a static pivot:
create table #temp
(
name varchar(50),
date datetime,
yesno bit
)
insert into #temp values('John', '01/01/2012', 1)
insert into #temp values('Mary', '01/01/2012', 1)
insert into #temp values('James', '01/01/2012', 1)
insert into #temp values('John', '01/02/2012', 0)
insert into #temp values('Mary', '01/02/2012', 0)
insert into #temp values('James', '01/02/2012', 0)
insert into #temp values('John', '01/03/2012', 1)
insert into #temp values('Mary', '01/03/2012', 0)
insert into #temp values('James', '01/03/2012', 1)
select name, [01/01/2012], [01/02/2012], [01/03/2012]
from
(
select name, date, cast(yesno as tinyint) as yesno
from #temp
) x
pivot
(
max(yesno)
for date in ([01/01/2012], [01/02/2012], [01/03/2012])
) p
drop table #temp
But it sounds like you want a dynamic pivot for this. To do this you must first get the list of columns to pivot and then run your query:
create table test
(
name varchar(50),
date datetime,
yesno bit
)
insert into test values('John', '01/01/2012', 1)
insert into test values('Mary', '01/01/2012', 1)
insert into test values('James', '01/01/2012', 1)
insert into test values('John', '01/02/2012', 0)
insert into test values('Mary', '01/02/2012', 0)
insert into test values('James', '01/02/2012', 0)
insert into test values('John', '01/03/2012', 1)
insert into test values('Mary', '01/03/2012', 0)
insert into test values('James', '01/03/2012', 1)
DECLARE @cols AS VARCHAR(MAX),
@query AS VARCHAR(MAX);
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + convert(varchar(10), t2.date, 101)
FROM test AS t2
ORDER BY '],[' + convert(varchar(10), t2.date, 101)
FOR XML PATH('')
), 1, 2, '') + ']'
set @query = 'select name, ' + @cols + '
from
(
select name, date, cast(yesno as tinyint) as yesno
from test
) x
pivot
(
max(yesno)
for date in (' + @cols + ')
) p'
execute(@query)
Here are some helpful links on dynamic pivots:
Pivots with Dynamic Columns in SQL Server
Using SQL Server 2005/2008 Pivot on Unknown Number of Columns (Dynamic Pivot)
there are a lot of questions/answers on SO for dynamic pivots.