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.
| Author |
Topic |
|
SQLoco
Starting Member
2 Posts |
 Posted - 2012-09-14 : 13:03:02
|
| Original Data is in this format:FULL ID YEAR CATEGORY DESCRIPTION CATEGORY CODE LEVEL BENEFIT COSTH1837-2 2012 Inpatient Hospital Care 3 1 No limit NULLH1837-2 2012 Inpatient Hospital Care 3 2 No Limit(s) NULLH1837-2 2012 Inpatient Hospital Care 3 3 Days 1 - 5 $330SQL Statment used: Min(Case [Level] When '1' Then [Benefit] End) [IHC-1], Min(Case [Level] When '2' Then [Benefit] End) [IHC-2], Min(Case [Level] When '3' Then [Benefit] End) [IHC-3], Min(Case [Level] When '1' Then [Cost] End) [IHCco-1], Min(Case [Level] When '2' Then [Cost] End) [IHCco-2], Min(Case [Level] When '3' Then [Cost] End) [IHCco-3],Output:FULL ID CATEGORY DESCRIPTION CATEGORY CODE LEVEL BENEFIT IHC-1 IHC-2 IHC-3 IHCco-1 IHCco-2 IHCco-3H1837-1 Inpatient Hospital Care 3 1 No limit No limit NULL NULL NULL NULL NULL H1837-1 Inpatient Hospital Care 3 2 No limit(s) NULL No limit(s) NULL NULL NULL NULLH1837-1 Inpatient Hospital Care 3 3 Days 1-5 NULL NULL DAYS 1-5 NULL NULL $330DESIRED OUTPUT:FULL ID CATEGORY DESCRIPTION CATEGORY CODE LEVEL IHC-1 IHC-2 IHC-3 IHCco-1 IHCco-2 IHCco-3H1837-1 Inpatient Hospital Care 3 1 No limit No Limit(s) DAYS 1-5 NULL NULL $330 As you can see I want to transpose the Data from vertical to Horizontal and to reduce three rows into one.QUESTION:What is the best way to accomplish this ? Thanks in Advance. ! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-14 : 13:13:11
|
| You need to remove LEVEL from the SELECT list and the GROUP BY clause. |
 |
|
|
SQLoco
Starting Member
2 Posts |
Posted - 2012-09-14 : 13:42:31
|
| It was that simple ?!!Thanks a Lot, it worked wonders. |
|
|
|
|
|
|
Not your ordinary "multiple rows into one case"