Solved

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

Posted on 2008-10-28
29
421 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
  • 15
  • 14
29 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
Thank you, for the direction.  I will let you know how it turns out.
0
 

Author Comment

by:cesemj
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
Thank you again for your time and patience,
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
Thnk you again,

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

Expert Comment

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

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
WHERE StudentName = 'Jim Barns'
WHERE ISNULL(ActivityName, '') <> ''

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

Author Comment

by:cesemj
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
Thanks.

You are most welcome.

Happy coding!

/kev
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now