Split serial column into parallel columns for each value

Question

I have a table with several columns:

CREATE TABLE [dbo].[MyTable](
[ValueID] [int] NOT NULL,
[Timestamp] [datetime] NOT NULL,
[RealValue] [float] NOT NULL
) ON PRIMARY

+---------+----------------------+----------+
| ValueID | Timestamp            |RealValue |
+---------+----------------------+----------+
| 123     | 3/1/2018 12:00:49 AM | 54.1     |
| 123     | 3/1/2018 12:01:49 AM | 55.1     |
| 123     | 3/1/2018 12:02:49 AM | 56.1     |
| 123     | 3/1/2018 12:03:49 AM | 57.1     |
| 123     | 3/1/2018 12:04:49 AM | 58.1     |
| 876     | 3/1/2018 12:00:49 AM | 1.0      |
| 876     | 3/1/2018 12:01:49 AM | 1.1      |
| 876     | 3/1/2018 12:02:49 AM | 1.2      |
| 876     | 3/1/2018 12:03:49 AM | 1.3      |
| 876     | 3/1/2018 12:04:49 AM | 1.4      |
| 63      | 3/1/2018 12:00:49 AM | 300.0    |
| 63      | 3/1/2018 12:01:49 AM | 300.5    |
| 63      | 3/1/2018 12:02:49 AM | 301.0    |
| 63      | 3/1/2018 12:03:49 AM | 301.5    |
| 63      | 3/1/2018 12:04:49 AM | 302.0    |
+---------+----------------------+----------+

I need to split this table into a "column-per-value" structure:

+----------------------+----------+----------+----------+
| Timestamp            | 123      | 876      | 63       |
+----------------------+----------+----------+----------+
| 3/1/2018 12:00:49 AM | 54.1     | 1.0      | 300.0    |
| 3/1/2018 12:01:49 AM | 55.1     | 1.1      | 300.5    |
| 3/1/2018 12:02:49 AM | 56.1     | 1.2      | 301.0    |
| 3/1/2018 12:03:49 AM | 57.1     | 1.3      | 301.5    |
| 3/1/2018 12:04:49 AM | 58.1     | 1.4      | 302.0    |
+----------------------+----------+----------+----------+

Would this be possible with SQL, or is it more suited to a script?

Such as:

SELECT DISTINCT [ValueID] FROM [db].[dbo].[MyTable]

//  ...Build a hash of distinct values in the script...

CREATE TABLE [dbo].[NewTable](
[123] int NOT NULL,
[876] int NOT NULL,
[63] int NOT NULL
...
) ON PRIMARY

//  ...Loop through hash and populate each column with a separate query...

The table is fairly large (26 million rows) and there are ~500 distinct [ValueID] values which would become columns in the destination table.

[SQL transpose fullt table](https://stackoverflow.com/questions/15297809/sql-transpose-full-table) looks similar. — sticky bit, Apr 21 '18 at 21:52
Possible duplicate of [SQL transpose full table](https://stackoverflow.com/questions/15297809/sql-transpose-full-table) — Rann Lifshitz, Apr 22 '18 at 01:24
Da, da-da-daaaa...! [PIVOT](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms177410(v=sql.105)) to the rescue! — Jake Reece, Apr 24 '18 at 14:14

Andrea · Accepted Answer · 2018-05-02T13:24:00.513

You can use the pivot statement.

1. Static version

If you have a fixed number of ValueID values you can write a query like this (otherwise you should use a dynamic solution to produce all output columns):

if OBJECT_ID('MyTable') is null
  CREATE TABLE [dbo].[MyTable](
    [ValueID] [int] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [RealValue] [float] NOT NULL
  )  
insert into [dbo].[MyTable]
          select 123     ,'20180301 12:00:49',54.1      
union all select 123     ,'20180301 12:01:49',55.1      
union all select 123     ,'20180301 12:02:49',56.1      
union all select 123     ,'20180301 12:03:49',57.1      
union all select 123     ,'20180301 12:04:49',58.1      
union all select 876     ,'20180301 12:00:49',1.0       
union all select 876     ,'20180301 12:01:49',1.1       
union all select 876     ,'20180301 12:02:49',1.2       
union all select 876     ,'20180301 12:03:49',1.3       
union all select 876     ,'20180301 12:04:49',1.4       
union all select 63      ,'20180301 12:00:49',300.0     
union all select 63      ,'20180301 12:01:49',300.5     
union all select 63      ,'20180301 12:02:49',301.0     
union all select 63      ,'20180301 12:03:49',301.5     
union all select 63      ,'20180301 12:04:49',302.0     

select piv.*
from 
( 
select ValueID,Timestamp,RealValue
from [dbo].[MyTable] 
) src 
pivot 
( 
max(realValue) 
for valueid in ([123], [876], [63])  -- If you have other valueids add them here
) piv

Results of this query:

2. Dynamic version

If you need a dynamic query that automatically create all the columns for all ValueId values you can use dynamic SQL, but be aware of security problems (SQL injection etc) of this approach!

Here is a sample script that dynamically generates a query with all the columns for your ValueID:

if OBJECT_ID('MyTableDyn') is null
  CREATE TABLE [dbo].[MyTableDyn](
    [ValueID] [int] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [RealValue] [float] NOT NULL
  )  
insert into [dbo].[MyTableDyn]
          select 123     ,'20180301 12:00:49',54.1      
union all select 123     ,'20180301 12:01:49',55.1      
union all select 123     ,'20180301 12:02:49',56.1      
union all select 123     ,'20180301 12:03:49',57.1      
union all select 123     ,'20180301 12:04:49',58.1      
union all select 876     ,'20180301 12:00:49',1.0       
union all select 876     ,'20180301 12:01:49',1.1       
union all select 876     ,'20180301 12:02:49',1.2       
union all select 876     ,'20180301 12:03:49',1.3       
union all select 876     ,'20180301 12:04:49',1.4       
union all select 63      ,'20180301 12:00:49',300.0     
union all select 63      ,'20180301 12:01:49',300.5     
union all select 63      ,'20180301 12:02:49',301.0     
union all select 63      ,'20180301 12:03:49',301.5     
union all select 63      ,'20180301 12:04:49',302.0     
union all select 99      ,'20180301 12:00:49',900.0     
union all select 99      ,'20180301 12:01:49',900.5     
union all select 99      ,'20180301 12:02:49',901.0     
union all select 99      ,'20180301 12:03:49',901.5     
union all select 99      ,'20180301 12:04:49',902.0  

declare @script nvarchar(max)=''
declare @ids nvarchar(max)=''
select @ids = @ids + ', ['+ cast([ValueID] as varchar(max)) + ']' 
  from [dbo].[MyTableDyn] 
  group by [ValueID]
set @ids = RIGHT(@ids, len(@ids)-2)

set @script = @script + 'select piv.*'
set @script = @script + ' from' 
set @script = @script + ' ( '
set @script = @script + ' select ValueID,Timestamp,RealValue'
set @script = @script + ' from [dbo].[MyTableDyn] '
set @script = @script + ' ) src '
set @script = @script + ' pivot '
set @script = @script + ' (' 
set @script = @script + ' max(realValue) '
set @script = @script + ' for valueid in (' + @ids +') '
set @script = @script + ' ) piv '

exec (@script)

Here are the results (as you can see a new column with id 99 appears):

Thank you, this dynamic solution is perfect. – yogatoaster Apr 25 '18 at 14:47 — yogatoaster, Apr 25 '18 at 14:47

Split serial column into parallel columns for each value

1 Answers1

1. Static version

2. Dynamic version