sqlserver sql行专列_SQLSERVER行转列的2种实现方式

IF(OBJECT_ID('tbl_bigcode_pivot') IS NOT NULL)

BEGIN

DROP TABLE dbo.tbl_bigcode_pivot

END

GO

CREATE TABLE tbl_bigcode_pivot

(

[像素编号] INT,

[波段] INT,

[像素值] FLOAT

)

INSERT INTO tbl_bigcode_pivot

SELECT 400 [像素编号], 1 [波段],0.021347 [像素值]

UNION ALL

SELECT 400 [像素编号], 2 [波段],0.021195 [像素值]

UNION ALL

SELECT 400 [像素编号], 3 [波段],0.020949 [像素值]

UNION ALL

SELECT 401 [像素编号], 1 [波段],0.021366 [像素值]

UNION ALL

SELECT 401 [像素编号], 3 [波段],0.020968 [像素值]

UNION ALL

SELECT 402 [像素编号], 1 [波段],0.021378 [像素值]

UNION ALL

SELECT 402 [像素编号], 2 [波段],0.021226 [像素值]

UNION ALL

SELECT 402 [像素编号], 3 [波段],0.02098 [像素值]

SELECT * FROM tbl_bigcode_pivot

--CASE方式实现

SELECT [像素编号],

MAX(CASE [波段] WHEN 1 THEN [像素值] ELSE 0 END) [1],

MAX(CASE [波段] WHEN 2 THEN [像素值] ELSE 0 END) [2],

MAX(CASE [波段] WHEN 3 THEN [像素值] ELSE 0 END) [3]

FROM tbl_bigcode_pivot GROUP BY [像素编号]

--PIVOT函数实现

SELECT [像素编号],CONVERT(DECIMAL(18,6),ISNULL([1],0)) AS [1],CONVERT(DECIMAL(18,6),ISNULL([2],0)) AS [2],CONVERT(DECIMAL(18,6),ISNULL([3],0)) AS [3] FROM dbo.tbl_bigcode_pivot PIVOT(MAX([像素值]) FOR [波段] IN([1],[2],[3])) dtpiv