操作表结构

Question

我有一个满是数据的表，我希望从一行中提取内容，并将其添加到列标题中，然后将其相应的值放在该列中的下面！下面的表格最能说明我正在尝试描述的问题：

正规表

​

​

一旦创建了这个表(这是一个临时表)，我希望将该表重新构建为如下所示

​

​

但是我已经在网上到处搜索了，我似乎找不到怎么做的地方！我将非常感谢你的帮助！

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)