Split data from one column into multiple colums

Question

Post reply

Split data from one column into multiple colums

jo_bloggs

SSC Veteran

Points: 265
More actions
August 17, 2013 at 10:24 am

#297004

Table is called drugs and is laid out like so:
name dosage packsize
aspirin 75mg 10
aspirin 75mg 20
aspirin 150mg 10
aspirin 150mg 20
aspirin 300mg 10
aspirin 300mg 20
aspirin 300mg 30
What I need to do is show this:
Name dosage packsize10 packsize20 packsize30
aspirin 75mg x o o
aspirin 75mg o x o
aspirin 150mg x o o
aspirin 150mg o x o
aspirin 300mg x o o
aspirin 300mg o x o
aspirin 300mg o o x
where x = positive and o=negative result
I'm pretty sure that I'm going to have to create a new table, but I can't for the life of me get the name, dosage and packsize to all be on the same row.
Any and all help is very much appreciated.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Please see the following article. Change SUM to MAX if you want to "pivot" character based data.

http://www.sqlservercentral.com/articles/T-SQL/63681/

If you prefer a coded answer to your problem, please see the first link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Welcome to the SSC 😛

While you post the problems please the DDL statements of Tables, Insert data and desired output so that we can help out you guys quickly.............

Here is the solution of your problem that you posted:

Create table drugs

(

name varchar(20),

Dosage varchar(20),

PackSize int

)

GO

INSERT INTO Drugs

SELECT 'aspirin', '75mg',10

UNION ALL

SELECT 'aspirin', '75mg',20

UNION ALL

SELECT 'aspirin', '150mg',10

UNION ALL

SELECT 'aspirin', '150mg',20

UNION ALL

SELECT 'aspirin', '300mg',10

union all

SELECT 'aspirin', '300mg',20

union all

SELECT 'aspirin', '300mg',30

GO

Here is the script that gives you desired output:

SELECT

d.name,

d.dosage,

MIN(CASE WHEN PackSize = 10 THEN 'x' ELSE 'o' END) Packsize10,

MIN(CASE WHEN packsize =20 THEN 'x' ELSE 'o' END) Packsize20,

MIN(CASE WHEN packsize =30 THEN 'x' ELSE 'o' END) Packsize30

FROM drugs d

GROUP BY d.name, d.dosage, d.PackSize

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Also read the article that Jeff has posted, it an excellent article...

Previously I used to use PIVOT but after reading Jeff's article I start using CROSS TAbS....

Thanks Jeff for that valuable article 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

kapil_kk (8/17/2013)
Also read the article that Jeff has posted, it an excellent article...
Previously I used to use PIVOT but after reading Jeff's article I start using CROSS TAbS....
Thanks Jeff for that valuable article 🙂

Thank you, kind Sir, for the thoughtful feedback. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jo_bloggs SSC Veteran Points: 265 More actions · Answer 5

@ Jeff Moden: Thanks for the response; Sorry about not posting correctly :blush: I'll get it right next time. Thanks for your help, it's much appreciated.

@ kapil_kk: Thanks for the response; that query helped me out no end. Thank you, it's much appreciated - you a cool dude. Cheers. 😎

jo_bloggs SSC Veteran Points: 265 More actions · Answer 6

Further to this, I wonder if you could help me out with a query that uses these tables;

Create table drugs

(

drugref varchar (5),

name varchar(120),

DosageMG varchar(120),

PackSize int

)

GO

INSERT INTO Drugs

SELECT 'A1', 'Aspirin', '75', 10

UNION ALL

SELECT 'B2', 'Salbutamol', '100',20

UNION ALL

SELECT 'C3', 'Tramadol', '150',10

UNION ALL

SELECT 'D4', 'Warfarin', '5',20

UNION ALL

SELECT 'E5', 'Bisoprolol', '5',10

union all

SELECT 'F6', 'Paracetamol', '200',20

union all

SELECT 'G7', 'Paracetamol', '300',20

UNION all

select 'H8', 'Atorvastatin', '80',28

union all

select 'I9', 'Folic Acid', '25',14

union all

select 'J10', 'Ramipril', '2.5',28

Go

Create Table Doctor

(

doctorref varchar (5),

doctorname varchar (120),

location varchar (120)

)

GO

INSERT INTO Doctor

SELECT 'A1', 'Smith', 'London'

union all

select 'B2', 'Jones', 'Glasgow'

union all

SELECT 'C3', 'Campbell', 'Manchester'

Union all

Select 'D4', 'Ward', 'Birmingham'

Union all

Select 'E5', 'Perry', 'Cardiff'

Go

Create Table Doctor_Prescribed_Drugs

(

doctorref varchar (5),

drugref varchar (5)

)

Go

INSERT INTO Doctor_Prescribed_Drugs

SELECT 'A1', 'G7'

UNION ALL

SELECT 'A1', 'A1'

UNION ALL

SELECT 'A1', 'E5'

UNION ALL

SELECT 'A1', 'I9'

UNION ALL

SELECT 'C3', 'A1'

UNION ALL

SELECT 'C3', 'E5'

GO

I've been trying to write a query using joins using the Doctor_Prescribed_Drugs table, as this is the only table that contains relational data.

Here's my query so far:

select d.drugname, d.packsize,

doc.name, doc.location

from drugs d

left join doctor_prescribed_drugs dpd on d.drugref = dpd.drugref

