How to Convert Rows into Columns?

Question

How to Convert Rows into Columns?

Sahasam

Ten Centuries

Points: 1001
More actions
May 16, 2010 at 1:32 pm

#232603

Hi All, I have a table with the following data.
ClientVisitGuid HealthIssue
1001 Cancer
1001 Pneumonia
1001 Chestpain
1002 Fever
1002 Heartpain
I want to convert the HealthIssue Column into Number of Columns on the basis of ClientVisitGuid.
Below is the desired Result Output. Please Suggest a query?
ClientVisitGuid HealthIssue1 HealthIssue2 HealthIssue3
1001 Cancer Pneumonia Chestpain
1002 fever HeartPain

Viewing 12 posts - 1 through 11 (of 11 total)

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

Conan The Canadian Ten Centuries Points: 1335 More actions · Answer 1

I won't give you an exact answer, but rather just an idea for a basis to a solution.

What if you were to create a temp table with a set of columns like GUID, HealthIssue1, HealthIssue2, HeathIssue3...to however many columns you need. (using Dynamic SQL would be handy here too, you could figure out how many columns you need and build the table to the exact size).

Next, throw the raw data into a 2nd temp table that looks identical to your source table.

Insert the top 1 row per GUID into the report temp table with the HealthIssue in the HealthIssue1 column.

Delete from the raw data temp table all the records that have matching rows in the report temp table.

Now update your report temp table with the top 1 record found that matches your GUID (set the HealthIssue in HealthIssue2 column)

Delete from the raw data temp table again and keep repeating the update and deletes by moving over one column in your report until you have processeed all your raw data.

And voila, you will have your report in a temp table!

I'm sure there are more effecient ways of doing it, but that's the best idea that I can think of off hand.

Kev

-=Conan The Canadian=-

www.kevinconan.com

Kev -=Conan The Canadian=-
@ConanTheCdn

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

This is a basic pivot operation. Unless you get "fancy", you would need to know how many issues you would need to pivot out, and you'd need a consistent menthod for order elements within groups.

That said - once you have those you could use something like (test data setup included)

create table #T1 (ClientVisitGuid int, HealthIssue varchar(50));

go

insert #t1

select 1001,'Cancer'

union all select

1001,'Pneumonia'

union all select

1001,'Chestpain'

union all select

1002,'Fever'

union all select

1002,'Heartpain'

go

;with PivotCTE as (

select ClientVisitGuid, HealthIssue, ROW_NUMBER() over (partition by ClientVisitGuid order by healthissue) RN

from #T1)

select ClientVisitGuid,

MAX( case when rn=1 then HealthIssue end) HealthIssue1,

MAX( case when rn=2 then HealthIssue end) HealthIssue2,

MAX( case when rn=3 then HealthIssue end) HealthIssue3

from PivotCTE

group by ClientVisitGuid

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Actually, you can get very sophisticated with this type of thing and it can all be done "auto-magically" with a bit of dynamic SQL. Please see the following article for how to do it... it's really easy...

http://www.sqlservercentral.com/articles/Crosstab/65048/

I normally take issue with this type of denormalization of data (normally, a mistake for sure) but enough folks have justified individual needs so I'm not even going to ask "Why do you want to do this" anymore. 😛

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

Sahasam Ten Centuries Points: 1001 More actions · Answer 4

I did this in some other way.

I created 3 functions and used those 3 funcions in the procedure directly by passing a parameter.

First Function Which represents first column, i retrieved the data by using TOP 1 with Inner Query operation

Second Function Which represents second column, i retrieved the data by using TOP 2 with Inner Query operation.

Similarily 3rd Function with top operation.

Thanks all for your help guys.

Sahasam Ten Centuries Points: 1001 More actions · Answer 5

Matt, the one you mentioned is a very simplified Format. Matt, From your solution i known the usage of partition by Operation. Thank you.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Sahasam (5/17/2010)
I did this in some other way.
I created 3 functions and used those 3 funcions in the procedure directly by passing a parameter.
First Function Which represents first column, i retrieved the data by using TOP 1 with Inner Query operation
Second Function Which represents second column, i retrieved the data by using TOP 2 with Inner Query operation.
Similarily 3rd Function with top operation.
Thanks all for your help guys.

Ok... just be careful. The use of functions that way could make for some pretty slow code.

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

Sahasam Ten Centuries Points: 1001 More actions · Answer 7

You are right jeff, But I had very few number of records(Around 3000). so i am using the functions.

Sahasam...

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Sahasam (5/17/2010)
You are right jeff, But I had very few number of records(Around 3000). so i am using the functions.
Sahasam...

Heh... you've hit a bit of a sore spot with me on that. It's not difficult to do the right way so that it's scalable... why are you building a time bomb in your code (or someone else's code if they "borrow" your method)?

This is why a lot of people hate 3rd party resources and outsourcing and have developed a distrust even for inhouse IT. People take unnecessary shortcuts based on short sighted "requirements" and the customer ultimately and eventually pays the price. It's like saying that you're going to put a threadbare tire on someone's car because they don't use the car much.

If it's worth doing, it's worth doing right. 😉

--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 SSC Guru Points: 1004406 More actions · Answer 9

Heh... no response... guess that fell on deaf ears.

Just in case someone is still listening and for the record, the code that Matt Miller posted will blow the doors off code that uses 3 functions especially if they're scalar functions. Even if Matt's code is converted to dynamic SQL, it'll still smoke 3 function scalar code.

Please reconsider for the sake of who ever is going to use the code... 😉

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

Sahasam Ten Centuries Points: 1001 More actions · Answer 10

This issue is not on client side. i personally working on some data and came up with this issue. Thanks for your info abt functions, i will get rid of them.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

Sahasam,

Thanks for the feedback. Before you get rid of the functions, there's a learning opportunity to be had. Create a couple hundred thousand rows and test it against the function code and measure the CPU time and duration. Then, do the same thing for Matt's code and compare the differences. It's nice that you take my word for it but you really should test to see and truly understand the difference. After all, that's how I figured it out... someone said it was bad and instead of just taking the word of someone on the internet, I tested. They turned out to be correct... it was bad. 😀

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