May 20, 2011 at 10:49 am
Here's an example data set (pipe delimited) that I'm trying to take from vertical to horizontal. I have tried several approaches, but I keep hitting a wall...and our DBA is out due to an injury. Any and all help will be GREATLY appreciated.
Original Data Set
AcctNum | InsCode | InsName
1234 | 10 | Acme - PPO
1234 | 367 | Medicare Plan A
1234 | 14 | Medicaid Plan A
1235 | 10 | Acme - PPO
1235 | 14 | Medicaid Plan A
1236 | 368 | Medicare Plan B
1237 | 367 | Medicare Plan A
1237 | 14 | Medicaid Plan A
The data needs to look like this:
AcctNum | InsCode1 | InsName1 | InsCode2 | InsName2 | InsCode3 | InsName3
1234 | 10 | Acme - PPO | 367 | Medicare Plan A | 14 | Medicaid Plan A
1235 | 10 | Acme - PPO | Medicaid Plan A
1236 | 368 | Medicare Plan B
1237 | 367 | Medicare Plan A | Medicaid Plan A
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
May 20, 2011 at 11:17 am
Credit to this article:
http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]
USE tempdb
GO
IF OBJECT_ID(N'tempdb..#SomeTable1') > 0
DROP TABLE #SomeTable1;
GO
CREATE TABLE #SomeTable1
(
AcctNum INT,
InsCode INT,
InsName VARCHAR(100)
)
GO
INSERT INTO #SomeTable1 (AcctNum, InsCode, InsName)
SELECT 1234, 10, 'Acme - PPO'
UNION ALL SELECT 1234, 367, 'Medicare Plan A'
UNION ALL SELECT 1234, 14, 'Medicaid Plan A'
UNION ALL SELECT 1235, 10, 'Acme - PPO'
UNION ALL SELECT 1235, 14, 'Medicaid Plan A'
UNION ALL SELECT 1236, 368, 'Medicare Plan B'
UNION ALL SELECT 1237, 367, 'Medicare Plan A'
UNION ALL SELECT 1237, 14, 'Medicaid Plan A'
GO
WITH cte
AS (
SELECT AcctNum,
InsCode,
InsName,
ROW_NUMBER() OVER (PARTITION BY AcctNum ORDER BY AcctNum) AS row_num
FROM #SomeTable1
)
SELECT AcctNum,
MAX(CASE WHEN row_num = 1 THEN InsCode
END) AS [InsCode1],
MAX(CASE WHEN row_num = 1 THEN InsName
END) AS [InsName1],
MAX(CASE WHEN row_num = 2 THEN InsCode
END) AS [InsCode2],
MAX(CASE WHEN row_num = 2 THEN InsName
END) AS [InsName2],
MAX(CASE WHEN row_num = 3 THEN InsCode
END) AS [InsCode3],
MAX(CASE WHEN row_num = 3 THEN InsName
END) AS [InsName3]
FROM cte
GROUP BY AcctNum ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 20, 2011 at 11:21 am
THANK YOU!!! That was exactly what I was looking for!
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
May 20, 2011 at 11:33 am
You're welcome!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply