Start Free Trial

asked on

Display data from a sql table in a Pivot table view using SQL Server 2005

Hello, I manually create a matrix using excel to dertermine what after school acitivity each week students signed up for and are are doing.

I have SQL server 2005 management studio express and understand that I can create a Pivot table to do what I am doing manually, bt am not having much luck with an example that I can use as a reference to build the pivot table in the SQL query editor. Please share your suggestions.

tblStudentDetail
stuDetailID - int
ActivityID - int
Student Name - nvarchar

stuDetailID StudentName ActivityID

1 Jim Barns 1222
2 Tom Fern 2222
3 Frank Miller 3333
4 Dan Hompkins 4444
5 Terry Lewis 1222
6 Jim Barns 3333
7 Tom Fern 15
8 Frank Miller 234
9 Dan Hompkins 2345
10 Terry Lewis 4567
11 Peter Fry 4444
12 Tom Fern 5566
13 Frank Miller 2222
14 Dan Hompkins 2568
15 Mike Brown 4555

I am trying to accomplish this pivot table look in sql server 2005

Student Name (RowName)
ActivityID (Column Name)

Student Name 15 234 1222 2222 2345 2568 3333 4444 4555 4567 5566

Jim Barns X X
Tom Fern X X X
Frank Miller X X X
Dan Hompkins X X X
Terry Lewis X X
Peter Fry X
Mike Brown X

Thank you in advance,

You can do this in SQL 2005 using a PIVOT statement; however, PIVOT needs hardcoded columns to pivot by. One way around this is to use dynamic SQL.

Since you have been using Excel anyway, I would suggest you consider using Excel's data import functionality to bring back your data as show in previous statement as one sheet in your excel for raw data and then use that as the source for a PIVOT report/table in Excel.

If you have a finite list of activityid values, I can show you how to do with PIVOT.

It would look like this. To do in dynamic SQL, you would build the column list into variable by selecting your distinct activity ID's from tblStudentDetail and then build a sql string in a variable like @sql to look like below select statement then use:

EXECUTE(@sql)

SELECT StudentName,  
[15],[234],[1222],[2222],[2345],[2568],[3333],[4444],[4555],[4567],[5566]
FROM tblStudentDetail
PIVOT (COUNT(stuDetailID) FOR ActivityID IN ([15],[234],[1222],[2222],[2345],[2568],[3333],[4444],[4555],[4567],[5566])) p

Open in new window

ASKER

Correct me If I am wrong,

Even if I have a table of 4000 activity codes [ActivityID (Column Name)] and I wanted to use a PIVOT statement I will have to hardcod the columns. Wow!

Could you please share with me how you would use dynamic SQL using the example above. The table listed above is dissected because all the fields would not fit.

If you have 4000 activity codes, yes this method sucks for that. I would stick with porting data to Excel and then using Excel's PIVOT to dynamically create the pivot based on the actual data which may or may not have all 4000 codes in it.

For dynamic SQL, you would do something like this:
(you may run into limitations on the amount of characters, so I have tried to trim out unnecessary spaces in this code, but you can further save ~8000 characters by eliminating the [ ] from around the ActivityID (2 chars x 4000 ids) as I think technically it will function without it -- just use to putting that to remind myself those are activing as columns)

DECLARE @activityids NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
 
SELECT @activityids = ISNULL(@activityids + ',', '') + '[' + ActivityID + ']'
FROM tblStudentDetail
GROUP BY ActivityID
 
SET @sql = 'SELECT * FROM tblStudentDetail PIVOT (COUNT(stuDetailID) FOR ActivityID IN (' + @activityids + ')) p'
 
EXECUTE(@sql)

Open in new window

Alternative, would be to have mutiple pieces of sql. You can get TOP 25 PERCENT or whatever works to fit data and then do something like this.

EXECUTE(@sql1 + @sql2 + @sql3 + @sql4)

That works too, just make sure the concatenation yields same SQL as above. You can use PRINT(@sql) to preview it before running.

ASKER

Thank you, for the direction. I will let you know how it turns out.

ASKER

