Need to pivot query results

Question

Need to pivot query results

oradbguru

Hall of Fame

Points: 3126
More actions
June 25, 2013 at 1:27 pm

#276649

Hello, I have the following query and I need the results in 1 row going across. Here is the query:
SELECT top(5)
ap.apptDte,
ap.begTime,
lc.locName,
ev.event,
pm.fname + ' ' + pm.lname
FROM
appts ap
join loc_mstr lc on lc.locID = ap.locID
join events ev on ev.eventID = ap.eventID
join prov_mstr pm on pm.provID = ap.rend_provID
WHERE
personID = '5D06AAE9-1B8D-461B-BAAB-633C1ED7ED43'
AND appt_date > GETDATE()
The results would have 25 columns on 1 row. Is this possible?
Thank you.

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

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

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

oradbguru (6/25/2013)
Hello, I have the following query and I need the results in 1 row going across. Here is the query:
SELECT top(5)
ap.apptDte,
ap.begTime,
lc.locName,
ev.event,
pm.fname + ' ' + pm.lname
FROM
appts ap
join loc_mstr lc on lc.locID = ap.locID
join events ev on ev.eventID = ap.eventID
join prov_mstr pm on pm.provID = ap.rend_provID
WHERE
personID = '5D06AAE9-1B8D-461B-BAAB-633C1ED7ED43'
AND appt_date > GETDATE()
The results would have 25 columns on 1 row. Is this possible?
Thank you.

Yes it is possible. Please read the links in my signature about cross tabs. If you still need help then read the first link in my signature for best practices when posting questions.

_______________________________________________________________

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/

oradbguru Hall of Fame Points: 3126 More actions · Answer 2

There is no aggregated data here. The results from this query would be a 5 rows with 5 columns. I just need to have 1 row with with 25 columns. Do you have any sample code with this?

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

Without sample data and expected results, I'm only taking a shot in the dark.

You should really read and understand the articles mentioned by Sean as there's some sample code in them.

I can't test my solution but it might give you an idea. Even if it worked, you must complete it.

WITH CTE as(

SELECT top(5)

ap.apptDte,

ap.begTime,

lc.locName,

ev.event,

pm.fname + ' ' + pm.lname AS name,

ROW_NUMBER() OVER( ORDER BY( SELECT NULL)) rn

FROM

appts ap

join loc_mstr lc on lc.locID = ap.locID

join events ev on ev.eventID = ap.eventID

join prov_mstr pm on pm.provID = ap.rend_provID

WHERE

personID = '5D06AAE9-1B8D-461B-BAAB-633C1ED7ED43'

AND appt_date > GETDATE())

SELECT MAX( CASE WHEN rn = 1 THEN apptDte END),

MAX( CASE WHEN rn = 1 THEN begTime END),

MAX( CASE WHEN rn = 1 THEN locName END),

MAX( CASE WHEN rn = 1 THEN event END),

MAX( CASE WHEN rn = 1 THEN name END),

MAX( CASE WHEN rn = 2 THEN apptDte END),

MAX( CASE WHEN rn = 2 THEN begTime END),

MAX( CASE WHEN rn = 2 THEN locName END),

MAX( CASE WHEN rn = 2 THEN event END),

MAX( CASE WHEN rn = 2 THEN name END)

--And so on

FROM CTE

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

oradbguru Hall of Fame Points: 3126 More actions · Answer 4

Louis, I think this is what I am looking for. Thank you!

David