目标的SQL动态支点 [英] SQL dynamic pivot for goals
本文介绍了目标的SQL动态支点的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有一个包含以下行和列的表
Let's assume I have a table with the following rows and columns
EmpCode Empname Goals
------- ---- ------
101 kiran readsql
101 kiran coding
101 kiran readcss
102 rohit coding
102 rohit readjava
103 pradi do nothing
我想以以下格式显示表格上方
I want to display above table in below format:
EmpCode Empname Goal1 Goal2 Goal3
------- ---- ------ ------ ------
101 kiran readsql coding readcss
101 rohit coding readjava
103 pradi do nothing
实地目标是动态的,请帮帮我.谢谢你.
The field goals is dynamic, please help me out. Thank you.
推荐答案
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,'kiran','coding'),
(101,'kiran','readcss'),
(102,'rohit','coding'),
(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,1,''
);
DECLARE @stmt NVARCHAR(MAX)=N'
SELECT *
FROM (
SELECT
EmpCode,Empname,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 |
+---------+---------+------------+----------+---------+
这篇关于目标的SQL动态支点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文