I receive the following error: Any Suggestions?

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value ']' to data type int.

Thank you in advance

Sorry, it is because it is trying to ADD the values as numbers instead of CONCATENATE the strings as was my purpose.

DECLARE @activityids NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
 
SELECT @activityids = ISNULL(@activityids + ',', '') + '[' + LTrim(RTrim(Str(ActivityID))) + ']'
FROM tblStudentDetail
GROUP BY ActivityID
 
SET @sql = 'SELECT * FROM tblStudentDetail PIVOT (COUNT(stuDetailID) FOR ActivityID IN (' + @activityids + ')) p'
 
EXECUTE(@sql)

Open in new window

ASKER

Thank you again, I am learing a lot from the concept you have shared with me.

Right now I am using what you did to just show the StudentName and the ActivityName with an X placed under the ActivityName only if the student has signed up for the activity. I know in the example you used the int and I am not sure if I do the same thing you did with the nvarchar. I will find out and lt you know.

tblStudentDetail
stuDetailID - int
ActivityID - int
Student Name - nvarchar
ActivityName - nvarchar

stuDetailID StudentName ActivityID ActivityName

1 Jim Barns 1222 Running
2 Tom Fern 2222 Jumping
3 Frank Miller 3333 Walking
4 Dan Hompkins 4444 Skipping
5 Terry Lewis 1222 Running
6 Jim Barns 3333 Walking
7 Tom Fern 15 Swimming
8 Frank Miller 234 Carving
9 Dan Hompkins 2345 Gliding
10 Terry Lewis 4567 Sailing
11 Peter Fry 4444 Skipping
12 Tom Fern 5566 Talking
13 Frank Miller 2222 Jumping
14 Dan Hompkins 2568 Writing
15 Mike Brown 4555 Dancing

ASKER

Sorry I have not responed, I have been doing some more research, and one problem I realized is that I do not need to summarize each row for each column and row intersection so instead of using sum or count I must use First. I am getting close, please give me another day and I will close the ticket. Thank you for your patience with me.

Do you mean the first student ID?

NOt sure that is what you mean, but you can use some of the other aggregates in PIVOT like MIN.

DECLARE @activityids NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
 
SELECT @activityids = ISNULL(@activityids + ',', '') + '[' + LTrim(RTrim(Str(ActivityID))) + ']'
FROM tblStudentDetail
GROUP BY ActivityID
 
SET @sql = 'SELECT * FROM tblStudentDetail PIVOT (MIN(stuDetailID) FOR ActivityID IN (' + @activityids + ')) p'
 
EXECUTE(@sql)

Open in new window

ASKER

Thank you agian,

Yes, first studentName. I would use first on StudentName to show only the StudentName as the only Row Heading and the ActivityName as the column Heading and instead of placing the ActivityID as the value I would just place an X.

Trying
SET @sql = 'SELECT studentName FROM tblStudentDetail PIVOT (MIN(stuDetailID) FOR ActivityID IN (' + @activityids + ')) p'

Thank you again for your assistance.

Result:
*******
StudentName Running Jumping Walking Skipping
 
Jim Barns      X                   
Tom Fern               X                   
Frank Miller                   X        
Dan Hompkins                           X   
Terry Lewis    X 
 
 
No Luck using the two approaches below
 
DECLARE @activityids NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
 
SELECT @activityids = ISNULL(@activityids + ',', '') + '[' + LTrim(RTrim(Str(Activity Name))) + ']'
FROM tblStudentDetail
GROUP BY StudentName
 
SET @sql = 'SELECT StudentName FROM tblStudentDetail PIVOT (MIN(stuDetailID) FOR ActivityName IN (' + @activityids + ')) p'
 
EXECUTE(@sql)
 
OR 
 
TRANSFORM First([tblStudentDetail].stuDetailID) AS FirstOfstuDetailID
SELECT [tblStudentDetail].StudentName
FROM [tblStudentDetail]
GROUP BY [tblStudentDetail].StudentName
PIVOT [tblStudentDetail].ActivityName;

Open in new window

