操纵表结构(Manipulating table structure)

我有一个完整的数据表，我期待把内容从一行并把它添加到列标题，然后把它下面相应的值在该列！ 我试图描述的问题最好通过如下表所示：

普通表

一旦该表已创建（这是一个临时表）我要重建表看起来像这样

但我找遍上线和我似乎无法找出如何做到这一点的任何地方！ 我会的帮助非常感谢！

Answer 1:

尝试这个

CREATE TABLE #table1
  (
     sourceid  INT,
     name      VARCHAR(50),
     value     VARCHAR(50),
     timestamp NUMERIC(10, 2)
  )

INSERT INTO #table1
VALUES      (1,
             'Mark',
             '99%',
             9.00),
            (1,
             'Mark',
             '75%',
             9.30),
            (1,
             'Mark',
             '60%',
             10.00),
            (1,
             'Mark',
             '85%',
             10.30),
            (2,
             'John',
             '50%',
             9.00),
            (2,
             'John',
             '30%',
             9.30),
            (2,
             'John',
             '65%',
             10.00),
            (2,
             'John',
             '79%',
             10.30)

DECLARE @columns NVARCHAR(MAX),
        @sql     NVARCHAR(MAX);

SET @columns = N'';

SELECT @columns += N', p.' + QUOTENAME(timestamp)
FROM   (SELECT DISTINCT p.timestamp
        FROM   #table1 AS p) AS x;

SET @sql = N'
select *from (SELECT *
FROM
(
  SELECT * FROM #table1
) AS j
PIVOT
(
  MAX(VALUE) FOR TIMESTAMP IN ('
           + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
           + ')
) AS p)tb
order by tb.sourceid';

EXEC sp_executesql
  @sql;

DROP TABLE #table1 

Answer 2:

使用Dynamic Pivot做到这一点。

DECLARE @sql     NVARCHAR(max),
        @collist VARCHAR(max)=''

SELECT @collist += Isnull(CONVERT(VARCHAR(20), Quotename(timestamp)), '')+ ','
FROM   #pivo
GROUP  BY timestamp

SELECT @collist = LEFT(@collist, Len(@collist) - 1)

SET @sql='select * from Tablename
pivot (max(Value) for timestamp in('
         + @collist + '))piv'

EXEC Sp_executesql @sql 

Answer 3:

对于MS SQL动态透视代码

DECLARE @PivotColumnHeaders varchar(MAX)
SELECT @PivotColumnHeaders =
COALESCE(@PivotColumnHeaders + ',[' + CONVERT(varchar(5),Timestamp,108) + ']', '[' + CONVERT(varchar(5),Timestamp,108)+ ']'  )
FROM (SELECT Timestamp FROM Table1 GROUP BY Timestamp ) UC

DECLARE @PQuery varchar(MAX) = '
    SELECT * FROM (SELECT ID, CONVERT(varchar(5),Timestamp,108) AS Timestamp, Value FROM Table1 T0) T1
    PIVOT (MAX([value]) FOR CONVERT(varchar(5),Timestamp,108) IN (' + @PivotColumnHeaders + ') ) AS P'

EXECUTE (@PQuery)

文章来源: Manipulating table structure