T-SQL查询：sqlserver 行列转换

最新推荐文章于 2024-12-05 18:05:57 发布

薛定谔的DBA

最新推荐文章于 2024-12-05 18:05:57 发布

阅读量1k

点赞数

分类专栏： SQLServer SQLServer SQL应用

SQLServer 同时被 2 个专栏收录

269 篇文章

订阅专栏

SQLServer SQL应用

46 篇文章

订阅专栏

本文详细介绍了SQL中将二维表数据转为一维表和一维表转为二维表的方法，包括行转列、列转行的技术实现，通过案例演示了多种SQL语句的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*【行转列】*/  
  
CREATE TABLE TB(姓名 VARCHAR(10) , 课程 VARCHAR(10) , 分数 INT)  
GO  
  
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  
  
  
/*方法一*/  
SELECT 姓名,  
'语文'=(SELECT 分数 FROM DBO.TB WHERE 姓名=A.姓名 AND 课程='语文'),  
'数学'=(SELECT 分数 FROM DBO.TB WHERE 姓名=A.姓名 AND 课程='数学'),  
'物理'=(SELECT 分数 FROM DBO.TB WHERE 姓名=A.姓名 AND 课程='物理')  
FROM TB A  
GROUP BY 姓名  
  
  
  
/*方法二*/  
SELECT 姓名,  
'语文'=(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 END),  
'数学'=(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 END),  
'物理'=(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 END)  
FROM TB  
  
SELECT 姓名,  
'语文'=MAX(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 END),  
'数学'=MAX(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 END),  
'物理'=MAX(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 END)  
FROM TB  
GROUP BY 姓名  
  
--或者  
SELECT 姓名,  
'语文'=SUM(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 END),  
'数学'=SUM(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 END),  
'物理'=SUM(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 END)  
FROM TB  
GROUP BY 姓名  
  
  
  
/*方法三：使用SQL SERVER 2000静态SQL*/  
SELECT * FROM TB  
PIVOT(  
MAX(分数) FOR 课程 IN (语文,数学,物理)  
)AS A  
  
  
  
/*方法四：使用SQL SERVER 2000动态SQL*/  
--使用STUFF()  
DECLARE @SQL VARCHAR(8000)  
SET @SQL=''  
SELECT @SQL=@SQL+','+课程 FROM TB GROUP BY 课程  
SET @SQL=STUFF(@SQL,1,1,'')--去掉首个','  
SET @SQL='SELECT * FROM TB PIVOT (MAX(分数) FOR 课程 IN ('+@SQL+')) AS A'  
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+')) AS A'  
EXEC(@SQL)  
  
--或  
DECLARE @SQL VARCHAR(500)  
SET @SQL='SELECT 姓名'  
SELECT @SQL=@SQL+',MAX(CASE 课程 WHEN '''+课程+''' THEN 分数 ELSE 0 END)['+课程+']'  
FROM(SELECT DISTINCT 课程 FROM TB) AS A  
SET @SQL=@SQL+' FROM TB GROUP BY 姓名'  
EXEC(@SQL)  
  
/*********************************************************************************/  
/*********************************************************************************/  
  
/*【列转行】*/  
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB  
GO  
  
  
CREATE TABLE TB(姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT)  
INSERT INTO TB VALUES('张三',74,83,93)  
INSERT INTO TB VALUES('李四',74,84,94)  
GO  
  
SELECT * FROM TB  
  
  
/*方法一：SQL SERVER 2000 静态SQL*/  
SELECT * FROM  
(  
SELECT 姓名 , 课程= '语文' , 分数 = 语文 FROM TB   
UNION ALL  
SELECT 姓名 , 课程 = '数学' , 分数 = 数学 FROM TB  
UNION ALL  
SELECT 姓名 , 课程 = '物理' , 分数 = 物理 FROM TB  
) AS T  
ORDER BY 姓名,  
    CASE 课程  
    WHEN '语文' THEN 1  
    WHEN '数学' THEN 2  
    WHEN '物理' THEN 3  
    END  
  
  
  
/*方法二*/  
SELECT 姓名,课程,分数 FROM TB  
UNPIVOT(  
分数 FOR 课程 IN([语文] , [数学] , [物理])  
) AS T  
  
  
  
/*方法三：SQL SERVER 2000 动态SQL*/  
DECLARE @SQL VARCHAR(8000)  
SELECT @SQL = ISNULL(@SQL + ' UNION ALL ' , '' )+' SELECT 姓名 , [课程] = ' + QUOTENAME(NAME , '''') + ' , [分数] = ' + QUOTENAME(NAME) + ' FROM TB'  
FROM SYSCOLUMNS   
WHERE NAME! = N'姓名' AND ID = OBJECT_ID('TB') --表名TB，不包含列名为姓名的其它列  
ORDER BY COLID ASC EXEC(@SQL + ' ORDER BY 姓名 ')