SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split data from one column into multiple colums


Split data from one column into multiple colums

Author
Message
jo_bloggs
jo_bloggs
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 7
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87228 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3105 Visits: 2766
Welcome to the SSC :-P

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
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3105 Visits: 2766
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
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87228 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jo_bloggs
jo_bloggs
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 7
@ 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. Cool
jo_bloggs
jo_bloggs
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 7
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
LinksUp
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 4450
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
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3105 Visits: 2766
Great formatted post this time :-P

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
jo_bloggs
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 7
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search