SQL Server 2008 - 从另一个表动态创建表

Question

我有一个如下的表格：

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

我想创建一些矩阵或数据透视表，以便最终得到这样的结果：

NAME  | 01/01/2012 | 01/02/2012 | 01/03/2012
John  | bit        | bit        | bit
Mary  | bit        | bit        | bit
James | bit        | bit        | bit

我看过一些示例，这些示例仅有少量的列项（例如香蕉、苹果、橙子），而我需要一个不确定数量的名称和日期（所以不能硬编码列名）。我考虑将其拆分为多个表，但我总是需要动态创建日期列或名称列。

有人能提供帮助吗？

- AlwaysLearning

---

谢谢Bluefeet整理，我在这里是个新手，所以不能发布图片:D - AlwaysLearning

Answer 1

也许是这样：

测试数据

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)

查找唯一列

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,'')

声明和执行动态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)

清理自己的痕迹

DROP TABLE Table1

结果

Name        01/01/2012    02/01/2012     03/01/2012
James       1             1              0
John        1             0              1
Mary        0             1              0

- Arion

---

感谢大家的帮助，我真的很感激你们抽出时间和精力来简洁地解决我的问题。祝你们有美好的一天，就像我一样。 - AlwaysLearning

Answer 2

如果您只有几列数据，可以使用静态透视表：

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

但是听起来你想要一个动态的透视表。为了做到这一点，你必须首先获取要透视的列的列表，然后运行你的查询：

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)

以下是关于动态透视表的一些有用链接：

SQL Server中使用动态列的透视表

在未知列数的情况下使用SQL Server 2005/2008透视表（动态透视表）

在SO上有很多关于动态透视表的问题/答案。

- Taryn