Convert Rows to Columns Using PIVOT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert Rows to Columns Using PIVOT

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-09-28 : 09:46:57

Hey guys,

I'm trying to convert a recordset of rows to columns using PIVOT but keep getting the error "Incorrect syntax near 'PIVOT'". Here's the original table format:

encounterId, chartTime, attributeId, valueString
21, 2010-05-05 07:00:00:000, 11411, Yes
21, 2010-05-05 07:00:00:000, 11410, Yes
19, 2010-05-05 07:00:00:000, 11411, Yes
19, 2010-05-05 07:00:00:000, 11410, Yes

I want it to display as follows:

encounterId, chartTime, 11411, 11410
21, 2010-05-05 07:00:00:000, Yes, Yes
19, 2010-05-05 07:00:00:000, Yes, Yes

Below is what I attempted based on what I have researched thus far:

------
SELECT encounterId, chartTime, [11410] AS RN_S_OFF, [11411] AS RN_S_ON

FROM (

SELECT encounterId, chartTime, attributeId, valueString

FROM PtAssessment

) AS T

PIVOT (

COUNT(encounterId)

FOR attributeId IN ([11410], [11411])

) AS P
------

The part that is confusing is what aggregate function to use since I'm not really adding, counting or averaging anything.

Guidance will be much appreciated.
Thank you!
J.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 10:44:10

use MAX(valueString)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-09-28 : 10:49:22

Still same error: "Incorrect syntax near 'PIVOT'".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 10:52:07

are you using SQL 2005 or above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-09-28 : 11:12:11

Yes we are on SQL Server 2008 R2

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-09-28 : 11:14:13

Sorry I answered the wrong thread!

quote:
Originally posted by AdamWest

Yes we are on SQL Server 2008 R2

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-09-28 : 11:22:04

I'm using SQL Server 2005 but I have just learned that the DB I am quering is only on SQL Server 2000. I think that is my problem right there. Any ideas for a workaround?

Thanks a lot for the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 11:26:33

[code]
select encounterId, chartTime,
max(case when attributeId=11411 then valueString else null end) as 11411,
max(case when attributeId=11410 then valueString else null end) as 11410
from PtAssessment
group by encounterId, chartTime
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-09-29 : 11:52:18

Thank you for your input. This was just a simplified idea of a more complex query I am trying to develop but after analyzing the output I don't think this is going to work.

Thanks again for your prompt and educating feedback.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-29 : 12:07:26

why do you think it wont work? Probably give us an idea whats your exact scenario so that we can see if we can adapt above query to meet that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-09-29 : 13:59:10

OK great, I will take you up on that offer then. But, there are a few things I need clarified first. I will do my best to try and explain my exact scenario in a separate post but that might not happen until Monday. Stay tuned and thanks a lot for all your help!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 00:54:26

ok no probs...will check out once you come back with explanation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-10-03 : 14:32:29

OK, here is the main table once again:

Table Name: PtAssessment
------------------------
ptAssessmentId (PK, int)
attributeId (FK, int)
encounterId (FK, int)
chartTime (datetime)
clinicalUnitId (FK, int)
careProviderId (FK, int)
terseForm (nvarchar(32))

Below I have the basic script with joins to a few other tables. In addition to the fields I have already listed in the SELECT clause, I need to convert rows from the PtAssessment table above to columns. I just need to capture the chartTime and terseForm values WHERE attributeId IN (13142, 14138, 14155, 5961, 13985, 5463, 11410, 11411). This has become difficult because the time for attributeId 13142 might be different then the time for attributeId 14138 resulting in multiple rows with NULL values. I just want 1 row per date (yyyy/mm/dd/) regardless of the time. Not sure if this is even possible. Am I making sense?

SELECT

- D_CareProvider.lastName AS NAME,
- D_Encounter.MRN AS MRN,
- PtBedStay.bedLabel AS BED,
- ...

FROM

- D_CareProvider,
- D_ClinicalUnit,
- D_Encounter,
- PtAssessment,
- PtBedStay

WHERE

- PtAssessment.careProviderId = D_CareProvider.careProviderId
- AND ptAssessment.clinicalUnitId = D_ClinicalUnit.clinicalUnitId
- AND PtAssessment.encounterId = D_Encounter.encounterId
- AND PtBedStay.encounterId = D_Encounter.encounterId
- AND D_ClinicalUnit.clinicalUnitId = 19
- AND D_CareProvider.professionalTitle = 'RN'

Please let me know if you need clarification on something and thanks a million for your will to help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 14:39:31

for considering only date part for grouping just use

DATEADD(dd,DATEDIFF(dd,0,chartTime),0)

so that it ignores the time part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-10-07 : 15:49:45

Well, your suggestion seemed promising at first but after several tests I am getting duplicate and inconsistent results. Unfortunately, the database I am working with is probably the worst design I have seen in my career and that is what is making this so difficult. I think I'm probably gonna end up doing a stored procedure so I can create temporary tables, store the data in a presentable format then extract it. I am running out of ideas.

Thanks for your help though. You've certainly tought me a few new tricks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-08 : 00:49:13

first question is whether you're including date with timepart also in your group. then you will get lot of duplicates. thats why i gave you suggestion to strip off timepart from date and then group it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-10-12 : 15:40:26

Yes, I did strip the time off the data in the SELECT clause and tried with and without time in the GROUP BY clause. Trust me, I think I have exercised every possible option. This will have to be a multi-step query/stored procedure with temporary tables because there are too many different scenarios with the data.

Thanks so much for everything!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 01:59:05

is there any other field with unique id values for same date thats involved in GROUP BY? if yes, then that can also screw up results to include duplicate rows for same date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2011-10-27 : 09:05:37

What I want to accomplish here is quite complex to do in a single SQL statement, especially with the way the data is stored in the database. Let's forget about this idea for now but stay tunned for my next post if you're still interested.

Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources