Convert Row data (Dates ) in Column headers in SQL

Question

hello friends i m trying to convert row Data with dates in column headers this is my sql attendanceData Table

and i want to convert row dates into column headers like this

Transpose

can someone tell me how i can do this in sql

i m sure using pivot i can,

so i tried sql query to pivot

SELECT Enterprise_ID, Date, Attendance 
FROM attendanceData natural 
    join (  SELECT Enterprise_ID 
            FROM attendanceData 
            group BY Enterprise_ID
        ) 
pivot (max(P) for Attendance in ('P' as P, 'WO' as WO)

i have edited my question with original data as a Image link — Rajendra Jadhav, Dec 12 '18 at 10:22
@RajendraJadhav post the data, not images or links to images. Don't make people type your data just to test your query. The easiest you make it for people to help, the faster you'll get an answer — Panagiotis Kanavos, Dec 12 '18 at 10:23
In any case, in `PIVOT` you specify the values you want to appear as columns. Given the desired output that would be the dates, not the categories. Those columns need to appear in the `SELECT` clause, ie `SELECT Enterprise_ID, [2018-12-12],[2018-12-13].. PIVOT (... for Date in [2018-12-13],...)` etc — Panagiotis Kanavos, Dec 12 '18 at 10:27
Possible duplicate of [SQL Pivot on dates column?](https://stackoverflow.com/questions/13977241/sql-pivot-on-dates-column) — Panagiotis Kanavos, Dec 12 '18 at 10:28
Possible duplicate of [Understanding PIVOT function in T-SQL](https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql) — bat7, Dec 12 '18 at 10:42

score 0 · Accepted Answer · edited Dec 12 '18 at 10:35

I tried using dynamic pivot

Sample script to generate data:

create table #a
(
  entrprise_id varchar(50),
  datee date,
  attendance varchar(10)
)

insert into #a values
('james','2018-12-22','p'),
('james','2018-12-23','p'),
('pick','2018-12-23','p'),
('rick','2018-12-23','p'),
('pick','2018-12-22','p'),
('rick','2018-12-22','p')

PIVOT Query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.datee) 
            FROM #a c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT entrprise_id, ' + @cols + ' from 
            (
                select*
                from #A
           ) x
            pivot 
            (
                 max(attendance)
                for datee in (' + @cols + ')
            ) p '


execute(@query)

or

select *
from
(
  select *
  from #A
) src
pivot
(
  max(attendance)
  for datee in ([2018-12-22], [2018-12-23])
) piv

how i can get distinct or dynamic date from date column instead of manual input in ....max(attendance) for datee in ([2018-12-22], [2018-12-23]) — Rajendra Jadhav, Dec 12 '18 at 14:45

Convert Row data (Dates ) in Column headers in SQL

1 Answers1