SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Transpose 1 column to rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

specialk9203
Starting Member

USA
11 Posts

Posted - 07/02/2014 :  10:41:09  Show Profile  Reply with Quote
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

USA
11 Posts

Posted - 07/02/2014 :  10:41:59  Show Profile  Reply with Quote
Sorry, I am using SQL Server 2008
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 07/02/2014 :  10:59:58  Show Profile  Reply with Quote
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
Go to Top of Page

specialk9203
Starting Member

USA
11 Posts

Posted - 07/02/2014 :  11:25:37  Show Profile  Reply with Quote
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.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())))
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 07/02/2014 :  11:34:17  Show Profile  Reply with Quote
Can you post some (simplified) sample data that you are getting from this query and what you want it to look like after pivoting?
Go to Top of Page

specialk9203
Starting Member

USA
11 Posts

Posted - 07/02/2014 :  11:48:34  Show Profile  Reply with Quote
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 LabTypeId

9999 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
9999 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 7
9999 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 8
9999 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
Go to Top of Page

specialk9203
Starting Member

USA
11 Posts

Posted - 07/02/2014 :  11:49:56  Show Profile  Reply with Quote
the end result would be. one line per member

9999 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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 07/02/2014 :  12:08:46  Show Profile  Reply with Quote
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())))
) s
PIVOT (MAX(LabTypeId) FOR LabTypeId IN ([6],[7],[8],[11],[19]))P
Go to Top of Page

specialk9203
Starting Member

USA
11 Posts

Posted - 07/02/2014 :  13:06:15  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 07/02/2014 :  14:02:28  Show Profile  Reply with Quote
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?
Go to Top of Page

specialk9203
Starting Member

USA
11 Posts

Posted - 07/02/2014 :  14:22:05  Show Profile  Reply with Quote
This is the error message:

Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'PIVOT'
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 07/02/2014 :  14:28:48  Show Profile  Reply with Quote
What version of SQL Server are you on? PIVOT works only on SQL 2005 or later.
Go to Top of Page

specialk9203
Starting Member

USA
11 Posts

Posted - 07/02/2014 :  14:41:59  Show Profile  Reply with Quote
That makes sense then, I use 2008, is there logic buit in 2008 to accomodate?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 07/02/2014 :  15:59:17  Show Profile  Reply with Quote
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.
Go to Top of Page

specialk9203
Starting Member

USA
11 Posts

Posted - 07/02/2014 :  17:08:50  Show Profile  Reply with Quote
Hi James,

I tried your first option and got an error message:
Msg 208, Level 16, State 1, Line 1
Invalid 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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 07/02/2014 :  17:26:37  Show Profile  Reply with Quote
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
or
EXEC sp_dbcmptlevel @dbname = 'YourDatabaseNameHere'
How did you figure out that you are using SQL 2008?

Edited by - James K on 07/02/2014 17:27:06
Go to Top of Page

specialk9203
Starting Member

USA
11 Posts

Posted - 07/02/2014 :  19:24:24  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000