SQL动态枢纽为目标

问题描述：

假设我有一个表下面的行和列SQL动态枢纽为目标

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

的场均进球是动态的，请帮助我。谢谢。

看一看的例子[这里]（https://technet.microsoft.com/en-us/library/ms177410（V = SQL.105）。 ASPX）。如果目标数量没有最大限度，我想你可能会遇到问题。 –

嗨骨头，在上面的例子中，他使用了几个星期来分组，但在我的情况下，提交的目标是varchar – kiransr

它不会专门回答你的问题，但是直到别人在你身上发出响声才可能适应它，重新做，找到你自己的答案。它击败“刷新”等待。：o） –

答

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 ('[email protected]_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 | 
+---------+---------+------------+----------+---------+

真棒...谢谢TT :-) – kiransr

SQL动态枢纽为目标

相关推荐