Pivoting Problem - SQL Server Forums

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

Pivoting Problem

Author

Topic

ransomsreason
Starting Member

2 Posts

Posted - 2013-04-03 : 18:24:05

Hi, so I'm working with data on mental health patients and I need to compare their scores on an assessment at their admit and their discharge from a program. Some of them have been admitted and discharged multiple times and I'd need a comparison for each time. My data currently looks like this, with a new row for each assessment:

And I'd like it to look something like this afterwards:

I can imagine it's very complicated to get it exactly how I want it, but any solutions or even pointers in the right direction would be much appreciated! Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-04 : 01:09:23

[code]
WITH PersonDetails
AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY Person,TestInterval ORDER BY Date) AS Seq
FROM Table
)
SELECT pd.Person,pd.Score AS BeforeScore,pd.[Date] AS BeforeDate,pd1.Score AS AfterScore, pd1.[Date] AS AfterDate
FROM PersonDetails pd
LEFT JOIN PersonDetails pd1
ON pd1.Person = pd.Person
AND pd1.TestInterval = pd.TestInterval
AND pd1.Seq = pd.Seq + 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-04 : 03:03:19

[code]
DECLARE @tab TABLE (Person varchaR(10), Date DATE, TestInterval varchaR(15), Score INT)
INSERT INTO @tab
SELECT 'Larry', '3/11/2012', 'Admit', 12 union all
SELECT 'Larry', '3/12/2012', 'Discharge', 16 union all
SELECT 'Larry', '3/13/2012', 'Admit', 14 union all
SELECT 'Larry', '3/14/2012', 'Discharge', 18 union all
SELECT 'Sushan', '3/15/2012', 'Admit', 21 union all
SELECT 'Sushan', '3/16/2012', 'Discharge', 26 union all
SELECT 'Kevin', '3/17/2012', 'Admit', 16 union all
SELECT 'Kevin', '3/18/2012', 'Discharge', 19 union all
SELECT 'Kevin', '3/19/2012', 'Admit', 17
;WITH CTE AS(SELECT *,ROW_NUMBER() OVER (PARTITION BY Person, testInterval ORDER BY Date) AS Seq FROM @tab)
SELECT Person,
MAX(case when TestInterval = 'Admit' then DATE END) as BeforeDate,
MAX(case when TestInterval = 'Discharge' then DATE END) as AfterDate,
MAX(case when TestInterval = 'Admit' then Score END) as BeforeScore,
MAX(case when TestInterval = 'Discharge' then Score END) as AfterScore
FROM CTE
GROUP BY Person, Seq
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources