Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Split data from one column into multiple colums Expand / Collapse
Author
Message
Posted Tuesday, August 20, 2013 1:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:33 AM
Points: 6,754, Visits: 12,854
LinksUp (8/18/2013)
I think this is what you are after:

select d.name, d.packsize, doc.doctorname, doc.location
from Doctor_Prescribed_Drugs dpd
left join Doctor doc on dpd.doctorref = doc.doctorref
left join Drugs d on dpd.drugref = d.drugref
where dpd.drugref in ('A1', 'B2', 'C3')

If you compare your query with the above query, you will see the problem right away! By using the Doctor_Prescribed_Drugs table as the primary table, or linking (my term) table, it makes it easy to see how the left joins are to be constructed.


This works and the explanation fits too, but why LEFT JOIN to the link table? dpd shouldn't contain any doctors which aren't in the doctors table, or drugs which aren't in the drugs table.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1486130
Posted Tuesday, August 20, 2013 3:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:37 PM
Points: 319, Visits: 1,142
ChrisM@Work (8/20/2013)


This works and the explanation fits too, but why LEFT JOIN to the link table? dpd shouldn't contain any doctors which aren't in the doctors table, or drugs which aren't in the drugs table.



I was simply building upon the OP's original query. Since it started with a left join, I continued to use it since it still gave the correct output. What each table may or may not contain as correct data is highly dependent upon the constraints of which we are not privy to.


__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1486156
Posted Tuesday, August 20, 2013 11:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 11:14 PM
Points: 7, Visits: 7
Our dba is away on long term sick at the moment and this has fallen to me ('the network guy') to sort out as I've used SQL "once or twice".

I have one further query for this and then I'm done I promise.

Using the tables posted here: http://www.sqlservercentral.com/Forums/FindPost1485573.aspx

I need to get: doctor name, doctor location, drug name, drug packsize. I then need to do a total number of drugs for that doctor.

I started with something like this:

select d.doctorname, d.location, dg.name, dg.packsize,
Count (dg.packsize) as 'Total Drugs'
from Doctor_Prescribed_Drugs dpd
left join Doctor d on dpd.doctorref = d.doctorref
left join drugs dg on dpd.drugref = dg.drugref
where d.Location in ('London', 'Manchester')
and dg.PackSize is not null
group by d.doctorname, d.location, dg.name, dg.PackSize
with rollup
order by [Total Drugs]

This sort of gives me the answer I need, but the results are a mess and not really presentable to the end customer.

Any further suggestions?

Thanks in advance; you guys are great.
Post #1486384
Posted Tuesday, August 20, 2013 12:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:37 PM
Points: 319, Visits: 1,142
As you have found out, SQL is not at its best presenting data to the world. That is usually the job of the front end!

I was not 100% sure what you are trying to count so I took a stab at it.

select d.doctorname, d.location, dg.name, dg.packsize, 
ROW_NUMBER() over(partition by d.doctorname order by (select 0)) Total_Drugs
from Doctor_Prescribed_Drugs dpd
left join Doctor d on dpd.doctorref = d.doctorref
left join drugs dg on dpd.drugref = dg.drugref
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

I just added a Row_Number based on the doctorname. That effectively gives you a running count of drugs prescribed by each doctor.

HTH


__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1486396
Posted Tuesday, August 20, 2013 11:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 11:14 PM
Points: 7, Visits: 7
Hmmn, that sort of works, but what I'm after is this results wise:

doctorname  doctorlocation  drugname    packsize  count   Total Drugs Prescribed
Campbell Manchester Aspirin 10 1
Campbell Manchester Bisoprolol 10 1
Campbell Manchester 2

Where the 'total drugs prescribed' column is a sum of the count column.
Post #1486518
Posted Wednesday, August 21, 2013 11:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:37 PM
Points: 319, Visits: 1,142
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/
Post #1486829
Posted Thursday, August 22, 2013 11:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 11:14 PM
Points: 7, Visits: 7
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.
Post #1487638
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse