Tukukkk

I have below table:

------------------------------------------------------

| Id    Code  percentage  name  name1   activity     |

-----------------------------------------------------

| 1   Prashant  43.43    James  James_  Running      |

| 1   Prashant  70.43    Sam    Sam_    Cooking      |

| 1   Prashant  90.34    Lisa   Lisa_   Walking      |

| 1   Prashant  0.00     James  James_  Stealing     |

| 1   Prashant  0.00     James  James_  Lacking      |

| 1   Prashant  73       Sam     Sam_   Cooking 1    |

------------------------------------------------------

1) The problem is, due to MAX function it is ignoring 0.00 value with Column name Name1 of Lacking

2) Expected Result:

-------------------------------------------------------------------

Id  Code        James    James_  Sam        Sam_    Lisa      Lisa_

-------------------------------------------------------------------

1   Prashant    Running  43.43  Cooking     3.43    Walking   90.34

1   Prashant    Stealing 0.0    Cooking 1   73      NULL      NULL

1   Prashant    Lacking  0.0    NULL        NULL    NULL      NULL

-------------------------------------------------------------------

The PIVOT Query what I tried:

DECLARE @DynamicPivotQuery NVARCHAR(MAX)



SET @DynamicPivotQuery  = N'SELECT Id,Code,James,James_,Sam,Sam_,Lisa,Lisa_

    INTO ##TempPivot 

    FROM A

    PIVOT(MAX(activity)

          FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT

          (

          MAX(percentage)

          FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1'





EXECUTE(@DynamicPivotQuery) 



SELECT * 

INTO #RESULT 

FROM ##TempPivot





SELECT * 

FROM #RESULT

Sample query to generate data:

CREATE TABLE A

(

  Id NVARCHAR(10),

  Code NVARCHAR(MAX),

  percentage NVARCHAR(MAX),

  name NVARCHAR(MAX),

  name1 NVARCHAR(MAX),

  activity NVARCHAR(MAX)

)





INSERT INTO A VALUES (1,'Prashant',43.43,'James','James_','Running')

INSERT INTO A VALUES (1,'Prashant',3.43,'Sam','Sam_','Cooking')

INSERT INTO A VALUES (1,'Prashant',90.34,'Lisa','Lisa_','Walking')

INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Stealing')

INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Lacking')

INSERT INTO A VALUES (1,'Prashant',73,'Sam','Sam_','Cooking 1')

If you add a ROW_NUMBER() into the mix, your pivot will be able to retain the association between activities and percentages.

;with cte as 

(

    select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM

    from A

),

cte2 as

(

    SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_

    FROM cte

    PIVOT(MAX(activity)

          FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT

          (

          MAX(percentage)

          FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1

)

select Id, Code, MAX(James) James, MAX(James_) James_, MAX(Sam) Sam, MAX(Sam_) Sam_, MAX(Lisa) Lisa, MAX(Lisa_) Lisa_

from cte2

group by Id, Code, ROWNUM

Returns:

Id  Code        James       James_  Sam         Sam_    Lisa    Lisa_

1   Prashant    Running     43.43   Cooking 1   73      Walking 90.34

1   Prashant    Stealing    0.00    Cooking     3.43    NULL    NULL

1   Prashant    Lacking     0.00    NULL        NULL    NULL    NULL

The idea is, in thefirst common table expression, you transform the A table to this:

Id  Code        percentage  name    name1   activity    ROWNUM

1   Prashant    43.43       James   James_  Running     1

1   Prashant    0.00        James   James_  Stealing    2

1   Prashant    0.00        James   James_  Lacking     3

1   Prashant    90.34       Lisa    Lisa_   Walking     1

1   Prashant    73          Sam     Sam_    Cooking 1   1

1   Prashant    3.43        Sam     Sam_    Cooking     2

And throughout the remaining query, the ROWNUM column just acts to bind the percentage value to the activity.

Making it dynamic is easy once you have a working query. Just replace all the dynamic parts (in this case, comma-delimited lists of names, right?) with variables. Something like this:

declare @sql nvarchar(max)

declare @name_concat nvarchar(max)

declare @name1_concat nvarchar(max)

declare @select_aggs nvarchar(max)

select @name_concat = STUFF((select distinct ',' + quotename(name) from A order by 1 for xml path('')), 1, 1, '')

select @name1_concat = STUFF((select distinct ',' + quotename(name1) from A order by 1 for xml path('')), 1, 1, '')



;with cte_all_names as (

    select name from A

    union all 

    select name1 from A

)

select @select_aggs = STUFF((select distinct ',MAX(' + quotename(name) + ') ' + quotename(name) from cte_all_names order by 1 for xml path('')), 1, 1, '')



select @sql = '

;with cte as 

(

    select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM

    from A

),

cte2 as

(

    SELECT Id,Code,ROWNUM,' + @name_concat + ',' + @name1_concat + '

    FROM cte

    PIVOT(MAX(activity)

          FOR name IN (' + @name_concat + ')) AS PVTTable PIVOT

          (

          MAX(percentage)

          FOR name1 IN (' + @name1_concat + ')) AS PVTTable1

)

select Id, Code, ' + @select_aggs + '

from cte2

group by Id, Code, ROWNUM

'



exec sp_executesql @sql