Transpose Rows to columns

Question

Transpose Rows to columns

ajspencer

SSC Enthusiast

Points: 197
More actions
April 22, 2015 at 1:45 pm

#147324

Hollo,
I am in need of some coding help..... I'm trying to transpose rows to columns at the same time do a count of the rows without getting duplicate crs_id.
select l27_mfyp_crs_id, l27_mfyp_crs_pair1, l27_mfyp_crs_pair2,
case
when (l27_mfyp_crs_rank = '1') then count(l27_mfyp_id) end as '1st Choice'
-- when (l27_mfyp_crs_rank = '2') then count(l27_mfyp_id) end as '2nd Choice'
-- when (l27_mfyp_crs_rank = '3') then count(l27_mfyp_id) end as '3rd Choice',
-- when (l27_mfyp_crs_rank = '4') then count(l27_mfyp_id) end as '4th Choice',
-- when (l27_mfyp_crs_rank = '5') then count(l27_mfyp_id) end as '5th Choice',
-- when (l27_mfyp_crs_rank = '6') then count(l27_mfyp_id) end as '6th Choice',
-- when (l27_mfyp_crs_rank = '7') then count(l27_mfyp_id) end as '7th Choice',
-- when (l27_mfyp_crs_rank = '8') then count(l27_mfyp_id) end as '8th Choice',
-- when (l27_mfyp_crs_rank = '9') then count(l27_mfyp_id) end as '9th Choice',
-- when (l27_mfyp_crs_rank = '10') then count(l27_mfyp_id) end as '10th Choice'
from dbo.INA_CRS
group by l27_mfyp_crs_id, l27_mfyp_crs_pair1, l27_mfyp_crs_pair2, l27_mfyp_crs_rank

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Put the COUNT outside the CASE.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

ajspencer (4/22/2015)
Hollo,
I am in need of some coding help..... I'm trying to transpose rows to columns at the same time do a count of the rows without getting duplicate crs_id.
select l27_mfyp_crs_id, l27_mfyp_crs_pair1, l27_mfyp_crs_pair2,
case
when (l27_mfyp_crs_rank = '1') then count(l27_mfyp_id) end as '1st Choice'
-- when (l27_mfyp_crs_rank = '2') then count(l27_mfyp_id) end as '2nd Choice'
-- when (l27_mfyp_crs_rank = '3') then count(l27_mfyp_id) end as '3rd Choice',
-- when (l27_mfyp_crs_rank = '4') then count(l27_mfyp_id) end as '4th Choice',
-- when (l27_mfyp_crs_rank = '5') then count(l27_mfyp_id) end as '5th Choice',
-- when (l27_mfyp_crs_rank = '6') then count(l27_mfyp_id) end as '6th Choice',
-- when (l27_mfyp_crs_rank = '7') then count(l27_mfyp_id) end as '7th Choice',
-- when (l27_mfyp_crs_rank = '8') then count(l27_mfyp_id) end as '8th Choice',
-- when (l27_mfyp_crs_rank = '9') then count(l27_mfyp_id) end as '9th Choice',
-- when (l27_mfyp_crs_rank = '10') then count(l27_mfyp_id) end as '10th Choice'
from dbo.INA_CRS
group by l27_mfyp_crs_id, l27_mfyp_crs_pair1, l27_mfyp_crs_pair2, l27_mfyp_crs_rank

You seem to be not using the case expression correctly. The stuff you have commented indicates you think it will return multiple rows and it can't do that. Your query seems like a basic cross tab with a bit of misunderstanding going on. I think your query should be something close to this.

select l27_mfyp_crs_id, l27_mfyp_crs_pair1, l27_mfyp_crs_pair2,

SUM(case when (l27_mfyp_crs_rank = '1') then 1 else 0 end) as '1st Choice'

, SUM(case when (l27_mfyp_crs_rank = '2') then 1 else 0 end) as '2nd Choice'

-- when (l27_mfyp_crs_rank = '3') then count(l27_mfyp_id) end as '3rd Choice',

-- when (l27_mfyp_crs_rank = '4') then count(l27_mfyp_id) end as '4th Choice',

-- when (l27_mfyp_crs_rank = '5') then count(l27_mfyp_id) end as '5th Choice',

-- when (l27_mfyp_crs_rank = '6') then count(l27_mfyp_id) end as '6th Choice',

-- when (l27_mfyp_crs_rank = '7') then count(l27_mfyp_id) end as '7th Choice',

-- when (l27_mfyp_crs_rank = '8') then count(l27_mfyp_id) end as '8th Choice',

-- when (l27_mfyp_crs_rank = '9') then count(l27_mfyp_id) end as '9th Choice',

-- when (l27_mfyp_crs_rank = '10') then count(l27_mfyp_id) end as '10th Choice'

from dbo.INA_CRS

group by l27_mfyp_crs_id, l27_mfyp_crs_pair1, l27_mfyp_crs_pair2, l27_mfyp_crs_rank

Notice I only coded the first 2 choices, I figured you could finish them. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ajspencer SSC Enthusiast Points: 197 More actions · Answer 3

ajspencer

SSC Enthusiast

Points: 197

April 22, 2015 at 2:31 pm

#1792859

Thanks for your help....I understand.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Luis and Sean beat me to it... I was going to add, here's a good article on this subject:

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001