Table A record ID VAL TV1 100 10 2 200 20 4 400 40 --------- There is no ID to 3 record 0 instead of getting this query result: ----------- --------- Id1 VAL1 TV1 ID2 VAL2 TV2 ID3 VAL3 TV3 ID4 VAL4 TV41 100 10 2 200 20 3 0 0 4 400 40
-------------------- Answer (9CBS Zou Jian)
--test
- Test Data Create Table A (ID INT, VAL INT, TV INT) INSERT A SELECT 1,100,10 Union All Select 2,200,20 Union All SELECT 4,400,40go
- Query declare @s varchar (8000), @ i1 int, @ i2 intSelect @ i1 = min (id) - If fixed from 1, directly assign: @ i1 = 1, @ i2 = max (id), @S = '' from a while @ i1 <= @ i2begin select @ s = @ S ', ID' CAST (@ i1 as varchar) '=' CAST (ID as varchar) ', VAL' CAST (@ i1 as varchar) '=' CAST (Val as varchar) ', TV' CAST (@ i1 as varchar) '=' CAST (TV as varchar) from a where id = @ i1 if @ @ RowCount = 0 SELECT @ s = @ s ', ID' CAST (@ i1 as varchar) '=' CAST (@ i1 as varchar) ', Val' Cast (@ i1 as varchar) '= 0 ' ', TV ' CAST (@ i1 as varchar) ' = 0 'set @ i1 = @ i1 1ndset @ s = stuff (@ s, 1, 1,' ') EXEC (' SELECT ' @ s) GO - Remove Test DROP TABLE A
/*--Test Results
ID1 VAL1 TV1 ID2 VAL2 TV2 ID3 VAL3 TV3 ID4 VAL4 TV4 ------------------------------- - ---- - ----- ----- ----- 1 100 10 2 200 20 0 0 4400 40 - * /
Record can't be too much
