SQL dynamic pivot for goals

Question

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.

Take a look at the example [here](https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx). I think you may have a problem if the number of goals doesn't have a maximum. — Johnny Bones, Feb 11 '16 at 18:42
hi bones ,in the above example he is using count of weeks to group, but in my case filed goal is varchar — kiransr, Feb 11 '16 at 19:02
It's not going to answer your question specifically, but until someone else chimes in you can probably adapt it to what you're doing and find your own answer. It beats waiting around hitting "Refresh". :o) — Johnny Bones, Feb 11 '16 at 19:05
I'm wondering why the EmpCode in the second line of your expected output is `101` and not `102` . — TT., Feb 11 '16 at 19:56
@bones .. Thanks for ur comments.. :-) I am new to this concept so not able to map my requirement with existing example wic u have mentioned — kiransr, Feb 12 '16 at 03:06

score 0 · Accepted Answer · answered Feb 11 '16 at 21:25

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;

Result:

+---------+---------+------------+----------+---------+
| EmpCode | Empname |   Goal1    |  Goal2   |  Goal3  |
+---------+---------+------------+----------+---------+
|     101 | kiran   | readsql    | coding   | readcss |
|     102 | rohit   | coding     | readjava | NULL    |
|     103 | pradi   | do nothing | NULL     | NULL    |
+---------+---------+------------+----------+---------+

SQL dynamic pivot for goals

1 Answers1

Linked