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
 Transpose 1 column to rows

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

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

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

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 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

11 Posts

Posted - 2014-07-02 : 11:49:56
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
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())))
) s
PIVOT (MAX(LabTypeId) FOR LabTypeId IN ([6],[7],[8],[11],[19]))P

[/code]
Go to Top of Page

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

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

specialk9203
Starting Member

11 Posts

Posted - 2014-07-02 : 14:22:05
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
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.
Go to Top of Page

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

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

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 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
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
or
EXEC sp_dbcmptlevel @dbname = 'YourDatabaseNameHere'
How did you figure out that you are using SQL 2008?
Go to Top of Page

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

- Advertisement -