left join doctor_prescribed_drugs dpd on doc.doctorref = dpd.doctorref

where d.drugref in ('A1', 'B2', 'C3')

but this is erroring out with "Msg 1011, Level 16, State 1, Line 1

The correlation name 'dpd' is specified multiple times in a FROM clause."

I can't get the data back that I need (the Doctor name, location and what they've prescribed etc) without using the dpd tabe, but I can't figure out the query that will do this. Pretty much everything I've tried so far has broken.

I'm sure that I'm just being stupid here... either that or I'm too new...

Thanks for any help. It's always appreciated.

PS: Jeff, I hope I got this formatted right this time!

LinksUp SSCertifiable Points: 6526 More actions · Answer 7

I think this is what you are after:

select d.name, d.packsize, doc.doctorname, doc.location

from Doctor_Prescribed_Drugs dpd

left join Doctor doc on dpd.doctorref = doc.doctorref

left join Drugs d on dpd.drugref = d.drugref

where dpd.drugref in ('A1', 'B2', 'C3')

If you compare your query with the above query, you will see the problem right away! By using the Doctor_Prescribed_Drugs table as the primary table, or linking (my term) table, it makes it easy to see how the left joins are to be constructed.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 8

Great formatted post this time 😛

I think this is what you are looking:

select d.drugname, d.packsize,

doc.name, doc.location

from drugs d

left join doctor_prescribed_drugs dpd on d.drugref = dpd.drugref

left join doctor doc on doc.doctorref = dpd.doctorref

where d.drugref in ('A1', 'B2', 'C3')

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

jo_bloggs SSC Veteran Points: 265 More actions · Answer 9

Thank you both for your help, it's much appreciated.

I see what I was doing wrong now, by not using the dpd table as the main 'from' table in the query.

Thanks both, it's much appreciated.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

LinksUp (8/18/2013)
I think this is what you are after:
select d.name, d.packsize, doc.doctorname, doc.location
from Doctor_Prescribed_Drugs dpd
left join Doctor doc on dpd.doctorref = doc.doctorref
left join Drugs d on dpd.drugref = d.drugref
where dpd.drugref in ('A1', 'B2', 'C3')
If you compare your query with the above query, you will see the problem right away! By using the Doctor_Prescribed_Drugs table as the primary table, or linking (my term) table, it makes it easy to see how the left joins are to be constructed.

This works and the explanation fits too, but why LEFT JOIN to the link table? dpd shouldn't contain any doctors which aren't in the doctors table, or drugs which aren't in the drugs table.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LinksUp SSCertifiable Points: 6526 More actions · Answer 11

ChrisM@Work (8/20/2013)
This works and the explanation fits too, but why LEFT JOIN to the link table? dpd shouldn't contain any doctors which aren't in the doctors table, or drugs which aren't in the drugs table.

I was simply building upon the OP's original query. Since it started with a left join, I continued to use it since it still gave the correct output. What each table may or may not contain as correct data is highly dependent upon the constraints of which we are not privy to.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

jo_bloggs SSC Veteran Points: 265 More actions · Answer 12

Our dba is away on long term sick at the moment and this has fallen to me ('the network guy') to sort out as I've used SQL "once or twice". :rolleyes:

I have one further query for this and then I'm done I promise. 😛

Using the tables posted here: http://www.sqlservercentral.com/Forums/FindPost1485573.aspx

I need to get: doctor name, doctor location, drug name, drug packsize. I then need to do a total number of drugs for that doctor.

I started with something like this:

select d.doctorname, d.location, dg.name, dg.packsize,

Count (dg.packsize) as 'Total Drugs'

from Doctor_Prescribed_Drugs dpd

left join Doctor d on dpd.doctorref = d.doctorref

left join drugs dg on dpd.drugref = dg.drugref

where d.Location in ('London', 'Manchester')

and dg.PackSize is not null

group by d.doctorname, d.location, dg.name, dg.PackSize

with rollup

order by [Total Drugs]

This sort of gives me the answer I need, but the results are a mess and not really presentable to the end customer.

Any further suggestions?

Thanks in advance; you guys are great.

LinksUp SSCertifiable Points: 6526 More actions · Answer 13

As you have found out, SQL is not at its best presenting data to the world. That is usually the job of the front end!

I was not 100% sure what you are trying to count so I took a stab at it.

select d.doctorname, d.location, dg.name, dg.packsize,

ROW_NUMBER() over(partition by d.doctorname order by (select 0)) Total_Drugs

from Doctor_Prescribed_Drugs dpd

left join Doctor d on dpd.doctorref = d.doctorref

left join drugs dg on dpd.drugref = dg.drugref

where d.Location in ('London', 'Manchester') and dg.PackSize is not null

group by d.doctorname, d.location, dg.name, dg.PackSize

order by d.doctorname, Total_Drugs

I just added a Row_Number based on the doctorname. That effectively gives you a running count of drugs prescribed by each doctor.

HTH

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

jo_bloggs SSC Veteran Points: 265 More actions · Answer 14

Hmmn, that sort of works, but what I'm after is this results wise:

doctorname doctorlocation drugname packsize count Total Drugs Prescribed

Campbell Manchester Aspirin 10 1

Campbell Manchester Bisoprolol 10 1

Campbell Manchester 2

Where the 'total drugs prescribed' column is a sum of the count column.