Help needed: Moving rows to columns

Question

Help needed: Moving rows to columns

pierre.daubresse

Valued Member

Points: 62
More actions
September 17, 2010 at 9:30 am

#387809

Hi,
I have a database with several tables, containing clinical info. One of the tables (let's name it TableA) has a one-to-many relationship with another table (let's name it TableB).
TableA contains one row per patient, with several variables like age, gender,...
TableB contains info about antibiotics taken by these patients. Of course it may happen one patient takes several antibiotics (maximum 4) at the same time or no antibiotic at all. So the cardinality of the relation would be 0-4.
If I do a left join on my tables, I get a result table with all the patients and all the variables. Example:
PatientID - PatientAge - PatientGender - AntibioticVar1 - AntibioticVar2
----------------------------------------------------------------------
2 - 35 - Male - Vancomycin - 200mg a day
3 - 48 - Female - Vancomycin - 100mg a day
3 - 48 - Female - Methicillin - 200 mg a day
In this case, for patients who are taking several antibiotics, I get several rows. And my client asks me to provide her with a table where there is only one row/patient. So, I need a table like this:
PatientID - PatientAge - PatientGender - Antibiotic1Var1 - Antibiotic1Var2 - Antibiotic2Var1 - Antibiotic2Var2
----------------------------------------------------------------------------------------------------------
2 - 35 - Male - Vancomycin - 200mg a day - NULL - NULL
3 - 48 - Female - Vancomycin - 100mg a day - Methicillin - 200 mg a day
My question is: can I get such a table through SQL (ideally in a view), or do I have to compute it with a language like C#?
Thanks for any advice,
Pierre

Viewing 6 posts - 1 through 6 (of 6 total)

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

hallidayd SSCrazy Points: 2490 More actions · Answer 1

You can do this in SQL, but SQL is not designed to produce this sort of output (lots of reasons, interesting if you like relational theory) and the code can get nasty.

Ideally you would do this client side, in C# or SSRS or Excel or whatever your consumer is.

There's some nice stuff by Jeff Smith that covers the FE and BE options:

http://weblogs.sqlteam.com/jeffs/category/156.aspx

WayneS SSC Guru Points: 95461 More actions · Answer 2

Please refer to the links in my signature that deal with Cross-Tabs and Pivot tables, Parts 1 and 2.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

pierre.daubresse Valued Member Points: 62 More actions · Answer 3

Thanks for your help. I will dwelve into your links on Monday. Have a nice weekend.

pierre.daubresse Valued Member Points: 62 More actions · Answer 4

Hi,

I looked at "Cross Tabs and Pivots, Part 1 – Converting Rows to Columns" and it looks like The Multi-Aggregate Cross Tab is the solution for me. But I need a column to "pivot" my data. In my case I should have a column identifying for each patient the nth occurrence of an antibiotic, which would look like:

PatientID - PatientAge - PatientGender - AntibioticOccurrence - AntibioticVar1 - AntibioticVar2

----------------------------------------------------------------------

2 - 35 - Male - 1 - Vancomycin - 200mg a day

3 - 48 - Female - 1 - Vancomycin - 100mg a day

3 - 48 - Female - 2 - Methicillin - 200 mg a day

3 - 48 - Female - 3 - Amoxicillin- 50 mg a day

In this way I would "pivot" the data using the AntibioticOccurrence column. My next question is: how can I add such a column to my table? Using the rank() function?

Thanks for your help,

Pierre

LutzM SSC Guru Points: 107049 More actions · Answer 5

pierre.daubresse (9/20/2010)
Hi,
I looked at "Cross Tabs and Pivots, Part 1 – Converting Rows to Columns" and it looks like The Multi-Aggregate Cross Tab is the solution for me. But I need a column to "pivot" my data. In my case I should have a column identifying for each patient the nth occurrence of an antibiotic, which would look like:
PatientID - PatientAge - PatientGender - AntibioticOccurrence - AntibioticVar1 - AntibioticVar2
----------------------------------------------------------------------
2 - 35 - Male - 1 - Vancomycin - 200mg a day
3 - 48 - Female - 1 - Vancomycin - 100mg a day
3 - 48 - Female - 2 - Methicillin - 200 mg a day
3 - 48 - Female - 3 - Amoxicillin- 50 mg a day
In this way I would "pivot" the data using the AntibioticOccurrence column. My next question is: how can I add such a column to my table? Using the rank() function?
Thanks for your help,
Pierre

You could use ROW_NUMBER() OVER(PARTITION BY PatientID ORDER BY AntibioticVar1) (if you want to have a different order per patientid you'd need to find a column that'll provide that order - or the reverse order...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]