SQL Convert one column with different values to multiple columns
I am using SQL Server and I have no idea how to approach this problem. I have one table with 5.5M records in the following form:
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 ValueID column contains 13 different data types, and the Value column lists the data type dimension.
I need to create a table with the following shape:
Device Time ValueID(AH1) ValueID(AT1) ValueID(PP1) etc...
------------------------------------------------------------
1 xxxx Value Value Value
I tried to INSERT on a new table with a case argument, but it doesn't work, any other approach? Any hint? Thanks to
+3
source to share
2 answers
We can achieve results 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
user7715598
source
to share
you can use beer as below:
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 create dynamic columns as shown 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
+3
source to share