sql server pivot converting rows to columns with dynamic query
|
Jak
858
Points
132
Posts
|
Hi, I have followin table: EmpDate Emp ----------------------- -------------------------------------------------- 2015-01-01 00:00:00.000 A 2015-01-02 00:00:00.000 A 2015-01-02 00:00:00.000 B 2015-01-01 00:00:00.000 C 2015-01-02 00:00:00.000 D 2015-01-04 00:00:00.000 B I want result as: EmpDate A B C D ----------------------- ----------- ----------- ----------- ----------- 2015-01-01 00:00:00.000 1 0 1 0 2015-01-02 00:00:00.000 1 1 0 1 2015-01-04 00:00:00.000 0 1 0 0
Views:
8778
Total Answered: 1 Total Marked As Answer: 0 Posted On: 04-Jan-2015 17:06 Share: 
|
|
Rahul M...
4822
Points
23
Posts
|
Hi, Use this: select EmpDate,CASE WHEN A IS NULL THEN 0 ELSE 1 END AS A,CASE WHEN B IS NULL THEN 0 ELSE 1 END AS B,CASE WHEN C IS NULL THEN 0 ELSE 1 END AS C,CASE WHEN D IS NULL THEN 0 ELSE 1 END AS Dfrom(select EmpDate, Empfrom test) dpivot(max(Emp)for Emp in (A,B,C,D)) piv; If you have dynamic column then you have to use procudure as:Create PROCEDURE TESTEMPASBEGINDECLARE @cols AS NVARCHAR(MAX),@cols1 AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)set @cols = (select distinct ',CASE WHEN '+Emp+' IS NULL THEN 0 ELSE 1 END AS '+Emp+'' AS [text()] from test FOR XML PATH(''))set @cols=SUBSTRING(@cols,2,LEN(@cols)-1)set @cols1 = (select distinct ','+Emp AS [text()] from test FOR XML PATH(''))set @cols1=SUBSTRING(@cols1,2,LEN(@cols1)-1)set @query = N'SELECT EmpDate,' + @cols + N' from (select EmpDate, Empfrom test) xpivot(max(Emp) for Emp in (' + @cols1 + N')) p '--PRINT @queryexec sp_executesql @query;END And then use as:EXEC TESTEMP
Result will be as: EmpDate A B C D ----------------------- ----------- ----------- ----------- ----------- 2015-01-01 00:00:00.000 1 0 1 0 2015-01-02 00:00:00.000 1 1 0 1 2015-01-04 00:00:00.000 0 1 0 0
Posted On:
04-Jan-2015 17:23
|
