Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Code to flatten out records

Posted on 2009-04-02
6
Medium Priority
?
539 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
ID: 24051282
what if second record also have prize1,2 and 3 along with 4, should first record need to be overwritten?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 24051606
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 70

Expert Comment

by:Scott Pletcher
ID: 24051649
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:d1cjm1ex
ID: 24051907
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
ID: 24053983
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 70

Expert Comment

by:Scott Pletcher
ID: 24060190
Ooh, yeah, I misunderstood -- that requirement's going to be considerably more complicated :-) .
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

571 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