Manipulating table structure

问题:

I have a table full of data and I am looking to take the contents from one row and add it into column headers and then put its corresponding value below it in that column! The issue that I am trying to describe is best shown by the tables below:

Normal Table

Once this table has been created (this is a temporary table) I want to reconstruct the table to look like this

But I have searched everywhere on line and I can't seem to find out how to do this anywhere! I would be very grateful for the help!

回答1:

try this

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 

回答2:

Use Dynamic Pivot to do this.

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 

回答3:

Dynamic Pivot code for 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)