September 18, 2015 at 6:04 am
Hi.
I have created a query for pivot in SQL 2008.
where :
the column 'description' will be the column header
the column 'name' will be at the row; and
total number of entries for each name
here is the query I used.
SELECT *
FROM (
SELECT name, description
FROM student where CONVERT(VARCHAR(10),enrolment,120) BETWEEN '2015-09-01' AND '2015-09-18'
) e
PIVOT (
COUNT(description)
FOR description in
([First Year],[Second Year],[Third Year],[Fourth Year])
) p
The value for the header is hard coded.
How can I make it dynamic?
I want the header to accommodate all 'description' if ever I added a new value for 'description'.
Let's say, I will add 'NULL' and 'Fifth Year' at column description.
How can my query be dynamic to accommodate the changes without altering the query all over again?
I have tried resources at the net to make it dynamic, but just resulted to several errors.
Here's the sample result I want for the dynamic query 🙂
name First Year Second Year Third Year ....( and so on)
student A 3 1 7
student B 7 3 6
student C 2 5 2
Thanks a lot 🙂
September 18, 2015 at 6:33 am
The following article will show you how to work with dynamic pivots and cross tabs. http://qa.sqlservercentral.com/articles/Crosstab/65048/
I usually use a different method for concatenation than the one explained in the article. This concatenation method is explained in here: http://qa.sqlservercentral.com/articles/comma+separated+list/71700/
Here's an example that you might need to tweak to get the results as desired, but I'm missing information. Note that I'm including sample data in a way that anyone can run the code and it will work without effort. You're expected to do the same.
CREATE TABLE student(
name varchar(100),
description varchar(100),
enrolment date)
INSERT INTO student
VALUES
( 'Mario', 'First Year', '20150915'),
( 'Luigi', 'First Year', '20150915'),
( 'Peach', 'Second Year', '20150915'),
( 'Toad', 'Fourth Year', '20150915');
DECLARE @pStartDate date = '2015-09-01',
@pEndDate date = '2015-09-18'
--Your original query
SELECT *
FROM (
SELECT name, description
FROM student where CONVERT(VARCHAR(10),enrolment,120) BETWEEN @pStartDate AND @pEndDate
) e
PIVOT (
COUNT(description)
FOR description in
([First Year],[Second Year],[Third Year],[Fourth Year])
) p;
--My solution
DECLARE @SQL nvarchar(max);
WITH CTE AS(
--Change this if you want all the descriptions
SELECT DISTINCT description
FROM student
WHERE enrolment BETWEEN @pStartDate AND @pEndDate -- DON'T USE FUNCTIONS ON YOUR COLUMNS IN A WHERE CLAUSE!!!!!!
)
SELECT @SQL =
'SELECT ISNULL( name, ''Total'') AS name ' + CHAR(10)
+( SELECT CHAR(9) + ',COUNT( CASE WHEN description = ' + QUOTENAME(description, '''') + ' THEN description END) AS ' + QUOTENAME(description) + CHAR(10)
FROM CTE
--Add an ORDER BY here if appropriate
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
+ ' ,COUNT(description) AS Total ' + CHAR(10) --For a Total column
+ ' FROM student ' + CHAR(10)
+ ' WHERE enrolment BETWEEN @pStartDate AND @pEndDate ' + CHAR(10)
+ ' GROUP BY name '
+ ' WITH ROLLUP ' --For a Total row
+ '; ';
PRINT @SQL;
EXEC sp_executesql @SQL, N'@pStartDate date, @pEndDate date', @pStartDate, @pEndDate;
GO
DROP TABLE student
EDIT: Added total row and total column.
September 18, 2015 at 7:04 am
Thanks a lot Luis.
I have tried this query and added several new description ( just to see if the header will be dynamic 🙂 )
and it works!
September 18, 2015 at 7:11 am
You're welcome. Be sure to understand it and ask any questions that you might have.
I added an additional row and column to show totals.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply