Split data from one column into multiple colums

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  August 21, 2013 at 11:50 am

  #1643558

  I see what you are after. The bottom line is that you could do the sub-totaling you are looking for, but it is not elegant or pretty. This is what front ends were designed to do. Get information from the user, submit a query to the backend, massage the results of the data returned by the query to be presented to the user.

  Here is a query that is still only "kind of " close. It requires a self join to get the counts. I also left the RowNum column in there for comparison.

  select d.doctorname, d.location, dg.name, dg.packsize,

  Count(dpd2.doctorref) Total_Drugs,

  ROW_NUMBER() over(partition by d.doctorname order by (select 0)) RowNum

  from Doctor_Prescribed_Drugs dpd

  left join Doctor d on dpd.doctorref = d.doctorref

  left join drugs dg on dpd.drugref = dg.drugref

  join Doctor_Prescribed_Drugs dpd2 on dpd2.doctorref = dpd.doctorref

  where d.Location in ('London', 'Manchester') and dg.PackSize is not null

  group by d.doctorname, d.location, dg.name, dg.PackSize

  order by d.doctorname, Total_Drugs

  If you really want SQL to do the running count and formatting, I would suggest you study the following article. It seems to be real close to what you are trying to accomplish:

  http://www.databasejournal.com/features/mssql/article.php/3112381/SQL-Server-Calculating-Running-Totals-Subtotals-and-Grand-Total-Without-a-Cursor.htm

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

• jo_bloggs

  SSC Veteran

  Points: 255

  More actions

  August 22, 2013 at 11:15 pm

  #1644246

  Thanks for the reply.

  That gives me something to work with; I would have never have considered doing a 'double' join on the same table.

  Thanks again, it's much appreciated.