Transpose row data to 1 colunn

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Transpose row data to 1 colunn

Author

Topic

mrHenry
Starting Member

2 Posts

Posted - 2010-05-03 : 16:20:49

Hello,
I'm new to SQL.
I have a Table, TableA that has 500 columns in the following format:

Timestamp |Data_VAL0|Data_VAL1|Data_VAL2|Data_VAL3|...
2010-05-03 10:16:40.003| 15174 | 15173 | 15172 |15171 |...

I'd like to take all of the column data excluding the Timestamp column, and make one column - in another table, TableB.

AllData
15174
15173
15172
15171
...

I've looked into the Pivot method with no success.
Can someone point me in the right direction or provide some sample code?
Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-03 : 16:52:48

Something like this.

select [Timestamp],Data,Vals
from
(
select * from @t
) pvt
unpivot
(Vals for Data IN (Data_VAL0,Data_VAL1,Data_VAL2,Data_VAL3)
) unpvt

for this sample data

declare @t table ([Timestamp] datetime, Data_VAL0 int,Data_VAL1 int,Data_VAL2 int,Data_VAL3 int)
insert @t
select '2010-05-03 10:16:40.003',15174 , 15173 , 15172 ,15171
union all select '2010-05-02 10:16:40.003',14174 , 14173 , 14172 ,14171

mrHenry
Starting Member

2 Posts

Posted - 2010-05-03 : 18:43:44

Cool, this works.
But if I have 500+ columns I don't want to write the name of each column in the unpivot command
(Vals for Data IN (Data_VAL0,Data_VAL1,Data_VAL2,Data_VAL3,....,Data_VAL500)
) unpvt
Can i write some sort of for loop to include all Data_VAL### columns?
If it helps, I can get rid of the time stamp column and put it into another table, I can join the two tables later.

I would attempt to redesign the table, but I am gathering high speed data and transferring it into SQL, in chunks [arrays] to minimize network traffic; otherwise, the computer resources are completely drained
Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 04:04:32

then you need to go for dynamic sql

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources