PIVOT or CASE? - Combining multiple rows into 1 row..

[jamespfisher]

Hey all

I'm writing a large extract report on clients of a pharmacy and the drugs they are prescribed (maximum of 6 drugs in total).

Data structure of the drugs table looks like this:

Client_id, Drug_id, Is_Main_drug
123, 789, 1
123, 526, 0
124, 478, 0
124, 789, 0
124, 526, 1
124, 775, 0
124, 885, 0
124, 993, 0


My aim is to get the csv extract to look like this:

Client_id, Main_drugname, Other_drugname1 , Other_drugname2 , Other_drugname3 , Other_drugname4 , Other_drugname5

All is fine until I get to the Drug table as its obviously normalised, and we have a third column to consider (Is_Main_drug) which has a 1 or 0 flag to indicate if it is the main drug being prescribed. If it is flagged as the main drug (1) then we need to get it into the Main_drugname column. All the client's other drugs (maximum of 5) need to each go in the Other_drugnameX columns...there is no set order in which they need to go in.

All help appreciated!!!

Cheers
James

P.S. I'm using SQL Server 2008.

[ck9663]

Given your sample, how will it look like after? Throw in a couple more records, too.

Happy Coding!


~~ CK

[Rabbit]

I would use pivot for that.

[Rabbit]

Here's an example.

declare @drugs table (client_id int, drug_id int, is_main_drug int)

insert into @drugs values (123, 789, 1)
insert into @drugs values (123, 526, 0)
insert into @drugs values (124, 478, 0)
insert into @drugs values (124, 789, 0)
insert into @drugs values (124, 526, 1)
insert into @drugs values (124, 775, 0)
insert into @drugs values (124, 885, 0)
insert into @drugs values (124, 993, 0)

select client_id, [1] as main_drug, [2], [3], [4], [5], [6]
from (
	select client_id, drug_id,
	row_number() over (partition by client_id order by is_main_drug desc ) as r
	from @drugs
	) d
pivot (max(drug_id) for r in ([1], [2], [3], [4], [5], [6], [7])) as pivottable

[jamespfisher]

Thanks man but I don't have admin access...can't do any of the insert statement stuff, plus there are about 90,000 client records and a couple thousand drug types! Bugger. Have to work out another way to do it hmmm

[Rabbit]

You don't have to do an insert. That's just an example you can run on SQL Server to see the results.

The solution itself requires no inserts.