Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

SQL Code to flatten out records

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
d1cjm1ex
Asked:
d1cjm1ex
  • 3
  • 2
1 Solution
 
RiteshShahCommented:
what if second record also have prize1,2 and 3 along with 4, should first record need to be overwritten?
0
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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 Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
d1cjm1exAuthor Commented:
The first record should not be overwritten.  Additional columns would need to be added to the output to include all prizes.
0
 
d1cjm1exAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
Ooh, yeah, I misunderstood -- that requirement's going to be considerably more complicated :-) .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now