SQL Server 2008 - dynamically create a table from another table
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 kind of matrix or rotation shape so that in the end:
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 several pivot examples that contain a small number of column items (e.g. Banana, Apple, Orange). I need to have an indefinite number of names and an indefinite number of dates (so, no hard-coded column names). I was thinking about splitting into multiple tables, but I always need to dynamically create date columns or name columns.
Can anyone please help?
source to share
Perhaps 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 execute 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 yourself
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
source to share
If you only have a few columns, you can use a static bar:
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 to create a dynamic core. To do this, you must first get the list of columns to rotate and then execute 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 useful links to dynamic points:
Rotations with Dynamic Columns in SQL Server
Using SQL Server 2005/2008 Pivot on Unknown Number of Columns (Dynamic Pivot)
there are many questions / answers on SO for dynamic supports.
source to share