sqlserver行转列

一丶行专列案例【借鉴】

1.建立表格

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb

go

CREATE TABLE tb(姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT)

insert into tb VALUES ('张三','语文',74)
insert into tb VALUES ('张三','数学',83)
insert into tb VALUES ('张三','物理',93)
insert into tb VALUES ('李四','语文',74)
insert into tb VALUES ('李四','数学',84)
insert into tb VALUES ('李四','物理',94)

go

SELECT * FROM tb

go

动态sql

--使用stuff()

DECLARE @sql VARCHAR(8000)

SET @sql=''  --初始化变量 @sql

SELECT @sql= @sql+',' + 课程 FROM tb GROUP BY 课程 --变量多值赋值

SET @sql= STUFF(@sql,1,1,'')--去掉首个','

SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

PRINT @sql

exec(@sql)

--或使用isnull()

DECLARE @sql VARCHAR(8000)

--获得课程集合

SELECT @sql= ISNULL(@sql+',','')+课程 FROM tb
GROUP BY 课程           

SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

exec(@sql)

二丶实战

行转列

DECLARE @sql VARCHAR(8000)
SET @sql=''  --初始化变量 @sql
SELECT @sql= @sql+',' + '['+Admission_Grade+']' FROM #temp WHERE Admission_Grade>YEAR(GETDATE())-3 GROUP BY Admission_Grade ORDER BY Admission_Grade DESC--变量多值赋值
SET @sql= STUFF(@sql,1,1,'')--去掉首个','

SET @sql='select * from #temp pivot (max(studentCount) for Admission_Grade in ('+@sql+'))a'
PRINT @sql
exec(@sql)

2.

DECLARE @sql VARCHAR(8000)
SET @sql=''  --初始化变量 @sql
SELECT @sql= @sql+',' + '['+Admission_Grade+']' FROM #temp WHERE Admission_Grade>YEAR(GETDATE())-3 GROUP BY Admission_Grade ORDER BY Admission_Grade DESC--变量多值赋值
SET @sql= STUFF(@sql,1,1,'')--去掉首个','
SET @sql = ' select m.* , n.stuCount from
(select  * from (select * from #temp) a pivot (max(studentCount) for Admission_Grade in (' + @sql
    + ')) b) m,
(select Magor_Code,StudyMode,StudyHierarchy,sum(studentCount) as stuCount from #temp WHERE  Admission_Grade>YEAR(GETDATE())-3  group by Magor_Code,StudyMode,StudyHierarchy) n
where m.Magor_Code= n.Magor_Code AND m.StudyMode=n.StudyMode AND m.StudyHierarchy=n.StudyHierarchy
'
PRINT @sql
exec(@sql)

 3.sqlserver 多行转一行字符串(解决for xml path 超过2033截断问题)

1、t-sql：经过测试此方法在数据量较大时候效率特别低。

 

2、for xml path：此方法效率可以但是需要注意利用stuff函数将生成的xml文件转换为字符串，否则用sqldatereader或者SqlDataAdapter读取数据只能读取2033个字符。

例如：有如下test表有数据 

Id Name

1  a

2  b

3  c

4  d

当使用：SELECT ','+Id  FROM dbo.Test FOR XML PATH(''));  //这样读取的数据虽然是1,2,3,4,但是仍然是xml格式，所以当数据超过2033时候，用sqldatereader或者sqldateadapter读取数据会截断xml文件。

此时要：SELECT STUFF((SELECT ','+Id  FROM dbo.Test FOR XML PATH('')),1,1,'');//利用stuff函数将xml文件转换为一个字符串字段。就不会截断。