ID Email key value
1 aa@gmail.com LastName sdf
2 362643900@qq.com LastName xu
3 rclab_1014@ybb.ne.jp FirstName 浜田
3 rclab_1014@ybb.ne.jp LastName 隆史
我想得到的结果是
ID Email FirstName LastName
1 aa@gmail.com sdf
2 362643900@qq.com xu
3 rclab_1014@ybb.ne.jp 浜田 隆史
我查了下 谷歌都是聚合函数 用max 等 都是datetime,int类型的才可以
我想知道nvarchar类型的怎么转
------解决思路----------------------
IF OBJECT_ID('tempdb..#test','U') IS NOT NULL DROP TABLE #test
CREATE TABLE #test
(
ID INT
,Email VARCHAR(20)
,KEYS VARCHAR(20)
,VALUE NVARCHAR(20)
)
INSERT INTO #test
SELECT 1, 'aa@gmail.com', 'LastName', 'sdf' UNION ALL
SELECT 2, '362643900@qq.com', 'LastName', 'xu' UNION ALL
SELECT 3, 'rclab_1014@ybb.ne.jp', 'FirstName', '浜田' UNION ALL
SELECT 3, 'rclab_1014@ybb.ne.jp', 'LastName', '隆史'
SELECT ID
,ISNULL(FirstName,'') AS FirstName
,ISNULL(LastName,'') AS LastName
FROM #test AS A
PIVOT (MAX(VALUE) FOR KEYS IN(FirstName,LastName))pt
/*
ID Email FirstName LastName
2 362643900@qq.com xu
1 aa@gmail.com sdf
3 rclab_1014@ybb.ne.jp 浜田 隆史
*/