How to create a SQL query to count items grouped and concatenated by date?

Question

Please help me to get this output.

My Input:

-----------------------------------------------------
EMP_Id |    EMP_Name |  Date_Worked             |   Enc_No
---------------------------------------------------
100    |    Aj       |  2016-10-10 00:00:00.000 |   123
100    |    Aj       |  2016-10-10 00:00:00.000 |   124
101    |    Siv      |  2016-10-10 00:00:00.000 |   125
101    |    Siv      |  2016-10-11 00:00:00.000 |   126
102    |    Nav      |  2016-10-10 00:00:00.000 |   127
102    |    Nav      |  2016-10-11 00:00:00.000 |   128    
--------------------------------------------------------

I need output like this

---------------------------------------------------------
EmplyeeId | EmplyeeName | 10/10/2016 | 11/10/2016
---------------------------------------------------------
100       | Aj          | 2          |  0
101       | Siv         | 1          |  1
102       | Nav         | 1          |  1
---------------------------------------------------------

Use Pivot Table : http://stackoverflow.com/a/39569696/6606630 — Sandip - Frontend Developer, Oct 19 '16 at 13:09
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Sean Lange, Oct 19 '16 at 13:33
Glad i got my work done. Please checkout this link to know how. — Raja A, Oct 19 '16 at 13:59
http://stackoverflow.com/questions/27422109/how-to-pivot-dynamically-with-date-as-column — Raja A, Oct 19 '16 at 13:59

Chanukya · Answer 1 · 2016-10-19T13:34:44.710

1

 SELECT * FROM #A
 PIVOT
(
 count(Enc_No) 
  FOR DATE_WORKED IN([2016-10-10],[2016-10-11])
) PIV;

edited Oct 19 '16 at 13:34

answered Oct 19 '16 at 13:12

Chanukya

5,833
1
22
36

Please note , here date column is not fixed. – Raja A Oct 19 '16 at 13:16
can u explain y u gave down vote @RajaA – Chanukya Oct 19 '16 at 13:35
http://stackoverflow.com/questions/27422109/how-to-pivot-dynamically-with-date-as-column – Raja A Oct 19 '16 at 13:59
with this answer u will not satify ? and for kind of information we can write the query multiple ways....@RajaA – Chanukya Oct 20 '16 at 04:31
I asked y u gave negative mark ...@RajaA – Chanukya Oct 20 '16 at 04:32

score 0 · Answer 2 · answered Oct 19 '16 at 14:41

I think you are talking about dynamic pivot . I implemented dynamic pivot in my project .I give you sample script as per your input .Please check it . I am sure it will be helpful for you .

Here EmployeeWork is a TableName .

DECLARE @FactorText varchar(max)=null

SELECT @FactorText = COALESCE(@FactorText + '], ', '') + CAST('[' + FactorText AS VARCHAR(50)) 
                     FROM (SELECT DISTINCT CONVERT(varchar, Date_Worked, 103) as FactorText FROM EmployeeWork) AS EmployeeWork

                    SET  @FactorText =  @FactorText + ']'

DECLARE @DynamicPIVOT AS VARCHAR(MAX)

                SELECT @DynamicPIVOT = 'SELECT EMP_Id,emp,' + @FactorText + ' FROM ( select EMP_Id , EMP_Name as emp, EMP_Name
                as AppliedValue,  CONVERT(varchar, Date_Worked, 103) as FactorText FROM EmployeeWork
                ) EmployeeWorkData

                PIVOT
                      (
                   count(AppliedValue) FOR FactorText IN (' + @FactorText + ')
                      ) Result;'


EXEC (@DynamicPIVOT)

If any query then tells me.As per your requirement, it's working perfectly.

Thanks .

How to create a SQL query to count items grouped and concatenated by date?

2 Answers2

Linked