Anup Anup - 18 days ago 6
SQL Question

SQL Get columns data in a single column

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.

enter image description here

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?

Answer

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

Using UNION ALLworks too, but there you will have 1select statement per Punch. With UNPIVOTyou only need 1 Statement and just add the Punch columns you need.

Comments