troubleshooting Question

SQL combine records based on provider id

Avatar of robthomas09
robthomas09 asked on
Microsoft SQL Server 2005SQL
3 Comments2 Solutions276 ViewsLast Modified:
Hello experts,

I am trying to get multiple records of data to end up on the same row by doctor when I run a select query.

declare @Order table(
      provider_name varchar(100),
      PAQ_Count_Doc INT,
      PAQ_Count_Lab INT,
      PAQ_Count_ICS INT,
      PAQ_Count_Pic INT,
      practice_name varchar(100),
      Doc_Flag INT

)

Table: Order
Provider                            Docs      Labs         ICS              PICS
Metts, Brent A            2403      NULL      NULL      NULL
Metts, Brent A            NULL      2402      NULL      NULL
GOODMAN, GORDON TNULL      576              NULL      NULL
GOODMAN, GORDON TNULL      575            NULL      NULL
JACOBS, FRANCINE      NULL      NULL      1119      NULL
JACOBS, FRANCINE      NULL      NULL      1118      NULL
JACOBS, FRANCINE      NULL      1234      NULL      NULL
JACOBS, FRANCINE      1234      NULL      NULL      NULL
JACOBS, FRANCINE      1233      NULL      NULL      NULL
Krishnaraj, PanduranganNULL      NULL      NULL      2
Krishnaraj, PanduranganNULL      NULL      NULL      1
Krishnaraj, PanduranganNULL      NULL      1               NULL

I am trying to get all the data for the columns Docs, Labs, ICS, PICS to end up on the same line, and the top largest value in the column only showing, ending up with:

Table: Order
Provider                            Docs      Labs         ICS      PICS
Metts, Brent A            2403      2402      NULL      NULL
GOODMAN, GORDON TNULL      576         NULL      NULL
JACOBS, FRANCINE      1234      1234      1119      NULL
Krishnaraj, PandurangaNULL      NULL      1               2

something like:

select provider_name
, ROW_NUMBER() OVER ( PARTITION BY Provider ORDER BY count(Docs) DESC ) AS Docs
, ROW_NUMBER() OVER ( PARTITION BY Provider ORDER BY count(Labs) DESC ) AS Labs
, ROW_NUMBER() OVER ( PARTITION BY Provider ORDER BY count(ICS) DESC ) AS ICS
, ROW_NUMBER() OVER ( PARTITION BY Provider ORDER BY count(PICS) DESC ) AS PICS
from Order
group by provider,  Docs, Labs, ICS, PICS
order by provider

Thoughts?

Thanks!
ASKER CERTIFIED SOLUTION
awking00
Information Technology Specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros