Multiple Rows of Data To Single Row With One ID

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Multiple Rows of Data To Single Row With One...

Multiple Rows of Data To Single Row With One ID

Rate Topic

Display Mode

Topic Options

Author

Message

edgar58

Posted Tuesday, October 16, 2012 3:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 16, 2012 7:32 AM
Points: 4, Visits: 35

Hi,

I'm trying to alter a table to contain data in a horizontal (row) format rather than a vertical (column) format.

The data I have is similar to:

Acct_ID,Name,Start_Date,End_Date,Program,Program_ID
1234,Joe Smith,1/1/2012,1/15/2012,Code1,5678
1234,Joe Smith,2/1/2012,2/15/2012,Code2,5678
1234,Joe Smith,1/5/2012,1/15/2012,Code3,5678
9101,Bob Frank,2/1/2012,2/15/2012,Code1,1213
9101,Bob Frank,3/1/2012,3/15/2012,Code2,1213
9101,Bob Frank,4/1/2012,4/15/2012,Code3,1213
9101,Bob Frank,5/1/2012,5/15/2012,Code4,1213 ...and so on

I'd like for it to be:

Acct_ID, Name, Start_Date1, End_Date1, Program1, Start_Date2, End_Date2, Program2, Start_Date3, End_Date3, Program3, Start_Date4...,Program_ID
1234,Joe Smith, 1/1/2012, 1/15/2012, Code1, 2/1/2012, 2/15/2012, Code2...etc

I've explored using Pivot and Case. All help is appreciated.

Thanks!

Post #1373541

willspurgeon-1013241

Posted Tuesday, October 16, 2012 3:37 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 12:28 PM
Points: 1, Visits: 17

Please see this similar question:
http://ask.sqlservercentral.com/questions/94213/converting-multiple-rows-into-single-row.html

Post #1373553

Jeff Moden

Posted Tuesday, October 16, 2012 3:59 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

edgar58 (10/16/2012)

I've explored using Pivot and Case. All help is appreciated.

Thanks!

Please see the following article. Change SUM to MAX to do text and dates, etc.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1373559

Luis Cazares

Posted Tuesday, October 16, 2012 4:12 PM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 958, Visits: 1,917

edgar58 (10/16/2012)

Hi,

I've explored using Pivot and Case. All help is appreciated.

Thanks!

Have you explored using Case (Cross Tabs) with ROW_NUMBER with PARTITION BY Acct_ID?

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1373568

dwain.c

Posted Tuesday, October 16, 2012 11:42 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:01 PM
Points: 2,340, Visits: 3,163

Jeff Moden (10/16/2012)

edgar58 (10/16/2012)

I've explored using Pivot and Case. All help is appreciated.

Thanks!

Please see the following article. Change SUM to MAX to do text and dates, etc.

I think you may have forgotten to include the links:
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1373632

Jeff Moden

Posted Wednesday, October 17, 2012 7:56 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

dwain.c (10/16/2012)

Jeff Moden (10/16/2012)

edgar58 (10/16/2012)

I've explored using Pivot and Case. All help is appreciated.

Thanks!

Please see the following article. Change SUM to MAX to do text and dates, etc.

I think you may have forgotten to include the links:
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/

Definitely a NEC problem on my part. Thanks for the cover, Dwain.

NEC = Not Enough Coffee

--Jeff Moden

Post #1373800

danawexler

Posted Wednesday, October 17, 2012 11:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:57 AM
Points: 41, Visits: 77

Edgar, i had a similar situation and here in the code i used. obviously you'll sub in your details BigGrin

DECLARE @tColors TABLE
(rownum INT IDENTITY (1,1) primary key,
CustomerID int,
Color varchar(50)
)
declare
@rownum int = 1,
@maxrow int = 0,
@ColorList varchar(2000) = null,
@CustomerID int = 26

BEGIN
INSERT INTO @tColors
(CustomerID, Color)
SELECT distinct co.CustomerID, co.Color
from Testing.Colors co
WHERE CustomerID = @CustomerID
ORDER BY co.CustomerID, co.Color DESC
SELECT @maxrow = count(1) from @tColors
WHILE @rownum <= @maxrow
BEGIN
set @ColorList = CASE WHEN @ColorList IS NULL THEN '' ELSE @ColorList + ', ' end + (SELECT color from @tColors where rownum = @rownum)
set @rownum = @rownum + 1
END
SELECT @ColorList as CustomersColors
END

Dana

"Drats! Foiled again!"

Post #1373979

edgar58

Posted Thursday, October 18, 2012 9:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 16, 2012 7:32 AM
Points: 4, Visits: 35

Thank you all for the information. I ended up using Case. However, I have some instances where one ID has the same program but for different date spans.

Select P.ID,P.SSN,P.MCD_ID,P.Lname,P.Fname,P.Admit,P.Disch,RCP_Fname,RCP_Lname,RCPDOB,RcpMedicaidID,
MAX(Case WHEN P.EligibleDesc = 'Program' Then P.Eligible END) As 'Eligible_PRG',
MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligibleType END) As 'EligibleType_PRG',
MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligibleDesc END) As 'Elig_PRG',
MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligBeginDate END) As 'EligBegin_PRG',
MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligEndDate END) As 'EligEnd_PRG',
From Eligibility_Final P
group by P.Patacctnum,P.SSN,P.MCD_ID_Instate,P.Lname,P.Fname,P.Admit,P.Disch,RCP_Fname,RCP_Lname,RCPDOB,RcpMedicaidID

This produces semi-desired results; however, when some records have the same program but different date spans only the MAX dates are selected and the other records are eliminated. If I remove the MAX(Dates) then I get repeating rows. Can someone help me out with this issue?

The results I want would be:
ID,SSN,MCD_ID,Lname,Fname,Admit,Disch,RCP_Fname,RCP_Lname,RCPDOB,RcpMedicaidID,Eligible_PRG,EligibleType_PRG,Elig_PRG,EligBegin_PRG,EligEnd_PRG,Eligible_PRG,EligibleType_PRG,Elig_PRG,EligBegin_PRG,EligEnd_PRG

Where the information in bold is the data for the first date span and the information in italics is the data for the second date span and so on.

Thanks!!

Post #1374468

« Prev Topic | Next Topic »

Permissions