Solved

SQL Code to flatten out records

Posted on 2009-04-02
6
524 Views
Last Modified: 2013-11-25
I have a table sitting in SQL Server. I have a need to "flatten out" a table.  Here is my basic table

Date            Person      Prize1      Prize2      Prize3
1/1/2009 11:01:02      Bill      Item1      Item2      Item3
1/1/2009 11:05:23      Bill      Item4      NULL      NULL
I need to manipulate the data so Bill is just one record. A 4th field would need to be added to the output. I dont care about the timestamp. The new record would just have a value of 1/1/2009.  Would need sql code to do this.

Result set would be:
Date             Person    Prize1     Prize2   Prize3   Prize4
1/1/2009      Bill           Item1       Item2     Item3     Item4
0
Comment
Question by:d1cjm1ex
  • 3
  • 2
6 Comments
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
what if second record also have prize1,2 and 3 along with 4, should first record need to be overwritten?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
SELECT Person, CONVERT(CHAR(8), Date, 112),
    MAX(CASE WHEN Prize2 IS NOT NULL THEN Prize1 ELSE '' END) AS Prize1,
    MAX(CASE WHEN Prize2 IS NOT NULL THEN Prize2 ELSE '' END) AS Prize2,
    MAX(CASE WHEN Prize2 IS NOT NULL THEN Prize3 ELSE '' END) AS Prize3,
    MAX(CASE WHEN Prize2 IS NOT NULL THEN '' ELSE Prize1 END) AS Prize4
FROM tablename
GROUP BY Person, CONVERT(CHAR(8), Date, 112)
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
CORRECTION: Forgot to put the day back into a date format:


SELECT Person, CAST(CONVERT(CHAR(8), Date, 112) AS DATETIME) AS Date,
    MAX(CASE WHEN Prize2 IS NOT NULL THEN Prize1 ELSE '' END) AS Prize1,
    MAX(CASE WHEN Prize2 IS NOT NULL THEN Prize2 ELSE '' END) AS Prize2,
    MAX(CASE WHEN Prize2 IS NOT NULL THEN Prize3 ELSE '' END) AS Prize3,
    MAX(CASE WHEN Prize2 IS NOT NULL THEN '' ELSE Prize1 END) AS Prize4
FROM tablename
GROUP BY Person, CAST(CONVERT(CHAR(8), Date, 112) AS DATETIME)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:d1cjm1ex
Comment Utility
The first record should not be overwritten.  Additional columns would need to be added to the output to include all prizes.
0
 

Author Comment

by:d1cjm1ex
Comment Utility
Your solution does create the result set I gave as an example, however I guess I should have been a bit clearer....I need this to be a bit more dynamic in that the NULL values in the second record could contain actual values (Prize 6, etc).  If I add the value "Item6" in the second record for the Prize2 column, the result set is no longer correct.

For a given day for a given person, I need to create one record that has all the Prize values. Each record can have a max of 3 prizes.  The second (or third or fourth)  record may only have one prize value or it could have 2 or 3.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Ooh, yeah, I misunderstood -- that requirement's going to be considerably more complicated :-) .
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now