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.
| Author |
Topic |
|
mwidjaja
Starting Member
8 Posts |
 Posted - 2010-07-03 : 09:50:25
|
| Hi,How do I transform:this table:Col1 Col2DATA1 A1DATA1 A2DATA1 A3DATA2 B1DATA2 B2DATA2 B3DATA3 C1DATA3 C2DATA3 C3Col1 Col2 Col3 COl4Data1 A1 A2 A3Data2 B1 B2 B3Data3 C1 C2 C3I used temporary table with something like:create table ##table (col1 nvarchar(10),col2 float,col3 float,col4 float)insert ##tableselect col1,col2,col3,col4wherecol1 like '%data%'select * from ##tabledrop table ##tableI can only get 1 Data row. Any input is greatly appreciated.MW |
|
|
sql-programmers
Posting Yak Master
190 Posts |
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2010-07-05 : 00:39:42
|
| can you try this:SELECT col1,MAX(CASE WHEN CHARINDEX(1,col2)>0 THEN col2 END) AS 'col2',MAX(CASE WHEN CHARINDEX(2,col2)>0 THEN col2 END) AS 'col3',MAX(CASE WHEN CHARINDEX(3,col2)>0 THEN col2 END) AS 'col4'FROM tableGROUP BY col1--------------------Rock n Roll with SQL |
 |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
|
|
|
|
|
|
|
help for table transformation rows to columns