Home » SQL & PL/SQL » SQL & PL/SQL » How to denormalize multiple records into 1 row- (N/A)
How to denormalize multiple records into 1 row- [message #674989] Thu, 28 February 2019 14:49 Go to next message
newuser01
Messages: 4
Registered: October 2011
Location: east coast
Junior Member
A question for the SQL Gurus-
Source data comes from OLTP system- Database table or Flat file source data comes to us like the below i.e.: 3 cols- ssn, ref_no, ref_amt
For each ssn, we can have up to 20 different ref_no and the amount in 20 rows in the source file-

ssn ref_no ref_amt
--- ------ -------
1 101 20
1 201 22
1 301 33
1 401 44
2 101 10
2 200 15

To insert into OLAP system - Looking for a solution using SQL SELECT/INSERT statement logic, to load data into a Target table with 20 columns for each of these ref numbers for the same ssn. Simply put- I would like to insert the above records into Target db table like this below-

ssn ref_no1 ref_amt1 ref_no2 ref_amt2 ref_no3 ref_amt3 ref_no4 ref_amt4
--- ------- -------- ------- -------- ------- --------- ------- --------
1 101 20 201 22 301 33 401 44
2 101 10 200 15

Any knowhow sharing knowledge would be greatly appreciated.
Thank you very much.

PS: Not sure if this is the right discussion board for this question. If not, please refer me to the correct one. Thanks very much.

[Updated on: Thu, 28 February 2019 14:58] by Moderator

Report message to a moderator

Re: How to denormalize multiple records into 1 row- [message #674991 is a reply to message #674989] Thu, 28 February 2019 15:04 Go to previous message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Search for PIVOT.

Previous Topic: Huge XML data in BLOB columns
Next Topic: date validation
Goto Forum:
  


Current Time: Mon Jan 27 09:34:05 CST 2020