SQL Server 2008 - Create a table dynamically from another table

pivot, sql

Solution

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

Problem

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?

Original source