locked
SQL Table column values in Rows RRS feed

  • Question

  • User1724124194 posted

    Hi Friends,

    I have an employees attendance table with the following data. 

    EMPCODE YEAR MONTH DATE1 TYPE1 DATE2 TYPE2 DATE3 TYPE3 DATE4 TYPE5
    10001 2019 10 01-10-2019 P 02-10-2019 P 03-10-2019 A 04-10-2019 WO

    The columns continue still DATE31 and  TYPE31. 

    Can anyone help me to create SQL Query to get the below output

    EMPCODE YEAR MONTH DATE TYPE
    10001 2019 10 01-10-2019 P
    10001 2019 10 02-10-2019 P
    10001 2019 10 03-10-2019 A
    10001 2019 10 04-10-2019 WO
    10001 2019 10 05-10-2019 P
    10001 2019 10 06-2019 P

     Please help

    thanks in advance

    Saturday, October 5, 2019 3:55 PM

Answers

  • User452040443 posted

    Hi,

    Try something like this:

    select
        a.[EMPCODE], 
        a.[YEAR],
        a.[MONTH],
        ca.DATEN,
        ca.TYPEN
    from  Attendance as a
    cross apply
    (
        values (a.DATE1, a.TYPE1), (a.DATE2, a.TYPE2), (a.DATE3, a.TYPE3) --, (a.DATE4, a.TYPE4), ...
    ) as ca (DATEN, TYPEN)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 5, 2019 5:27 PM

All replies

  • User452040443 posted

    Hi,

    Try something like this:

    select
        a.[EMPCODE], 
        a.[YEAR],
        a.[MONTH],
        ca.DATEN,
        ca.TYPEN
    from  Attendance as a
    cross apply
    (
        values (a.DATE1, a.TYPE1), (a.DATE2, a.TYPE2), (a.DATE3, a.TYPE3) --, (a.DATE4, a.TYPE4), ...
    ) as ca (DATEN, TYPEN)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 5, 2019 5:27 PM
  • User-719153870 posted

    Hi sels2005j,

    @imapsp's answer is perfect, while in addintion, if you want to avoid hard coding the (a.DATE2, a.TYPE2), (a.DATE3, a.TYPE3) --, (a.DATE4, a.TYPE4), ... part, you can try do a while loop in your query.

    Please refer to below code:

    declare @i int=1
    declare @s varchar(max)='select
        a.[EMPCODE], 
        a.[YEAR],
        a.[MONTH],
        ca.DATEN,
        ca.TYPEN
    from  Attendance as a
    cross apply
    (
        values '
    while @i<=31--you will only need to change this 31 to change the whole loop range,other code parts are static
    begin
    set @s+='(a.DATE'+CONVERT(varchar(50),@i)+',a.TYPE'+CONVERT(varchar(50),@i)+'),'
    set @i+=1
    end
    set @s=LEFT(@s,LEN(@s)-1)+') as ca (DATEN, TYPEN)'
    exec(@s)

    Best Regard,

    Yang Shen

    Monday, October 7, 2019 2:56 AM
  • User1724124194 posted

    Hi,

    Try something like this:

    select
        a.[EMPCODE], 
        a.[YEAR],
        a.[MONTH],
        ca.DATEN,
        ca.TYPEN
    from  Attendance as a
    cross apply
    (
        values (a.DATE1, a.TYPE1), (a.DATE2, a.TYPE2), (a.DATE3, a.TYPE3) --, (a.DATE4, a.TYPE4), ...
    ) as ca (DATEN, TYPEN)

    Hope this help

    Thanks YOU very much imapsp. This is perfect and what I was looking.

    Monday, October 7, 2019 3:49 AM