1

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

enter image description here

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

enter image description here

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!

user3538102
  • 171
  • 1
  • 5
  • 14
  • When you say it's dynamic, you mean that the number of columns will depend on the number of distinct values in `TimeStamp` column? – Radu Gheorghiu Jan 06 '15 at 11:53
  • Yes, sorry about the mistake in the question! I will edit it now! Can you help me with this issue? – user3538102 Jan 06 '15 at 11:54
  • looks like homework :) for me it looks like you need to write a query to create table in TSQL after the data are filled in. Question is, what are you planning to do when some additional data are entered to original table – Jan Hruby Jan 06 '15 at 11:55
  • It does look that way alright haha I made the tables as an example to show my problem! It was the easiest way to illustrate my problem! – user3538102 Jan 06 '15 at 11:56
  • @user3538102 I haven't done anything like this before, but I can point you to [Pivot tables](http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx). Because it's dynamic, I think you can combine the principles of Pivot Tables with [Dynamic SQL](http://msdn.microsoft.com/en-us/library/ms188001.aspx) to create the `IN` part of the pivot query (will have to contain all distinct values from `Timestamp` column). – Radu Gheorghiu Jan 06 '15 at 11:57
  • I believe this is relevant for you - http://stackoverflow.com/questions/10877407/t-sql-how-to-create-tables-dynamically-in-stored-procedures first you use the loop to define all necessary columns based on distinct times in last column, second you use another loop to reorganize the data from original table and finally remove original table and rename the new one – Jan Hruby Jan 06 '15 at 12:03
  • I think it is bad practice to store values in headers. – i486 Jan 06 '15 at 13:13

3 Answers3

0

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
StackUser
  • 5,370
  • 2
  • 24
  • 44
  • I can't do it like this because the table is going to grow with more people and it needs to be able to handle this! – user3538102 Jan 06 '15 at 13:00
  • if you need to keep content of the original table, then execute this as after insert trigger... after you insert new record in original table, you alter the structure of dynamic table if necessary by adding new column, if not, you just add new record – Jan Hruby Jan 06 '15 at 13:11
0

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
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
-1

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)
LeeG
  • 708
  • 5
  • 14