Ok, think what you are trying to do is different than the original query will provide. Will have to probably alter query...have to run now, but will take a look later.

ASKER

Thank you again for your time and patience,

Sorry it took me so long to get back.

Hopefully I copied everything over correctly, but this worked on my system as a straight query:

SELECT *
FROM (
SELECT d.StudentName, 'X' AS x, a.ActivityName
FROM tblStudentDetail AS d INNER JOIN
(SELECT ActivityName, MIN(stuDetailID) AS stuDetailID
FROM tblStudentDetail
GROUP BY ActivityName) AS a ON d.stuDetailID = a.stuDetailID
) derived
PIVOT (MAX(x) FOR ActivityName IN (Running, Jumping, Walking)) p

I just made the below into the dynamic SQL to do PIVOT, but you should be able to test the concept with hardcoded column names in the IN statement above to see if that is what you wanted.

DECLARE @activitynames NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
 
SELECT @activitynames = ISNULL(@activitynames + ',', '') + '[' + ActivityName + ']'
FROM tblStudentDetail
GROUP BY ActivityName
 
SET @sql = 'SELECT * FROM ('
SET @sql = @sql + 'SELECT d.StudentName, ''X'' AS x, a.ActivityName'
SET @sql = @sql + ' FROM tblStudentDetail AS d'
SET @sql = @sql + ' INNER JOIN (SELECT ActivityName, MIN(stuDetailID) AS stuDetailID FROM tblStudentDetail GROUP BY ActivityName) AS a'
SET @sql = @sql + ' ON d.stuDetailID = a.stuDetailID) derived'
SET @sql = @sql + ' PIVOT (MAX(x) FOR ActivityName IN (' + @activitynames + ')) p'
 
EXECUTE(@sql)

Open in new window

ASKER

Hello

The straight Query version works fine, but, I receive the following when I run the Dynamic version:

Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

If I remove SET @sql = 'SELECT * FROM (' I receive a message in the Results Window that says Command(s) completed successfully, but no output.

Please tell me wht I am doing wrong.

ASKER

Hello,
The straight query is what I was looking for, I swapped the StudentName with ActivityName to list the student name one time. I do not have any X in the column intersactions because the fields in the table
are written as Running (R) and SQL Server does not like that so a Null is just listed . I am sure that the dynamic Query does not have this issue.

SELECT *
FROM (
SELECT d.ActivityName, 'X' AS x, a.StudentName
FROM tblStudentDetail AS d INNER JOIN
(SELECT StudentName, MIN(stuDetailID) AS stuDetailID
FROM tblStudentDetail
GROUP BY StudentName) AS a ON d.stuDetailID = a.stuDetailID
) derived
PIVOT (MAX(x) FOR ActivityName IN (Running, Jumping, Walking)) p

Try changing this:
EXECUTE(@sql)

To:
PRINT(@sql)

Ensure that the dynamic SQL is coming out correctly. I have seen where amount of data (columns) is so much that they get cutoff when selecting in dynamic SQL string.

So at least we know the query works as you need it and we can focus on some issue with the dynamic SQL statement.

ASKER

The Results Window Said
The Query Completed Successfully :

SELECT * FROM (SELECT d. StudentName, 'X' AS x, a.ActivityName FROM tblStudentDetail AS d INNER JOIN (SELECT ActivityName, MIN(StuDetailID) AS
StuDetailID FROM tblStudentDetail GROUP BY ActivityName) AS a ON d.StuDetailID = a.StuDetailID) derived PIVOT (MAX(x) FOR ActivityName IN
([Running (R)],[Jumping (J)],[Walking (W)],[Skipping],[Swimming (SW)],[],[Carving (CAR)],[Gliding],[Sailing],[Talking (T)]))

There is one that is missing information...after Swimming. Think the dynamic will work since surrounded by [] which tells SQL that entire value including space and paranthesis is columnname; however, it doesn't like no column names at all so try this:

DECLARE @activitynames NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
 
SELECT @activitynames = ISNULL(@activitynames + ',', '') + '[' + ActivityName + ']'
FROM tblStudentDetail
WHERE ISNULL(ActivityName, '') <> ''
GROUP BY ActivityName
 
