Split data from one column into multiple colums

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

  • 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)

  • 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/

  • 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/

  • 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)

  • @ 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. 😎

  • 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!

  • 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/

  • 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/

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

  • 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

  • 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/

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

  • 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/

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

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

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