I have a table in which Employee Punches are saved. For each date for each employee there are columns in the table as Punch1, Punch2 till Punch10.
I want all this Punch Columns data in a Single Column. e.g. If in a row i have dates stored in Punch1, Punch2, Punch3, Punc4....so on. I want all this data in a single Column.
How to achieve this?
UNPIVOTcan be used to normalize your table:
If you table is called
EmployeePunchesit would look like this:
SELECT UserID, Punch FROM ( SELECT UserID, Punch1, Punch2, Punch3, Punch4 FROM EmployeePunches ) AS ep UNPIVOT ( Punch FOR Punches IN (Punch1, Punch2, Punch3, Punch4) ) AS up
UNION ALLworks too, but there you will have 1select statement per Punch.
UNPIVOTyou only need 1 Statement and just add the Punch columns you need.