SET @sql = 'SELECT * FROM ('
SET @sql = @sql + 'SELECT d.StudentName, ''X'' AS x, a.ActivityName'
SET @sql = @sql + ' FROM tblStudentDetail AS d'
SET @sql = @sql + ' INNER JOIN (SELECT ActivityName, MIN(stuDetailID) AS stuDetailID FROM tblStudentDetail GROUP BY ActivityName) AS a'
SET @sql = @sql + ' ON d.stuDetailID = a.stuDetailID) derived'
SET @sql = @sql + ' PIVOT (MAX(x) FOR ActivityName IN (' + @activitynames + ')) p'
 
EXECUTE(@sql)

Open in new window

ASKER

Thnk you again,

It worked as you said, but the results window only shows 10 rows.

Those are the 10 rows of students who have at least one activity. Do you want all students regardless?

This should get the latter. It uses a LEFT JOIN and so additional change is needed to only have value of 'X' when ActivityName is not null otherwise leave it null or make it ''.

DECLARE @activitynames NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
 
SELECT @activitynames = ISNULL(@activitynames + ',', '') + '[' + ActivityName + ']'
FROM tblStudentDetail
WHERE ISNULL(ActivityName, '') <> ''
GROUP BY ActivityName
 
SET @sql = 'SELECT * FROM ('
SET @sql = @sql + 'SELECT d.StudentName, CASE WHEN a.ActivityName IS NOT NULL THEN ''X'' ELSE '''' END AS x, a.ActivityName'
SET @sql = @sql + ' FROM tblStudentDetail AS d'
SET @sql = @sql + ' LEFT JOIN (SELECT ActivityName, MIN(stuDetailID) AS stuDetailID FROM tblStudentDetail GROUP BY ActivityName) AS a'
SET @sql = @sql + ' ON d.stuDetailID = a.stuDetailID) derived'
SET @sql = @sql + ' PIVOT (MAX(x) FOR ActivityName IN (' + @activitynames + ')) p'
 
EXECUTE(@sql)

Open in new window

ASKER

Thank you again, the results are all good.

Moreover, Yes, I wanted to see all students because I may have to show a report with all student names listed regardless, a report that list only the rows of students who have at least one activity, and a report on a specific student.

Question: To filter by a specific student I could just use WHERE StudentName = 'Jim Barns' above
WHERE ISNULL(ActivityName, '') <> '' ?

Thank you again in advance,

Yes, if you want the ActivityName list filtered to the ones of a particular student.

If you want the report filtered to a particular student for all activities you would put that where clause in the dynamic SQL.

ASKER

I added the where statement and received the following below. I know it should work. Do you see what I am doing wrong?

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WHERE'

DECLARE @activitynames NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
 
SELECT @activitynames = ISNULL(@activitynames + ',', '') + '[' + ActivityName + ']'
FROM tblStudentDetail
WHERE StudentName = 'Jim Barns' 
WHERE ISNULL(ActivityName, '') <> ''
GROUP BY ActivityName
 
SET @sql = 'SELECT * FROM ('
SET @sql = @sql + 'SELECT d.StudentName, CASE WHEN a.ActivityName IS NOT NULL THEN ''X'' ELSE '''' END AS x, a.ActivityName'
SET @sql = @sql + ' FROM tblStudentDetail AS d'
SET @sql = @sql + ' LEFT JOIN (SELECT ActivityName, MIN(stuDetailID) AS stuDetailID FROM tblStudentDetail GROUP BY ActivityName) AS a'
SET @sql = @sql + ' ON d.stuDetailID = a.stuDetailID) derived'
SET @sql = @sql + ' PIVOT (MAX(x) FOR ActivityName IN (' + @activitynames + ')) p'
 
EXECUTE(@sql)

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thank you the And was the key.

Thank you again for staying engaged with me as well as, explaining to me the right way to produce the results I have been looking for. You are truly a WIZARD!

Thanks.

You are most welcome.

Happy coding!

/kev