Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Rows of Data To Single Row With One ID Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 3:53 PM
Points: 5, Visits: 64
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
Posted Tuesday, October 16, 2012 3:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 26, 2014 12:28 PM
Points: 4, Visits: 38
Please see this similar question:
http://ask.sqlservercentral.com/questions/94213/converting-multiple-rows-into-single-row.html
Post #1373553
Posted Tuesday, October 16, 2012 3:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 36,944, Visits: 31,448
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1373559
Posted Tuesday, October 16, 2012 4:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 3,497, Visits: 7,547
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1373568
Posted Tuesday, October 16, 2012 11:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:14 AM
Points: 3,618, Visits: 5,254
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/



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1373632
Posted Wednesday, October 17, 2012 7:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 36,944, Visits: 31,448
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1373800
Posted Wednesday, October 17, 2012 11:57 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 18, 2013 4:48 PM
Points: 46, Visits: 113
Edgar, i had a similar situation and here in the code i used. obviously you'll sub in your details

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
Posted Thursday, October 18, 2012 9:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 3:53 PM
Points: 5, Visits: 64
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 »

Add to briefcase

Permissions Expand / Collapse