Author |
Topic |
specialk9203
Starting Member
11 Posts |
Posted - 2014-07-02 : 10:41:09
|
I am looking to transpose a row into multiple columns. I have a member's data who might be associated with multiple labs for that one member. Instead of having multiple records for that member, I would like to have one record per member with multiple rows for the lab data. any help would be greatly appreciated!! |
|
specialk9203
Starting Member
11 Posts |
Posted - 2014-07-02 : 10:41:59
|
Sorry, I am using SQL Server 2008 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-02 : 10:59:58
|
You would need to use pivoting. Depending on your data you may be able to use the PIVOT operator in SQL 2008 http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx or you may need to use dynamic pivoting http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx |
|
|
specialk9203
Starting Member
11 Posts |
Posted - 2014-07-02 : 11:25:37
|
Thanks for the reply James, I was unable to figure out how to incorporate that into my code. Any ideas? The different lab type numbers are 6,7,8,11,19, and Null if the member did not have a labtype.SELECT DISTINCT m.cCampaignId, m.HIC, m.HealthPlanID, m.FirstName, m.LastName, m.DOB, m.Gender, m.HomePhone, m.LanguageId, m.Address, m.City, m.State, m.Zip, m.cSchedulerId, m.cProviderId, m.cCodingStatusId, m.cOutreachStatusId, m.cProgramId, m.PCPName, m.HPProviderID, dbo.RptCampaigns.CampaignName, dbo.RptCampaigns.CampaignStartDate, dbo.RptLanguages.Language, dbo.RptSchedulers.SchedulerLastName, dbo.RptSchedulers.SchedulerFirstName, dbo.RptProviders.ProviderLastName, dbo.RptProviders.ProviderFirstName, dbo.RptOutreachStatus.OutreachStatusDesc, dbo.RptOutreachStatus.OutreachStatusCode, m.MemberId, dbo.RptLabMembers.LabTypeIdFROM dbo.RptMembers AS m INNER JOIN dbo.RptCampaigns AS c ON c.CampaignID = m.cCampaignId INNER JOIN dbo.RptCampaigns ON m.cCampaignId = dbo.RptCampaigns.CampaignID INNER JOIN dbo.RptLanguages ON m.LanguageId = dbo.RptLanguages.LanguageId LEFT OUTER JOIN dbo.RptLabMembers ON m.MemberId = dbo.RptLabMembers.MemberID LEFT OUTER JOIN dbo.RptOutreachStatus ON m.cOutreachStatusId = dbo.RptOutreachStatus.OutreachStatusID LEFT OUTER JOIN dbo.RptProviders ON m.cProviderId = dbo.RptProviders.ProviderId LEFT OUTER JOIN dbo.RptSchedulers ON m.cSchedulerId = dbo.RptSchedulers.SchedulerIDWHERE (m.PlanID IN (62, 63)) AND (m.cProgramId IN (1, 5)) AND (YEAR(c.CampaignStartDate) = YEAR(DATEADD(dd, - 1, GETDATE()))) |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-02 : 11:34:17
|
Can you post some (simplified) sample data that you are getting from this query and what you want it to look like after pivoting? |
|
|
specialk9203
Starting Member
11 Posts |
Posted - 2014-07-02 : 11:48:34
|
You bet. This is the result for a member. He/She has multiple records because of the dbo.RptLabMembers.LabTypeId. I would like one record per member. so basically pivoting out the labtypeID believe is the solution, but was unable to do that. Sorry for the messy data, but it is the last field in each row (6,7,8,19)cCampaignId HIC HealthPlanID FirstName LastName DOB Gender HomePhone LanguageId Address City State Zip cSchedulerId cProviderId cCodingStatusId cOutreachStatusId cProgramId PCPName HPProviderID CampaignName CampaignStartDate Language SchedulerLastName SchedulerFirstName ProviderLastName ProviderFirstName OutreachStatusDesc OutreachStatusCode MemberId LabTypeId9999 12345 H12345 SQL SERVER 00:00.0 M 3.05E+09 1 123 Main Eau Claire MI 54729 NULL NULL NULL NULL 5 DR Jones NULL TEST ####### ENGLISH NULL NULL NULL NULL NULL NULL 1234567 69999 12345 H12345 SQL SERVER 00:00.0 M 3.05E+09 1 123 Main Eau Claire MI 54729 NULL NULL NULL NULL 5 DR Jones NULL TEST ####### ENGLISH NULL NULL NULL NULL NULL NULL 1234567 79999 12345 H12345 SQL SERVER 00:00.0 M 3.05E+09 1 123 Main Eau Claire MI 54729 NULL NULL NULL NULL 5 DR Jones NULL TEST ####### ENGLISH NULL NULL NULL NULL NULL NULL 1234567 89999 12345 H12345 SQL SERVER 00:00.0 M 3.05E+09 1 123 Main Eau Claire MI 54729 NULL NULL NULL NULL 5 DR Jones NULL TEST ####### ENGLISH NULL NULL NULL NULL NULL NULL 1234567 19 |
|
|
specialk9203
Starting Member
11 Posts |
Posted - 2014-07-02 : 11:49:56
|
the end result would be. one line per member9999 12345 H12345 SQL SERVER 00:00.0 M 3.05E+09 1 123 Main Eau Claire MI 54729 NULL NULL NULL NULL 5 DR Jones NULL TEST ####### ENGLISH NULL NULL NULL NULL NULL NULL 1234567 6 7 8 19 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-02 : 12:08:46
|
[code]SELECT * FROM ( -- this is your current query SELECT DISTINCT m.cCampaignId, m.HIC, m.HealthPlanID, m.FirstName, m.LastName, m.DOB, m.Gender, m.HomePhone, m.LanguageId, m.Address, m.City, m.State, m.Zip, m.cSchedulerId, m.cProviderId, m.cCodingStatusId, m.cOutreachStatusId, m.cProgramId, m.PCPName, m.HPProviderID, dbo.RptCampaigns.CampaignName, dbo.RptCampaigns.CampaignStartDate, dbo.RptLanguages.Language, dbo.RptSchedulers.SchedulerLastName, dbo.RptSchedulers.SchedulerFirstName, dbo.RptProviders.ProviderLastName, dbo.RptProviders.ProviderFirstName, dbo.RptOutreachStatus.OutreachStatusDesc, dbo.RptOutreachStatus.OutreachStatusCode, m.MemberId, dbo.RptLabMembers.LabTypeId FROM dbo.RptMembers AS m INNER JOIN dbo.RptCampaigns AS c ON c.CampaignID = m.cCampaignId INNER JOIN dbo.RptCampaigns ON m.cCampaignId = dbo.RptCampaigns.CampaignID INNER JOIN dbo.RptLanguages ON m.LanguageId = dbo.RptLanguages.LanguageId LEFT OUTER JOIN dbo.RptLabMembers ON m.MemberId = dbo.RptLabMembers.MemberID LEFT OUTER JOIN dbo.RptOutreachStatus ON m.cOutreachStatusId = dbo.RptOutreachStatus.OutreachStatusID LEFT OUTER JOIN dbo.RptProviders ON m.cProviderId = dbo.RptProviders.ProviderId LEFT OUTER JOIN dbo.RptSchedulers ON m.cSchedulerId = dbo.RptSchedulers.SchedulerID WHERE (m.PlanID IN (62, 63)) AND (m.cProgramId IN (1, 5)) AND (YEAR(c.CampaignStartDate) = YEAR(DATEADD(dd, - 1, GETDATE())))) sPIVOT (MAX(LabTypeId) FOR LabTypeId IN ([6],[7],[8],[11],[19]))P[/code] |
|
|
specialk9203
Starting Member
11 Posts |
Posted - 2014-07-02 : 13:06:15
|
Thanks James, So do I run your code off the dataset I created or run it as one query. As one query I got an error syntax. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-02 : 14:02:28
|
Not sure what you mean by dataset. The query I posted parses correctly on my computer; and syntax errors should be caught while parsing. What is the error you are getting? |
|
|
specialk9203
Starting Member
11 Posts |
Posted - 2014-07-02 : 14:22:05
|
This is the error message:Msg 170, Level 15, State 1, Line 20Line 20: Incorrect syntax near 'PIVOT' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-02 : 14:28:48
|
What version of SQL Server are you on? PIVOT works only on SQL 2005 or later. |
|
|
specialk9203
Starting Member
11 Posts |
Posted - 2014-07-02 : 14:41:59
|
That makes sense then, I use 2008, is there logic buit in 2008 to accomodate? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-02 : 15:59:17
|
PIVOT operator is available in SQL 2008, so you should not get a syntax error. Can you do two things:-- 1. Copy this code to a query window and execute it. It is trying to find the "compatibility level" of the database. As long as it returns 90 or above, you should be ok.SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName' 2. Copy the exact code that I posted earlier, past it to a query window and select Query -> Parse from the menu and see if it gives you any errors. |
|
|
specialk9203
Starting Member
11 Posts |
Posted - 2014-07-02 : 17:08:50
|
Hi James, I tried your first option and got an error message:Msg 208, Level 16, State 1, Line 1Invalid object name 'sys.databases'2nd option didnt work either, same error as before. I appreciate the patience as I am very new to SQL Server. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-02 : 17:26:37
|
That seems to indicate that you are on a version older than SQL 2005. Can you run this query and post the results?SELECT @@VERSION orEXEC sp_dbcmptlevel @dbname = 'YourDatabaseNameHere' How did you figure out that you are using SQL 2008? |
|
|
specialk9203
Starting Member
11 Posts |
Posted - 2014-07-02 : 19:24:24
|
James you would be correct - Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) I considered the version of SQL Server MAnagement Studio to be the edition I had. Is there a solution to my problem using the 2000 version? |
|
|
|