0

I Have a SQL Table as shown below,

| Loc | Date | Id | Sts |
-------------------------
|   Hyd |   15-01-2016  |   1   |   A   |

|   Vjd |   16-01-2016  |   2   |   B   |

|   Viz |   15-01-2016  |   3   |   C   |

|   Hyd |   15-03-2016  |   4   |   A   |

|   Vjd |   15-03-2016  |   5   |   B   |

|   Viz |   15-03-2016  |   6   |   C   |

|   Hyd |   15-03-2016  |   4   |   A   |

|   Vjd |   15-05-2016  |   5   |   B   |

|   Viz |   15-05-2016  |   6   |   C   |

And i need output like,

**| Loc | Jan-16    |   Mar-16  |   May-16  |**  
**|-------|A |B |C |A |B |C |A |B |C |**  

----------
|Hyd | 1 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 |    
|Vjd | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | 0 |  
|Viz | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 1 |

Can anyone help me out please..

Thanks in Advance.

Raj
  • 10,653
  • 2
  • 45
  • 52
Raja A
  • 53
  • 1
  • 7
  • Look for `PIVOT` to get A,B and C *side-by side*. Then you can use *conditional aggregation* (`GROUP BY` with `MAX(CASE WHEN...)`) to set groups of *ABCs* *side-by-side*. What you cannot achieve with a simple query are your *meta captions* `**| Loc | Jan-16 | Mar-16 | May-16 |**` – Shnugo Jan 12 '17 at 11:20
  • Try this..From the question you asked previously.. http://stackoverflow.com/questions/40131177/how-to-create-a-sql-query-to-count-items-grouped-and-concatenated-by-date – Shakeer Mirza Jan 12 '17 at 11:22
  • I Tried this (Replace # with @) DECLARE #DynamicPivotQuery AS NVARCHAR(MAX) DECLARE #ColumnName AS NVARCHAR(MAX) --Get distinct values of the PIVOT Column SELECT #ColumnName= ISNULL(#ColumnName + ',','') + QUOTENAME([Date]) FROM (SELECT DISTINCT [Date] FROM [Sample]) AS [Date] order by [Date] --Prepare the PIVOT query using the dynamic SET #DynamicPivotQuery = N'SELECT [Loc], ' + #ColumnName + ' FROM [Sample] PIVOT(SUM(Count(*)) FOR [Date] IN (' + #ColumnName + ')) AS PVTTable' --Execute the Dynamic Pivot Query EXEC sp_executesql #DynamicPivotQuery – Raja A Jan 12 '17 at 12:08

1 Answers1

2

You will basically need to aggregate based on CASE statements, like this:

DECLARE @table TABLE (loc VARCHAR(3), [date] DATE, id INT, sts CHAR(1));
INSERT INTO @table SELECT 'Hyd', '20160115', 1, 'A';
INSERT INTO @table SELECT 'Vjd', '20160116', 2, 'B';
INSERT INTO @table SELECT 'Viz', '20160115', 3, 'C';
INSERT INTO @table SELECT 'Hyd', '20160315', 4, 'A';
INSERT INTO @table SELECT 'Vjd', '20160315', 5, 'B';
INSERT INTO @table SELECT 'Viz', '20160315', 6, 'C';
INSERT INTO @table SELECT 'Hyd', '20160315', 4, 'A';
INSERT INTO @table SELECT 'Vjd', '20160515', 5, 'B';
INSERT INTO @table SELECT 'Viz', '20160515', 6, 'C';

SELECT
    loc,
    COUNT(CASE WHEN YEAR([date]) = 2016 AND MONTH([date]) = 1 AND sts = 'A' THEN 1 END) AS Jan_A,
    COUNT(CASE WHEN YEAR([date]) = 2016 AND MONTH([date]) = 1 AND sts = 'B' THEN 1 END) AS Jan_B,
    COUNT(CASE WHEN YEAR([date]) = 2016 AND MONTH([date]) = 1 AND sts = 'C' THEN 1 END) AS Jan_C,
    COUNT(CASE WHEN YEAR([date]) = 2016 AND MONTH([date]) = 3 AND sts = 'A' THEN 1 END) AS Mar_A,
    COUNT(CASE WHEN YEAR([date]) = 2016 AND MONTH([date]) = 3 AND sts = 'B' THEN 1 END) AS Mar_B,
    COUNT(CASE WHEN YEAR([date]) = 2016 AND MONTH([date]) = 3 AND sts = 'C' THEN 1 END) AS Mar_C,
    COUNT(CASE WHEN YEAR([date]) = 2016 AND MONTH([date]) = 5 AND sts = 'A' THEN 1 END) AS May_A,
    COUNT(CASE WHEN YEAR([date]) = 2016 AND MONTH([date]) = 5 AND sts = 'B' THEN 1 END) AS May_B,
    COUNT(CASE WHEN YEAR([date]) = 2016 AND MONTH([date]) = 5 AND sts = 'C' THEN 1 END) AS May_C
FROM
    @table
GROUP BY
    loc;

Results:

loc Jan_A   Jan_B   Jan_C   Mar_A   Mar_B   Mar_C   May_A   May_B   May_C
Hyd     1       0       0       2       0       0       0       0       0
Viz     0       0       1       0       0       1       0       0       1
Vjd     0       1       0       0       1       0       0       1       0
Richard Hansell
  • 5,315
  • 1
  • 16
  • 35
  • Thanks Richard, but this has to be done dynamically, all the column names should be dynamic and, what i have given is just a sample data. – Raja A Jan 12 '17 at 11:28