how convert row as column using pivot in sql

siva nathan

i have a table record ike this i want to convert rows as columns using pivot

attdate	present
04-01-17	x
04-02-17	/
04-03-17	L
04-04-17	AB

i want to convert attdate as column and data is present

like this

here 1,2,3 as attdate ==>day

1	2	3	4	5
x	/	L	AB

how to write a query for this

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
Accepted answer
Please test out this queries. I think this is what you are looking for.
create table #TempData(dtdate DATETIME, present char(3));
insert into #TempData
select '4-1-2017','X'
union
select '4-2-2017','/'
union
select '4-3-2017','L'
union
select '4-4-2017','AB'

Select * from #TempData

declare @cols varchar(max); -- don't do max here, try evaluating your options for fixed lenght variables
declare @query varchar(500);
SET @cols  = STUFF((
                    SELECT ','+QUOTENAME(day(dtdate))
                    FROM #Tempdata
                    GROUP BY dtdate
                    ORDER BY dtdate
                FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
            ,1,1,'')
SELECT @cols
SET @query='SELECT ' +@cols+'from

    (select day(dtdate)[dtDay],present from #TempData) x
    pivot
    (
        Max(present)
        for dtDay in ('+@cols+'
    )) p'
    EXECUTE (@query);
0
siva nathan9y ago
I can't understand why this error coming incorrect syntax near 31 . Above query running date between '01-01-17' and01-30-17' Up to 30 days it's working but day 31 cannot work while select why
0
Tapan Patel9y ago
Atleast try to understand what is it doing.
select * into #TempData
FROM
(
SELECT * from table1 where dtdate between '01-01-17' and '01-31-17'
UNION
SELECT * from table2 where dtdate between '01-01-17' and '01-31-17') t

DECLARE @cols varchar(max); --
DECLARE @query varchar(500);
SET @cols = STUFF(( SELECT ',' +QUOTENAME(day(dtdate)) FROM #Tempdata GROUP BY dtdate ORDER BY dtdate FORXML PATH('') ,TYPE).value( '.' ,'VARCHAR(MAX)',1,1, '' ))
SELECT @cols
SET @query= 'SELECT ' +@cols+'from (select day(dtdate)[dtDay],present from #TempData) x pivot ( Max(present) for dtDay in ( '+@cols+' )) p'
EXECUTE (@query);
0
siva nathan9y ago
i have on query doubt in pivot.I have explain the doubt below select * into temp( select * from table1 where dtdate between '01-01-17' and '01-31-17' union select * from table2 where dtdate between '01-01-17' and '01-31-17') t declare @cols varchar(max); -- declare @query varchar(500); SET @cols = STUFF(( SELECT ',' +QUOTENAME(day(dtdate)) FROM #Tempdata GROUP BY dtdate ORDER BY dtdate FORXML PATH( ''), TYPE).value( '.' ,'VARCHAR(MAX)',1,1, '' ) SELECT @cols SET @query= 'SELECT ' +@cols+'from (select day(dtdate)[dtDay],present from #TempData) x pivot ( Max(present) for dtDay in ( '+@cols+' )) p' EXECUTE (@query); while running above query I'm facing incorrect syntax near 31 why.why this error coming and how to avoid this error help me anyone
0
Tapan Patel9y ago
Apologies for delay.

Please test out this queries and let me know. Also, keep in mind that using While in queries could be costly. So try avoiding it by putting appropriate table design to store the required data.
create table #TempData(dtdate DATETIME, present char(3));
insert into #TempData
select '4-1-2017','X'
union
select '4-2-2017','/'
union
select '4-3-2017','L'
union
select '4-4-2017','AB'
union
select '4-14-2017','X'

Select * from #TempData          -- this table represents your data in database
-----------------------------------------------------------------------------------------
DECLARE @beginningdate DATE = (SELECT DATEADD(dd,-1,MIN(dtdate)) FROM #TempData);
DECLARE @maxDate DATE  = (SELECT MAX(dtdate) FROM #TempData);
CREATE TABLE #DateRange(rangedate DATE NULL);
WHILE (@beginningdate<=@maxDate)
BEGIN
SET @beginningdate = DATEADD(dd,1,@beginningdate)
INSERT INTO #DateRange
        ( rangedate ) VALUES (@beginningdate)
END
SELECT * FROM #DateRange        -- this table contains the date ranges from minimum date to max. date in above table
----------------------------------------------------------------------------------------

SELECT rangedate ,
       DATENAME(dw,rangedate) WeekDayName,
       present
INTO #Result
FROM #DateRange
LEFT JOIN #TempData  ON  rangedate=dtdate

UPDATE #Result
    SET present = 'WH'
WHERE WeekDayName='Sunday'


-- considering that you are expecting rest of the days to be marked as present, Updating the temp table
UPDATE #Result
    SET present = 'P'
WHERE present IS NULL

DROP TABLE #DateRange
DROP TABLE #TempData

SELECT * FROM #Result           -- result contains the data required as per your request.
----------------------------------------------------------------------------------------

declare @cols varchar(max); -- Pivot queries on #Result Table
declare @query varchar(500);
SET @cols  = STUFF((
                    SELECT ','+QUOTENAME(day(rangedate))
                    FROM #Result
                    GROUP BY rangedate
                    ORDER BY rangedate
                FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
            ,1,1,'')
SET @query='SELECT ' +@cols+'from

    (select day(rangedate)[dtDay],present from #Result) x
    pivot
    (
        Max(present)
        for dtDay in ('+@cols+'
    )) p'
    EXECUTE (@query);

DROP TABLE #Result
0
Tapan Patel9y ago
wait.. give me sometime. I will get back to this.
0
siva nathan9y ago
Tapan im getting result like below see screen shot

empid. empname 1 2 3 ..... 31
null. wh null. null
1 a. p. wh p ab
my expected result is

empid. empname 1 2 3 ..... 31
1. a. p. wh p. ab
2. b. p. wh p ab
0
siva nathan9y ago
Hi.. tapan here im adding one screen shot of my expected result
kindly send me a query for that
0
siva nathan9y ago
how to get output like this using pivot
empid name 1. 2. 3. 4 . 31
1. a. p. wh p. p. ab
wh means Sunday ab means absent
0
siva nathan9y ago
can u understand my question tapan
0
Tapan Patel9y ago
I am sorry but it is really hard to interpret the question from the text comment. Can you please format your post so that I can understand the qeustion? Or please provide the screenshot or example the way you posted for the original question.
0
siva nathan9y ago
thank you tapan for ur help I have another doubt about pivot I have a table called table1 in that table I'm only inserting present date like below
empid . empname. date
1. a . 04-01-17
1. a. 04-03-17
1. a. 04-04-17
employee present dates are stored in table1 absent dates are not stored in that table1 and also 04-02-17 and 04-09-17 dates are Sunday that dates are not inserted in table1 my expected result is
empid. empname 1 2 3 ..... 31
1. a. p. wh p. ab
2. b. p. wh p. ab
wh means Sunday how to query for that
0