Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Split data from one column into multiple colums Expand / Collapse
Author
Message
Posted Saturday, August 17, 2013 10:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 11:14 PM
Points: 7, 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.
Post #1485499
Posted Saturday, August 17, 2013 4:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1485532
Posted Saturday, August 17, 2013 8:12 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1485543
Posted Saturday, August 17, 2013 8:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1485544
Posted Saturday, August 17, 2013 10:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
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.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1485551
Posted Sunday, August 18, 2013 2:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 11:14 PM
Points: 7, Visits: 7
@ Jeff Moden: Thanks for the response; Sorry about not posting correctly 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.
Post #1485564
Posted Sunday, August 18, 2013 4:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 11:14 PM
Points: 7, 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!
Post #1485573
Posted Sunday, August 18, 2013 7:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 340, Visits: 1,298
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/
Post #1485581
Posted Sunday, August 18, 2013 11:20 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1485647
Posted Monday, August 19, 2013 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 11:14 PM
Points: 7, 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.
Post #1486107
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse