在Microsoft SQL Server中将表行转换为列

Question

这是我的表格:

<表类> ID 参数 阅读 tbody><<tr>1213Sr。11213Sr。(null)1213Sr。(null)1213名称(null)1213名称最终评级1213名称多数评级1214Sr。11214Sr。(null)1214Sr。(null)1214名称(null)1214名称最终评级1214名称多数评级

您的数据

CREATE TABLE mytable(
ID        INTEGER  NOT NULL
,Parameter VARCHAR(70) NOT NULL
,Reading   VARCHAR(70) NOT NULL
);
INSERT INTO mytable
(ID,Parameter,Reading) VALUES 
(1213,'Sr No.','1'),
(1213,'Sr No.','(null)'),
(1213,'Sr No.','(null)'),
(1213,'Name','(null)'),
(1213,'Name','Final Rating'),
(1213,'Name','Majority Rating'),
(1214,'Sr No.','1'),
(1214,'Sr No.','(null)'),
(1214,'Sr No.','(null)'),
(1214,'Name','(null)'),
(1214,'Name','Final Rating'),
(1214,'Name','Majority Rating');

在你的Subquery中使用Row_number和Join来得到你想要的结果

SELECT A.id,
[sr no.],
[name]
FROM   (SELECT id,
reading         AS 'Sr No.',
Row_number()
OVER(
ORDER BY (SELECT NULL)) RN
FROM   mytable
WHERE  parameter = 'Sr No.') A,
(SELECT id,
reading         AS 'Name',
Row_number()
OVER(
ORDER BY (SELECT NULL)) RN
FROM   mytable
WHERE  parameter = 'Name') B
WHERE  A.id = B.id
AND A.rn = B.rn
AND A.id = 1213    

dbfiddle