Avatar of robthomas09
robthomas09
 asked on

SQL combine records based on provider id

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!
Microsoft SQL Server 2005SQL

Avatar of undefined
Last Comment
robthomas09

8/22/2022 - Mon
SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
awking00

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
robthomas09

ASKER
Thanks!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck