参见英文答案 >
Convert Rows to columns using ‘Pivot’ in SQL Server7个
假设我有一个包含以下行和列的表
假设我有一个包含以下行和列的表
EmpCode Empname Goals ------- ---- ------ 101 kiran readsql 101 kiran coding 101 kiran readcss 102 rohit coding 102 rohit readjava 103 pradi do nothing
我想以下面的格式显示上表：
EmpCode Empname Goal1 Goal2 Goal3 ------- ---- ------ ------ ------ 101 kiran readsql coding readcss 101 rohit coding readjava 103 pradi do nothing
实地目标是动态的,请帮助我.谢谢.
解决方法
CREATE TABLE #tt(id INT IDENTITY(1,1) PRIMARY KEY,EmpCode INT,Empname VARCHAR(2566),Goals VARCHAR(256));
INSERT INTO #tt(EmpCode,Empname,Goals)VALUES
(101,'kiran','readsql'),(101,'coding'),'readcss'),(102,'rohit','readjava'),(103,'pradi','do nothing');
DECLARE @goal_cols NVARCHAR(MAX)=STUFF((
SELECT DISTINCT N',Goal'+CAST(ROW_NUMBER()OVER(PARTITION BY empcode ORDER BY id) AS VARCHAR(3))
FROM #tt
FOR XML PATH('')
),1,''
);
DECLARE @stmt NVARCHAR(MAX)=N'
SELECT *
FROM (
SELECT
EmpCode,Goals,goal_id=''Goal''+CAST(ROW_NUMBER()OVER(PARTITION BY empcode ORDER BY id) AS VARCHAR(3))
FROM
#tt
) AS s
PIVOT(MAX(Goals) FOR goal_id IN ('+@goal_cols+')) AS p
ORDER BY EmpCode;';
EXECUTE sp_executesql @stmt;
DROP TABLE #tt;
结果：
+---------+---------+------------+----------+---------+ | EmpCode | Empname | Goal1 | Goal2 | Goal3 | +---------+---------+------------+----------+---------+ | 101 | kiran | readsql | coding | readcss | | 102 | rohit | coding | readjava | NULL | | 103 | pradi | do nothing | NULL | NULL | +---------+---------+------------+----------+---------+
