d1cjm1ex
asked on
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
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
RiteshShah
what if second record also have prize1,2 and 3 along with 4, should first record need to be overwritten?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
ASKER
The first record should not be overwritten. Additional columns would need to be added to the output to include all prizes.
ASKER
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.
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.
Ooh, yeah, I misunderstood -- that requirement's going to be considerably more complicated :-) .