May 10, 2010 at 8:09 am
Hi all
I was asked to create a mechanism that takes a record from table and inserts it into another table in unpivot format. Later on I need to get the data in the new table and pivot it again to its original format. I admit that I’m not completely understand how pivot and unpivot work. I’ve done some reading and played with it a bit and found how to do the unpivot, but I can’t seem to get the pivot to work. In fact since all the examples that I looked at is doing pivot that is based on some numeric aggregation, I don’t think that it can be done. The script bellow creates the original table, the table that holds the data in unpivoted format and the insert statement that populate the unpivoted table. I’d greatly appreciate it if someone can show me how to get the data from the unpivoted table in its original format or will let me know that it isn’t possible.
use tempdb
go
--Creating the source table that holds the original data
create table SourceTable (ID int not null primary key,
StringData varchar(20) null,
DateData datetime null,
IntData int null)
go
--Inserting 2 records as a test data
Insert into SourceTable (ID, StringData, DateData, IntData)
select 1, 'This is a string', '20100508', 0
union
select 2, 'Another string', '20100510', 1
go
--Creating the table that will hold the unpivoted data
create table UnPivotSourceTable (ID int, ColumnName varchar(20), ColumnValue varchar(20))
go
--Insert the data in unpivoted format
insert into UnPivotSourceTable (ID, ColumnName, ColumnValue)
SELECT ID, ColumnName, ColumnValue
FROM
(
SELECT ID,
CONVERT(Varchar(50), StringData) AS StringData,
CONVERT(varchar(50), DateData, 112) AS DateData,
CONVERT(varchar(50), IntData) AS IntData
FROM SourceTable
) MyTable
UNPIVOT
(ColumnValue FOR ColumnName IN (StringData, DateData, IntData))AS MyUnPivot
--Getting the pivoted data
select * from UnPivotSourceTable
--Clean up
drop table SourceTable
drop table UnPivotSourceTable
Thank you for your help.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 10, 2010 at 10:48 am
I found how I should do it. I'm adding the answer just in case that it might help someone in the future.
select *
from dbo.UnPivotSourceTable
pivot
(
MIN(ColumnValue) --I have only one value for each ID, so the function can be MIN/MAX ...
FOR ColumnName IN ([StringData], [DateData], [IntData])
) as pt;
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply