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
SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of robthomas09
robthomas09

ASKER

Thanks!
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo