pivot

<http://www.cnblogs.com/gaizai/>

if Exists (Select * from sys.Objects where object_id=object_id(N'dbo.TestRowToColumn') and type in (N'U') )
drop table dbo.TestRowToColumn

go
create table [dbo].[TestRowToColumn]
(

[Id] int identity(1,1)  primary key not null,
UserName nvarchar(50) null,
Subject nvarchar(50) null,
Source numeric(18,0) null
)
 
go

Insert into TestRowToColumn (UserName ,Subject ,Source )select N'张三',N'语文',60 union all select N'张三1',N'数学',68 union all
select N'张三1',N'数据',12 union all
select N'张三1',N'语文',23 union all
select N'张三4',N'数学',50 union all
select N'张三4',N'数据',69 union all
select N'张三6',N'数学',99 union all
select N'张三4',N'语文',69 union all
select N'张三6',N'数据',78 union all
select N'张三6',N'语文',88 union all
select N'张三11',N'数据',89 union all
select N'张三',N'数学',60 union all
select N'张三',N'数据',45
go
select * from TestRowToColumn order by UserName
delete TestRowToColumn
select UserName,SUM(cASE[Subject]  WHEN '数学' Then Source else 0 end) as '数学',SUM(cASE[Subject]  WHEN '语文' Then Source else 0 end) as '语文',SUM(cASE[Subject]  WHEN '数据' Then Source else 0 end) as '数据' from TestRowToColumn group by UserName



DECLARE @SQL VARCHAR(8000)


select @sql=isnull(@sql,'')+ quotename(username) from TestRowToColumn
print @sql

SET @SQL='SELECT USERNAME,'
SELECT @SQL =@SQL +'SUM(CASE SUBJECT WHEN '''+[SUBJECT]+''' THEN SOURCE ELSE 0 END) AS '''+QUOTENAME(SUBJECT)+''','FROM (SELECT DISTINCT SUBJECT FROM TestRowToColumn ) AS A
print @sql SELECT @SQL=LEFT(@SQL,LEN(@SQL)-1)+' FROM TestRowToColumn GROUP BY USERNAME'

print (@sql)
exec(@sql);


select * FROM (SELECT USERNAME,SUBJECT,SOURCE FROM TestRowToColumn )  P
PIVOT (SUM(SOURCE) FOR username IN (张三1,张三4,张三11)) as pvt order by
pvt.Subject;

declare @sql_str varchar(8000)
declare @sql_col varchar(8000)
select @sql_col     =ISNULL(@SQL_COL+',','')+QUOTENAME(USERNAME)FROM TestRowToColumn GROUP BY USERNAME  
print @sql_col
SET @SQL_STR='SELECT * FROM (SELECT USERNAME,SUBJECT ,SOURCE FROM TestRowToColumn) P PIVOT (SUM(SOURCE) FOR USERNAME IN ( ' +@SQL_COL+')) AS PVT ORDER BY PVT.SUBJECT'
PRINT (@SQL_STR)
EXEC(@sql_str)

 

///////////////////////////////////

////////////////////////////////////////

DECLARE @sql_str NVARCHAR(MAX)
DECLARE @sql_col NVARCHAR(MAX)
DECLARE @tableName SYSNAME --行转列表
DECLARE @groupColumn SYSNAME --分组字段
DECLARE @row2column SYSNAME --行变列的字段
DECLARE @row2columnValue SYSNAME --行变列值的字段
SET @tableName = 'TestRowToColumn'
SET @groupColumn = 'UserName'
SET @row2column = 'Subject'
SET @row2columnValue = 'Source'
declare @whe nvarchar(500)='where username=''张三'''

--从行数据中获取可能存在的列
SET @sql_str = N'
SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])
    FROM ['+@tableName+'] GROUP BY ['+@row2column+']'
--PRINT @sql_str
EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
--PRINT @sql_col

SET @sql_str = N'
SELECT * FROM (
    SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+'] '+@whe+') p PIVOT
    (SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt
ORDER BY pvt.['+@groupColumn+']'
--PRINT (@sql_str)
EXEC (@sql_str)




DECLARE @sql_str NVARCHAR(MAX)
DECLARE @sql_col NVARCHAR(MAX)
DECLARE @sql_where NVARCHAR(MAX)
DECLARE @tableName SYSNAME --行转列表,-=nvarchar(128)
DECLARE @groupColumn SYSNAME --分组字段
DECLARE @row2column SYSNAME --行变列的字段
DECLARE @row2columnValue SYSNAME --行变列值的字段
SET @tableName = 'TestRowToColumn'
SET @groupColumn = 'UserName'
SET @row2column = 'Subject'
SET @row2columnValue = 'Source'
SET @sql_where = 'WHERE UserName = ''张三1'''

--从行数据中获取可能存在的列
SET @sql_str = N'
SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])
    FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']'
--PRINT @sql_str
EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
--PRINT @sql_col

SET @sql_str = N'
SELECT * FROM (
    SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']'+@sql_where+') p PIVOT
    (SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt
ORDER BY pvt.['+@groupColumn+']'
--PRINT (@sql_str)
EXEC (@sql_str)