MSSQL行转换成列查询

2019-01-11 03:24:45 933 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

MSSQL --- MSSQL体系结构一览声明笔记内容为个人学习整理，绝大部分内容来源于网络。文章内含有自己的总结、拓展与试验，仅作为个人学习资料，转载请联系原作者。文章末尾有含原文出处，本文尚未获得所有的原作者授权，但是我不要脸。在接触一个新数据库的时候，深入了解RDBMS的系结构可以更好的理解SQL语句的执行过程，分析各种问题。阅读详情

各位大哥请教下：MSSQL中原有表（表名称CST）内容如下图所示

怎样查询使得行转换成列，使得显示结果如下图所示？？

...全文

【生成数据集EXCEL文件】使用生成对抗网络GAN生成数据集：输出生成数据集EXCEL 生成器的任务是接收随机噪声向量作为输入，并尽可能生成与真实数据相似的样本，而判别器则是一个二分类器，旨在区分输入的样本是来自生成器生成的，还是来自真实数据。在训练过程中，生成器和判别器通过对抗学习的方式相互博弈，生成器的目标是生成足以欺骗判别器的样本，而判别器的目标则是尽可能准确地判断样本的真实性。GAN网络在多个领域展现了强大的能力，能够生成与训练数据分布相似的新数据，包括但不限于图像生成、图像风格转换、超分辨率、数据增强、视频生成、自然语言处理、医学图像处理以及游戏与虚拟现实等。阅读详情

qqww09633

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

RINK_1 2019-01-22

打赏
举报

引用 5 楼 qqww09633 的回复:

> [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]关键字 'with' 附近有语法错误。如果此语句是公用表表达式、xmlnamespaces 子句或者更改跟踪上下文子句，那么前一个语句必须以分号结尾。 (319) [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]“,”附近有语法错误。 (102)

把你写的语句放上来看看

qqww09633 2019-01-22

打赏
举报

> [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]关键字 'with' 附近有语法错误。如果此语句是公用表表达式、xmlnamespaces 子句或者更改跟踪上下文子句，那么前一个语句必须以分号结尾。 (319)
[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]“,”附近有语法错误。 (102)

RINK_1 2019-01-15

打赏
举报


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

CREATE TABLE #T
(CSTID VARCHAR(20),
 DATE VARCHAR(20),
 RESULT VARCHAR(20),
 COUNT VARCHAR(5),
 LEFT_1_01 DECIMAL(12,4),
 LEFT_1_02 DECIMAL(12,4),
 LEFT_1_03 DECIMAL(12,4))

INSERT INTO #T
SELECT 'CHA052','2019-01-07 9:51','NG',1,10.2577,93.9259,173.9259 UNION ALL
SELECT 'THA298','2019-01-07 9:25','NG',1,9.594,92.5866,173.2533 UNION ALL
SELECT 'THF006','2019-01-07 8:50','NG',1,9.2562,92.9155,172.9155 UNION ALL
SELECT 'CHA011','2019-01-07 8:22','NG',1,10.9303,94.2914,174.2518

DECLARE @SQL VARCHAR(MAX)

WITH CTE_1
AS
(SELECT 'CSTID' AS CSTID,'DATE' AS [DATE],'RESULT' AS RESULT,'COUNT' AS COUNT,
'LEFT_1_01' AS LEFT_1_01,'LEFT_1_02' AS LEFT_1_02,'LEFT_1_03' AS LEFT_1_03,0 AS SEQ
 UNION ALL
SELECT CSTID,CAST(DATE AS VARCHAR(20)),RESULT,CAST(COUNT AS VARCHAR(20)),
CAST(LEFT_1_01 AS VARCHAR(20)),CAST(LEFT_1_02 AS VARCHAR(20)),CAST(LEFT_1_03 AS VARCHAR(20)),
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SEQ FROM #T),

CTE_2
AS
(SELECT SEQ,VALUE,
ROW_NUMBER() OVER (PARTITION BY SEQ ORDER BY (SELECT 1)) AS SEQ_2
 FROM CTE_1
 UNPIVOT
 ([VALUE] FOR [NAME] IN ([CSTID],[DATE],[RESULT],[COUNT],[LEFT_1_01],[LEFT_1_02],[LEFT_1_03]))B)
 
  SELECT @SQL=ISNULL(@SQL+',','')+
 'MAX(CASE WHEN SEQ='''+CAST(SEQ AS VARCHAR)+''' THEN VALUE ELSE '''' END) AS '+'NAME_'+CAST(SEQ AS VARCHAR)
 FROM (SELECT DISTINCT SEQ FROM CTE_2) AS A
 
 SELECT @SQL='WITH CTE_1
AS
(SELECT ''CSTID'' AS CSTID,''DATE'' AS [DATE],''RESULT'' AS RESULT,''COUNT'' AS COUNT,
''LEFT_1_01'' AS LEFT_1_01,''LEFT_1_02'' AS LEFT_1_02,''LEFT_1_03'' AS LEFT_1_03,0 AS SEQ
 UNION ALL
SELECT CSTID,CAST(DATE AS VARCHAR(20)),RESULT,CAST(COUNT AS VARCHAR(20)),
CAST(LEFT_1_01 AS VARCHAR(20)),CAST(LEFT_1_02 AS VARCHAR(20)),CAST(LEFT_1_03 AS VARCHAR(20)),
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SEQ FROM #T),

CTE_2
AS
(SELECT SEQ,VALUE,
ROW_NUMBER() OVER (PARTITION BY SEQ ORDER BY (SELECT 1)) AS SEQ_2
 FROM CTE_1
 UNPIVOT
 ([VALUE] FOR [NAME] IN ([CSTID],[DATE],[RESULT],[COUNT],[LEFT_1_01],[LEFT_1_02],[LEFT_1_03]))B)
 
SELECT SEQ_2,'+@SQL+' FROM CTE_2 GROUP BY SEQ_2'

EXEC(@SQL)

qqww09633 2019-01-13

打赏
举报

不会写

Csdn技术大神 2019-01-12

打赏
举报

重新输入一段代码试试

射手座cl 2019-01-11

打赏
举报

查询的话可以用编程语言写个方法把

MSSQL行转换成列查询

相关推荐