SQL Server 2008 pivot without aggregate

Posted by Bryan Lewis on Stack Overflow See other posts from Stack Overflow or by Bryan Lewis
Published on 2010-03-22T16:39:16Z Indexed on 2010/03/22 16:51 UTC
Read the original article Hit count: 522

Filed under:

sql

|

pivot

|

sql-server

|

sql-server-2008

I have table to test score data that I need to pivot and I am stuck on how to do it.

I have the data as this:

gradelistening speaking reading writing
0   0.0   0.0   0.0   0.0
1 399.4 423.8   0.0   0.0
2 461.6 508.4 424.2 431.5
3 501.0 525.9 492.8 491.3
4 521.9 517.4 488.7 486.7
5 555.1 581.1 547.2 538.2
6 562.7 545.5 498.2 530.2
7 560.5 525.8 545.3 562.0
8 580.9 548.7 551.4 560.3
9 602.4 550.2 586.8 564.1
10 623.4 581.1 589.9 568.5
11 633.3 578.3 598.1 568.2
12 626.0 588.8 600.5 564.8

But I need it like this:

  gr0 gr1 gr2 gr3 gr4 gr5 gr6 gr7 ...
listening 0.0 399.4 461.6 501.0 521.9 555.1 562.7 560.5 580.9...
speaking 0.0 423.8...
reading 0.0 0.0 424.2...
writing 0.0 0.0 431.5...

I don't need to aggregate anything, just pivot the data.

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More

Related posts about pivot

multiple pivot table consolidation to another pivot table

as seen on Stack Overflow - Search for 'Stack Overflow'
I have to SQL Server views being drawn to 2 seperate worksheets as pivot tables in an excel 2007 file. the results on worksheet1 include example data: - company_name, tickets, month, year company1, 3, 1,2009 company2, 4, 1,2009 company3, 5, 1,2009 company3, 2, 2,2009 results from worksheet2… >>> More
Tool to automatically combine many large pivot table in many large Excel sheet ?

as seen on Super User - Search for 'Super User'
Hi all, We have several Excel files that contains large pivot data table with the same structure For example File A Pivot table (Field A, B, C) File B Pivot table (Field A, B, C) We want to combine them into 1 pivot table (A, B, C). Just want to know which ways we can do it ? Manual way :… >>> More
Nevron SharePoint Vision 2010 Vol.1 Now Available - advanced pivot Charts and Gauges for SharePoint

as seen on Dot net Slackers - Search for 'Dot net Slackers'
Nevron Software - leader in enterprise and scientific data visualization technology, announces the availability of the new Nevron SharePoint Vision 2010 Vol.1 the Data Visualization suite for SharePoint! The major release is now available for download and includes Nevron Chart and Gauge web parts… >>> More
Lisp Style question label local functions or not?

as seen on Stack Overflow - Search for 'Stack Overflow'
I was wondering if there is a standard practice regarding the use of labels in Lisp. I've been messing around with a Lisp implementation of the algorithm described in the first answer here http://stackoverflow.com/questions/352203/generating-permutations-lazily My current version uses labels to… >>> More
New Skool Crosstabbing

as seen on Oracle Blogs - Search for 'Oracle Blogs'
A while back I spoke about having to go back to BIP's original crosstabbing solution to achieve a certain layout. Hok Min has provided a 'man' page for the new crosstab/pivot builder for 10.1.3.4.1 users. This will make the documentation drop but for now, get it here! The old, hand method is still… >>> More