?
Solved

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

Posted on 2008-10-28
29
Medium Priority
?
435 Views
Last Modified: 2012-05-05
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,
0
Comment
Question by:cesemj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 14
29 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22824494
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.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22824540
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

0
 

Author Comment

by:cesemj
ID: 22825044
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.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22825233
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

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22825264
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.
0
 

Author Comment

by:cesemj
ID: 22827669
Thank you, for the direction.  I will let you know how it turns out.
0
 

Author Comment

by:cesemj
ID: 22831876
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
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22833173
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

0
 

Author Comment

by:cesemj
ID: 22843056
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
0
 

Author Comment

by:cesemj
ID: 22887076
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.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22887402
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

0
 

Author Comment

by:cesemj
ID: 22887917
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

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22888281
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.
0
 

Author Comment

by:cesemj
ID: 22890140
Thank you again for your time and patience,
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22891795
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

0
 

Author Comment

by:cesemj
ID: 22897146
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.
0
 

Author Comment

by:cesemj
ID: 22897456
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
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22897902
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.
0
 

Author Comment

by:cesemj
ID: 22898118
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)]))


0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22898181
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

0
 

Author Comment

by:cesemj
ID: 22898326
Thnk you again,

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

Expert Comment

by:Kevin Cross
ID: 22898499
Those are the 10 rows of students who have at least one activity.  Do you want all students regardless?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22898530
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

0
 

Author Comment

by:cesemj
ID: 22898727
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,
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22898820
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.
0
 

Author Comment

by:cesemj
ID: 22898876
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

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22898908
WHERE StudentName = 'Jim Barns'
WHERE ISNULL(ActivityName, '') <> ''

You have WHERE In twice, one should be an AND!
0
 

Author Comment

by:cesemj
ID: 22898984
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!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22901904
Thanks.

You are most welcome.

Happy coding!

/kev
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question