SQL SERVER列转换行及UNPIVOT
--***************列传行***************************
--构建样例表及初始化数据
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB
(
姓名 NVARCHAR(10)
,语文 INT
,数学 INT
,物理 INT
)
INSERT INTO TB VALUES(N'张三',74,83,93)
INSERT INTO TB VALUES(N'李四',74,84,94)
SELECT * FROM TB WITH(NOLOCK)
GO
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB
(
姓名 NVARCHAR(10)
,语文 INT
,数学 INT
,物理 INT
)
INSERT INTO TB VALUES(N'张三',74,83,93)
INSERT INTO TB VALUES(N'李四',74,84,94)
SELECT * FROM TB WITH(NOLOCK)
GO
--SQL SERVER 2000静态SQL
SELECT
*
FROM (
SELECT
姓名
,N'语文' AS 课程
,语文 AS 分数
FROM TB
UNION ALL
SELECT
姓名
,N'数学' AS 课程
,数学 AS 分数
FROM TB
UNION ALL
SELECT
姓名
,N'物理' AS 课程
,物理 AS 分数
FROM TB
) AS TT
ORDER BY TT.姓名
SELECT
*
FROM (
SELECT
姓名
,N'语文' AS 课程
,语文 AS 分数
FROM TB
UNION ALL
SELECT
姓名
,N'数学' AS 课程
,数学 AS 分数
FROM TB
UNION ALL
SELECT
姓名
,N'物理' AS 课程
,物理 AS 分数
FROM TB
) AS TT
ORDER BY TT.姓名
--SQL SERVER 2000动态SQL
DECLARE @SQL6 nvarchar(MAX)
SELECT
@SQL6=ISNULL(@SQL6+'
UNION ALL ','')+ N'SELECT
姓名
,N'''+[Name]+ N''' 课程,'+[Name] +N' 分数 FROM TB '
FROM sys.columns
WHERE
[object_id]=OBJECT_ID('TB') AND[Name]!=N'姓名'
SET @SQL6=N'SELECT
*
FROM ('+@SQL6+N') AS T
ORDER BY T.姓名'
PRINT @SQL6
DECLARE @SQL6 nvarchar(MAX)
SELECT
@SQL6=ISNULL(@SQL6+'
UNION ALL ','')+ N'SELECT
姓名
,N'''+[Name]+ N''' 课程,'+[Name] +N' 分数 FROM TB '
FROM sys.columns
WHERE
[object_id]=OBJECT_ID('TB') AND[Name]!=N'姓名'
SET @SQL6=N'SELECT
*
FROM ('+@SQL6+N') AS T
ORDER BY T.姓名'
PRINT @SQL6
EXEC(@SQL6)
UNPIVOT--列名转换列值,即列转行
Table_Source
UNPIVOT
(
value_column
FOR pivot_column
IN(列名列表)
)别名
----SQL SERVER 2005静态SQLSELECT
姓名,分数,课程
FROM TB
UNPIVOT
(
分数 FOR 课程 IN
(
语文
,数学
,物理
)
) AS T
----SQL SERVER 2005动态SQL
DECLARE @SQL7 NVARCHAR(MAX)
SELECT @SQL7=ISNULL(@SQL7+',','')+[Name] FROM sys.columns WHERE
[object_id]=OBJECT_ID('TB') AND[Name]!=N'姓名'
SET @SQL7= N'SELECT 姓名,分数,课程 FROM TB UNPIVOT( 分数 FOR 课程 IN ('+@SQL7+N')) AS T ORDER BY 姓名'
PRINT @SQL7
EXEC SP_EXECUTESQL @SQL7
DECLARE @SQL7 NVARCHAR(MAX)
SELECT @SQL7=ISNULL(@SQL7+',','')+[Name] FROM sys.columns WHERE
[object_id]=OBJECT_ID('TB') AND[Name]!=N'姓名'
SET @SQL7= N'SELECT 姓名,分数,课程 FROM TB UNPIVOT( 分数 FOR 课程 IN ('+@SQL7+N')) AS T ORDER BY 姓名'
PRINT @SQL7
EXEC SP_EXECUTESQL @SQL7
姓名 课程 分数
---------- -----------
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步