how convert row as column using pivot in sql

Question

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

Tapan Patel · 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);

Tapan Patel · Answer

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 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

siva nathan · Answer

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

Tapan Patel · Answer

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);

siva nathan · Answer

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

Tapan Patel · Answer

wait.. give me sometime. I will get back to this.

siva nathan · Answer

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

siva nathan · Answer

Hi.. tapan here im adding one screen shot of my expected result kindly send me a query for that

siva nathan · Answer

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

siva nathan · Answer

can u understand my question tapan

Tapan Patel · Answer

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.

siva nathan · Answer

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

how convert row as column using pivot in sql

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (12)