SQL将一个具有不同值的列转换为几个列

I´using SQL Server and I don´t know how to approach this issue. I have one table with 5.5M of records in the following form:

´´使用SQL Server和我不知道如何处理这个问题。我有一张表格，有5.5米的记录，表格如下:

Device        Time             ValueID   Value   Voltage
------------------------------------------------------------
1   2014-03-08 22:50:00      AH1      90,0    90,000"

1   2014-03-08 22:50:00      AT1      5,5     5,500"

1   2014-03-08 22:50:00      PP1      0,0     0,000"

1   2014-03-08 22:50:00      WS1      0,0     0,000"

1   2014-03-08 22:50:00      WD1      270,0   270,000"

1   2014-03-08 22:50:00      SR1      0,0     0,000"

1   2014-03-08 23:49:00      ST1      8,288   2,993"

The column ValueID has 13 different datatypes and the column Value contains the measurement of the data type.

列ValueID有13个不同的数据类型，列值包含数据类型的度量。

I need to create a table with the following form:

我需要创建一个表格，表格如下:

Device Time ValueID(AH1) ValueID(AT1) ValueID(PP1) etc...
------------------------------------------------------------
  1    xxxx   Value         Value          Value

I was trying to use INSERT in a new table with case statement but it does not work, ¿any different aproach? ¿Any clue? Thanks

我试着在一个带有case语句的新表中使用INSERT，但是它不起作用，¿有任何不同的aproach吗?还有什么线索?谢谢

2 个解决方案

#1

We can acheive the result by using Dynamic pivot

我们可以用动态枢轴来获得结果

IF OBJECT_ID('Tempdb..#temp') IS NOT NULL
Drop Table #temp

;WITH cte(Device,Time,ValueID,Value,Voltage)
AS
(
SELECT 1,'2014-03-08 22:50:00' ,'AH1', '90,0' ,  '90,000'   Union all
SELECT 1,'2014-03-08 22:50:00' ,'AT1', '5,5'  ,  '5,500'    Union all
SELECT 1,'2014-03-08 22:50:00' ,'PP1', '0,0'  ,  '0,000'    Union all
SELECT 1,'2014-03-08 22:50:00' ,'WS1', '0,0'  ,  '0,000'    Union all
SELECT 1,'2014-03-08 22:50:00' ,'WD1', '270,0',  '270,000'  Union all
SELECT 1,'2014-03-08 22:50:00' ,'SR1', '0,0'  ,  '0,000'    Union all
SELECT 1,'2014-03-08 23:49:00' ,'ST1', '8,288',  '2,993'  
)
SELECT * INTO #TEMP FROM CTE

Declare @Col nvarchar(max),
         @Sql nvarchar(max),
         @dynamicCol nvarchar(max)

SELECT @Col=STUFF((SELECT ', '+ ValueID 
From  #temp For XML PATH ('')),1,1,'')

;With cte
AS
(
SELECT STUFF((SELECT DISTINCT ', '+  ValueID + ' AS '+QUOTENAME(' ValueID ('+ValueID+')')
From  #temp For XML PATH ('')),1,1,'') AS DynamicColumnames
)
SELECT @dynamicCol= DynamicColumnames from cte

SET @Sql='
SELECT Device,Time,'+ @dynamicCol +' From
(
SELECT Device,Time,ValueID,Value From
#temp
)AS Src
PIVOT 
(
MAX(Value) For ValueID IN ('+@Col+')
)
AS Pvt'

Print @Sql

EXEC(@Sql)

#2

you can use pivot as below:

你可以使用pivot如下:

select Device, [Time], [AH1] as [Value(AH1)], [AT1] as [Value(AT1)]... from (
    select Device, [Time], ValueId, [Value] from Yourtable )
pivot (max([value]) for valueId in ([AH1],[AT1]...)) p

You can generate dynamic columns as below:

可以生成如下所示的动态列:

declare @cols varchar(max)
declare @fromcols varchar(max)
declare @query varchar(max)

select @cols = stuff((select ','+QuoteName(valueid) from #yourdevice group by valueid for xml path('')),1,1,'')
select @fromcols = stuff((select ','+QuoteName(valueid)+' as [Value('+ Valueid + ')]' from #yourdevice group by valueid for xml path('')),1,1,'')
--[AH1] as [Value(AH1)], [AT1] as [Value(AT1)]...

SET @query = 'select Device, [Time], ' + @fromcols + ' from ( ' + ' select Device, [Time], ValueId, [Value] from Yourtable ) a ' 
SET @Query = @query + ' pivot (max([value]) for valueId in (' + @cols + ')) p '

select @query --Execute below by uncommenting after checking the query
--exec sp_execute @query

#1