13031

SQL Pivot Dynamic Columns STUFF function empty cells

Question:

Doing SQL Pivot Dynamic Columns with STUFF function but not getting result I desire

Here is SQL Fiddle <a href="http://sqlfiddle.com/#!3/241c2/6/0" rel="nofollow">http://sqlfiddle.com/#!3/241c2/6/0</a>

What can I do to get rid of all the null\empty cells?

I would want the resulting display to be something like this where each column shows a list of classes each student is assigned to without a bunch of blank cells

Tom Harry Mary Sue Paul Algebra Algebra Algebra Algebra Geometry French French Spanish Spanish Art Biology Physics Physics Biology

Edit: request was made to show code here:

create table clsassin ( ClassID int, AssignID int, ClsNm varchar(10), StudntNm varchar(10), ) insert into clsassin values (1, 1, 'Algebra', 'Tom') insert into clsassin values (1, 2, 'Algebra', 'Harry') insert into clsassin values (1, 3, 'Algebra', 'Mary') insert into clsassin values (1, 4, 'Algebra', 'Sue') insert into clsassin values (2, 5, 'Geometry', 'Paul') insert into clsassin values (3, 6, 'French', 'Harry') insert into clsassin values (3, 7, 'French', 'Tom') insert into clsassin values (4, 8, 'Spanish', 'Mary') insert into clsassin values (4, 9, 'Spanish', 'Sue') insert into clsassin values (5, 10, 'Art', 'Paul') insert into clsassin values (6, 11, 'Biology', 'Tom') insert into clsassin values (6, 12, 'Biology', 'Paul') insert into clsassin values (7, 13, 'Physics', 'Harry') insert into clsassin values (7, 14, 'Physics', 'Sue') insert into clsassin values (8, 15, 'History', 'Sue') <hr />DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(StudntNm) FROM clsassin FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT ' + @cols + ' from ( select StudntNm, ClsNm , ClassID from clsassin ) x pivot ( min(ClsNm) for StudntNm in (' + @cols + ') ) p ' execute(@query)

Answer1:

The problem with your original query is you are including the classid in the select list of data for the PIVOT. You have 8 different classid values, which will then be grouped by when applying the aggregate function in the pivot.

The problem is that if you exclude the classid and apply the pivot, you will return only one value per student - the one that matches the min(ClsNm)

Since you want to display every class for each student, then you should consider using the row_number() windowing function instead of classid. If you apply row_number() and partition the data by the studntNm, then you will assign an incremented number for each class per student, then when you aggregate the data you will return each row.

The code will be:

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(StudntNm) FROM clsassin FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT ' + @cols + ' from ( select StudntNm, ClsNm, row_number() over(partition by StudntNm order by ClsNm) rn from clsassin ) x pivot ( min(ClsNm) for StudntNm in (' + @cols + ') ) p ' execute sp_executesql @query;

See <a href="http://sqlfiddle.com/#!3/241c2/13" rel="nofollow">SQL Fiddle with Demo</a>. This will give you the result:

| HARRY | MARY | PAUL | SUE | TOM | ---------------------------------------------------- | Algebra | Algebra | Art | Algebra | Algebra | | French | Spanish | Biology | History | Biology | | Physics | (null) | Geometry | Physics | French | | (null) | (null) | (null) | Spanish | (null) |

Answer2:

I have used this method to display a summary table from sales such as:

Product,Jan2013,Feb2013,.....

Using the FOR XML PATH('') method returns all the months, but they are not ordered. Can I sort them to follow the correct sequence? I attached my code below:

SET @cols = STUFF((SELECT distinct ',' + '[' + DATENAME(month,DateTime) + DATENAME(year,DateTime) +']' AS MonthYear from DRGCase dc INNER JOIN Company c ON dc.HospitalId=c.CompanyId WHERE dc.DischargeDate > @StartDate AND dc.DischargeDate <= DATEADD(DAY,1,@Enddate) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''); SELECT @query='SELECT Hospital,' + @cols + 'FROM ( SELECT c.CompanyName AS Hospital, DATENAME(month,DateTime) + DATENAME(year,DateTime) AS MonthYear,COUNT(*) AS Cases FROM DRGCase dc INNER JOIN Company c ON dc.HospitalId=c.CompanyId WHERE dc.DischargeDate > ''' + CONVERT(CHAR(10),@StartDate,126) + ''' AND dc.DischargeDate <= DATEADD(DAY,1,''' + CONVERT(CHAR(10),@EndDate,126) + ''') GROUP BY c.CompanyName, DATENAME(month,DateTime) + DATENAME(year,DateTime) ) ps PIVOT ( sum(Cases) FOR MonthYear IN (' + @cols + ') ) as pvt' exec(@query)

Recommend

  • How to restart a sketch project in processing?
  • Attribute value “001” of type ID must be an NCName when namespaces are enabled
  • Traversing a multi-dimensional array
  • how to create pretty urls
  • Generate c# object code and assign values to its properties from an xml document
  • Hoisting and variable scope
  • What are the best practices for migrating an Oracle 10g database to Microsoft SQL 2008 R2? Applicati
  • xquery to return the element name
  • Deserializing JSON to a .net base class with JSON.net
  • How can I get rid of dynamic SQL
  • Find 4 minimal values in 4 __m256d registers
  • Python : How to center Label in tkinter window
  • What does this mean? (Google Maps API)
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • Init child with Parent instance
  • How to find data from last week in MySQL
  • Basic defensive programming [duplicate]
  • Removing Duplicate Geometries
  • Identifier too long in Oracle
  • Eliminate partial duplicate rows from result set
  • PHP - How to update data to MySQL when click a radio button
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Convert array of 8 bytes to signed long in C++
  • Matplotlib draw Spline from multiple points
  • XCode can't find symbols for a specific iOS library/framework project
  • Calling of Constructors in a Java
  • Compare two NSDates in iPhone
  • Transpose CSV data with awk (pivot transformation)
  • Use group_by to filter specific cases while keeping NAs
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • log4net write single file for each call to log.info
  • Benchmarking RAM performance - UWP and C#
  • Acquiring multiple attributes from .xml file in c#
  • Understanding cpu registers
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • How can I remove ASP.NET Designer.cs files?
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • java string with new operator and a literal