有个表,结构如下:
accno year month flag
1233 2006 1 0
1233 2006 2 0
1233 2006 3 11234 2006 1 0
1234 2006 2 1
1234 2006 3 0如何将如上表中的内容读出,根据flag的内容 写到如下结构的表中:
accno year month1 month2 month3
1233 2006 0 0 1
1234 2006 0 1 0
accno year month flag
1233 2006 1 0
1233 2006 2 0
1233 2006 3 11234 2006 1 0
1234 2006 2 1
1234 2006 3 0如何将如上表中的内容读出,根据flag的内容 写到如下结构的表中:
accno year month1 month2 month3
1233 2006 0 0 1
1234 2006 0 1 0
Create table Test(accno int,Years int,Months int,flag int)
Insert Test Select 1233,2006,1,0
Union all Select 1233,2006,2,0
Union all Select 1233,2006,3,1
Union all Select 1234,2006,1,0
Union all Select 1234,2006,2,1
Union all Select 1234,2006,3,0
--转置语句
Declare @S Varchar(1000)
Set @S=''
Select @S=@S+',SUM(Case months When '''+Rtrim(months)+''' Then flag Else 0 End) As Month'+Rtrim(months) From (Select Distinct months From TEST) A Order By months
Set @S='Select accno,years'+@S+' From TEST Group By accno,years Order By accno,years'
EXEC(@S)