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