MSSQL行转换成列查询

[qqww09633]

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

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

[射手座cl]

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

[Csdn技术大神]

重新输入一段代码试试

[qqww09633]

不会写

[RINK_1]

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]

> [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]

引用 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)

把你写的语句放上来看看