根据数据达到 行列转换 的效果

kanmaogou 2019-06-12 03:16:19
已知数据:
name tv types svalue
-------------------------
a 1 rest a1
a 2 work a2
a 3 rest a3
a 4 work a4
a 5 rest a5
a 6 work a6
a 7 rest a7
b 1 rest b1
b 2 work b2
b 3 rest b3
c 1 work c1

希望得到如下数据:

name t1 t2 t3 t4 t5 t6 t7
-------------------------------
a a1 a2 a3 a4 a5 a6 a7
b b1 b2 b3
c c1
...全文
129 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
kanmaogou 2019-06-13
  • 打赏
  • 举报
回复
引用 3 楼 RINK_1 的回复:



IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(NAME VARCHAR(10),
 TV INT,
 TYPES VARCHAR(10),
 SVALUE VARCHAR(10))

INSERT INTO #T
SELECT 'A',1,'REST','A1' UNION ALL
SELECT 'A',2,'WORK','A2' UNION ALL
SELECT 'A',3,'REST','A3' UNION ALL
SELECT 'A',4,'WORK','A4' UNION ALL
SELECT 'A',5,'REST','A5' UNION ALL
SELECT 'A',6,'WORK','A6' UNION ALL
SELECT 'A',7,'REST','A7' UNION ALL
SELECT 'B',1,'REST','B1' UNION ALL
SELECT 'B',2,'WORK','B2' UNION ALL
SELECT 'B',3,'REST','B3' UNION ALL
SELECT 'C',1,'WORK','C1' UNION ALL
SELECT 'C',2,'REST','C2' UNION ALL
SELECT 'C',3,'REST','C3'

--如果T1到T7字段是固定显示的,那就用以下的静态语句


SELECT NAME,
MAX(CASE WHEN TV=1 THEN SVALUE ELSE '' END) AS T1,
MAX(CASE WHEN TV=2 THEN SVALUE ELSE '' END) AS T2,
MAX(CASE WHEN TV=3 THEN SVALUE ELSE '' END) AS T3,
MAX(CASE WHEN TV=4 THEN SVALUE ELSE '' END) AS T4,
MAX(CASE WHEN TV=5 THEN SVALUE ELSE '' END) AS T5,
MAX(CASE WHEN TV=6 THEN SVALUE ELSE '' END) AS T6,
MAX(CASE WHEN TV=7 THEN SVALUE ELSE '' END) AS T7
FROM #T
GROUP BY NAME

--如果字段的个数是可变的,那就用以下的动态语句


DECLARE @SQL VARCHAR(8000)

SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN TV='+CAST(TV AS VARCHAR)+' THEN SVALUE ELSE '''' END) AS T'+CAST(TV AS VARCHAR)
FROM 
(SELECT TV FROM #T GROUP BY TV) AS A
ORDER BY TV

SET @SQL='SELECT NAME,'+@SQL+' FROM #T GROUP BY NAME'

EXEC(@SQL)

谢谢大神
RINK_1 2019-06-12
  • 打赏
  • 举报
回复



IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(NAME VARCHAR(10),
 TV INT,
 TYPES VARCHAR(10),
 SVALUE VARCHAR(10))

INSERT INTO #T
SELECT 'A',1,'REST','A1' UNION ALL
SELECT 'A',2,'WORK','A2' UNION ALL
SELECT 'A',3,'REST','A3' UNION ALL
SELECT 'A',4,'WORK','A4' UNION ALL
SELECT 'A',5,'REST','A5' UNION ALL
SELECT 'A',6,'WORK','A6' UNION ALL
SELECT 'A',7,'REST','A7' UNION ALL
SELECT 'B',1,'REST','B1' UNION ALL
SELECT 'B',2,'WORK','B2' UNION ALL
SELECT 'B',3,'REST','B3' UNION ALL
SELECT 'C',1,'WORK','C1' UNION ALL
SELECT 'C',2,'REST','C2' UNION ALL
SELECT 'C',3,'REST','C3'

--如果T1到T7字段是固定显示的,那就用以下的静态语句


SELECT NAME,
MAX(CASE WHEN TV=1 THEN SVALUE ELSE '' END) AS T1,
MAX(CASE WHEN TV=2 THEN SVALUE ELSE '' END) AS T2,
MAX(CASE WHEN TV=3 THEN SVALUE ELSE '' END) AS T3,
MAX(CASE WHEN TV=4 THEN SVALUE ELSE '' END) AS T4,
MAX(CASE WHEN TV=5 THEN SVALUE ELSE '' END) AS T5,
MAX(CASE WHEN TV=6 THEN SVALUE ELSE '' END) AS T6,
MAX(CASE WHEN TV=7 THEN SVALUE ELSE '' END) AS T7
FROM #T
GROUP BY NAME

--如果字段的个数是可变的,那就用以下的动态语句


DECLARE @SQL VARCHAR(8000)

SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN TV='+CAST(TV AS VARCHAR)+' THEN SVALUE ELSE '''' END) AS T'+CAST(TV AS VARCHAR)
FROM 
(SELECT TV FROM #T GROUP BY TV) AS A
ORDER BY TV

SET @SQL='SELECT NAME,'+@SQL+' FROM #T GROUP BY NAME'

EXEC(@SQL)

kanmaogou 2019-06-12
  • 打赏
  • 举报
回复
引用 1 楼 RINK_1 的回复:
你这个比上个帖子简单一点,就用上个帖子的#2或者#3的方法就可以了,只是把里面MAX函数里的取TV字段改成取SVALUE字段就可以了。
试过了,达不到效果 svalue 是不定的,TV字段的范围是【1-7】
RINK_1 2019-06-12
  • 打赏
  • 举报
回复
你这个比上个帖子简单一点,就用上个帖子的#2或者#3的方法就可以了,只是把里面MAX函数里的取TV字段改成取SVALUE字段就可以了。

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