Some representative posts, see http://expert.9cbs.net/expert/topic/2440/2440306.xml?temp =.6941645
1: Column turn to line: EG1: CREATE TABLE TEST (Name Char (10), KM Char (10), CJ INT) GoInsert Test Values ('Zhang San', 'Language', 80) Insert Test Values ('Zhang 3 ',' Mathematics ', 86) INSERT TEST VALUES (' Zhang San ',' English ', 75) Insert Test Values (' Li Si ',' Language ', 78) INSERT TEST VALUES (' Li Si ',' Mathematics " , 85) Insert Test Values ('Li Si', 'English ", 78)
Imagine
Name Language Mathematics English Zhang San 80 86 75 Li Si 78 85 78
Declare @sql varchar (8000) set @SQL = 'select name'SELECT @SQL = @SQL ', SUM (Case Km When '' ' Km ' '' Ten CJ End) [' KM '] 'from SELECT DISTINCT KM from test) as aselect @SQL = @ SQL 'from test group by name'exec (@SQL)
Drop Table Test
EG2: Table A, ID PID 1 1 1 2 1 3 2 1 2 2 3 1 How to become Table B: ID PID 1 1, 2, 3 2 1, 2 3 1 Alternatively from Table B (do not use Cursor) I have a similar list before, I can't find it, help!
--1. Create a merged function crete Ferg (@id int) returns varchar (8000) asbegindeclare @str varchar (8000) set @str = '' select @ str = @ Str ',' cast (PID As Varchar ), a where id = @ idset @ Str = Right (@ Str, Len (@str) -1) Return (@str) endGo
- Call custom functions get the result Select Distinct ID, DBO.FMERG (ID) from FROM Table A
2: / *********** Row to transfer **************** / test: CREATE TABLE T1 (A INT, B INT, C INT, D, E INT, F INT, G INT, H INT) INSERT T1 VALUES (15, 9, 1, 0, 1, 2, 2, 0)
Declare @ varchar (8000) set @ = '' SELECT @ = @ r r (name) 'from T1 Union All Select' from syscolumns where id = Object_ID ('t1') set @ = left (@, len (@) -Len ('from T1 Union All Select') - Print @Exec ('SELECT' @ 'from T1') A ----------- 159101220
