How to achieve the horizontal column

Category: MS-SQL Server -> Foundation Classes Author: stookyyb Date: 2011-02-23 21:00:17
 
stookyyb
2011-02-23 21:00:17
There are as follows
X A 1
X B 2
Y C 3
How
A B C
X 1 2
Y 3

the effect of the horizontal column
zeroiii
2011-02-23 21:28:18
SQL statement to convert the ordinary ranks

Suppose Zhang Xuesheng results table (tb_rowtocol) as
Name Subject Result
Joe Smith Joe Smith mathematical language 73
83
Zhang Physics 93
three languages ​​74
John Doe John Doe John Doe 84
mathematical physics 94

want to become
Language Mathematical Physics
name connotes 73 83 93
John Doe 74 84 94

declare @ sql varchar (4000 )
set @ sql = 'select Name as' +' name '
select @ sql = @ sql +' , sum (case Subject when'' '+ Subject +''' then Result end) ['+ Subject +'] '
from (select distinct Subject from ; rowtocol) as a
set @ sql = @ sql + 'from rowtocol group by name'
exec (@ sql) If the above two tables

interchangeably here: namely
table name (cj)
name connotes language of mathematical physics
73 83 93
John Doe 74 84 94

want to become

Name Subject Result
connotes language 73
Joe Smith Joe Smith Mathematical Physics 93
83
language 74
John Doe John Doe mathematical physics 94 84
John Doe

select name as Name, 'language' as Subject, Language as Result from CJ union
select name as Name 'mathematics' as Subject, mathematics as Result from CJ union
select name as Name, 'physical' as Subject, physical as Result ; from CJ
order by name desc

ylfbj
2011-02-23 21:52:24
/ * reference Zou Brother * /
create table tb (O char (1), X varchar (10), Y varchar (10), Z varchar (10))
insert tb select 'A', 'x1', ' y1 ',' z1 '
union all select' B ',' x2 ',' y2 ',' z2 '
union all select' C ',' x3 ',' y3 ',' z3 '
go
declare @ s1 varchar (8000), @ s2 varchar (8000)
, @ s3 varchar (8000), @ s4 varchar (8000), @ s5 varchar (8000)
, @ i varchar (10)
select @ s1 ='', @ s2 ='' , @ s3 ='', @ s4 ='', @ s5 ='', @ i = '0 '
select @ s1 = @ s1 +', @ '+ @ i +' varchar ( 8000) '
, @ s2 = @ s2 +', @ '+ @ i +' ='' O ='''' '+ name +''''''''
, @ s3 = @ s3 + '
, @' + @ i + '= @' + @ i + '+'', ['' + cast ([O] as varchar ) +''] ='''''' + cast (['+ name +'] as varchar) +'''''''' '
, @ s4 = @ s4 + ', @' + @ i + '='' select'' + @' + @ i
, @ s5 = @ s5 + '+'' union all'' + @' + @ i
, @ i = cast (@ i as int) +1
from syscolumns
where object_id ('tb') = id and ; colid> 1
order by colid
select @ s1 = stuff (@ s1, 1,1,'')
, @ s2 = stuff ( @ s2, 1,1,'')
, @ s3 = stuff (@ s3, 1,5,'')
, @ s4 = stuff (@ s4 , 1,1,'')
, @ s5 = stuff (@ s5, 1,15,'')
exec ('declare' + @ s1 + '
select '+ @ s2 +'
select '+ @ s3 +'
from tb
select '+ @ s4 +'
exec ('+ @ s5 +') ')
/ * print @ s1: @ 0 varchar (8000), @ 1 varchar (8000), @ 2 ; varchar (8000)
print @ s2: @ 0 = 'O ='' X''', @ 1 = 'O ='' Y''', @ 2 = 'O ='' Z'' '
print @ s3: @ 0 = @ 0 +', ['+ cast ([O] as varchar) +'] ='' '+ cast ([X] as varchar) +''''
, @ 1 = @ 1 + ', [' + cast ([O] as varchar) + '] = '' '+ cast ([Y] as varchar) +''''
, @ 2 = @ 2 +', ['+ cast ([O] ; as varchar) + '] =''' + cast ([Z] as varchar) +''''
print @ s4: @ 0 = 'select' + @ 0 , @ 1 = 'select' + @ 1, @ 2 = 'select' + @ 2
print @ s5: @ 0 + 'union all' + @ 1 + ' ; union all '+ @ 2 * /
- Remove the test environment
drop table tb
/ * - Showing results
O A B C
- ---------------
X x1 x2 x3
Y y1 y2 y3
Z z1 z2 z3
(the number of rows shadow only 3 lines)
- * /
llu85
2011-02-23 22:15:29
the rows into columns: PIVOT and UNPIVOT
huangjiakui530154121
2011-02-23 22:31:11
Learning Learn
houen1234
2011-02-23 22:50:04
Hopewell_Go (good yet to come! hope better!!) ()
This can be affixed ^ 0 ^