Pivoting columns in SQL

Transact-SQL
1

Hi All, I am new in SQL and stuck in my dev work as I have a below requirement. I have a data as below ( example) there are four columns ID, TaskName, Type, Date in below example data.

ID, TaskName, Type, Date
44400 M0 A 1/1/2015
44400 M1 A 1/3/2015
44400 M2 A 1/4/2015
44400 M1 CF 2/1/2105
44400 DG1 CF 2/2/2015
44400 M0 POR 2/11/2015
45000 M0 A 2/1/2015
45000 M1 A 2/3/2015
45000 M2 A 2/4/2015
45000 M1 CF 3/1/2105
45000 DG1 CF 3/2/2015
45000 M0 POR 3/11/2015

Now, I want data as below

ID M0_A M1_A M2_A M1_CF DG1_CF M0_POR
44400 1/1/2015 1/3/2015 1/4/2015 2/1/2015 2/2/2015 2/11/2015
45000 2/1/2015 2/3/2015 2/4/2015 3/1/2015 3/2/2015 3/11/2015

Thank you in advance for your help.

2

One way:

select id
      ,max(M0_A)   as M0_A
      ,max(M1_A)   as M1_A
      ,max(M2_A)   as M2_A
      ,max(M1_CF)  as M1_CF
      ,max(DG1_CF) as DG1_CF
      ,max(M0_POR) as M0_POR
  from (select id
              ,case when taskname='M0'  and type='A'   then date else null end as M0_A
              ,case when taskname='M1'  and type='A'   then date else null end as M1_A
              ,case when taskname='M2'  and type='A'   then date else null end as M2_A
              ,case when taskname='M1'  and type='CF'  then date else null end as M1_CF
              ,case when taskname='DG1' and type='CF'  then date else null end as DG1_CF
              ,case when taskname='M0'  and type='POR' then date else null end as M0_POR
          from yourtable
       ) as a
 group by id
3

Thank you Bitsmed. It works. Is there a dynamic way to achieve this goal rather than hard coding TaskName and Type in Case Statement.?

4

You can copy and paste the code sample below to an SSMS window and run it. It pivots the data without having to explicitly specify the task names and types. In the output, the column names are numbered 1,2, etc. You can alias them if you know them in advance. If you don't have that info, and you still want to name the columns, you will need to use dynamic pivot query.

CREATE TABLE #tmp(id INT, taskname VARCHAR(32), [type] VARCHAR(8), [date] DATE);
GO
INSERT INTO #tmp
        ( id, taskname, type, date )
VALUES
('44400','M0','A','1/1/2015'),
('44400','M1','A','1/3/2015'),
('44400','M2','A','1/4/2015'),
('44400','M1','CF','2/1/2105'),
('44400','DG1','CF','2/2/2015'),
('44400','M0','POR','2/11/2015'),
('45000','M0','A','2/1/2015'),
('45000','M1','A','2/3/2015'),
('45000','M2','A','2/4/2015'),
('45000','M1','CF','3/1/2105'),
('45000','DG1','CF','3/2/2015'),
('45000','M0','POR','3/11/2015');
GO


;WITH cte AS 
(
	SELECT id,[Date], DENSE_RANK() OVER ( ORDER BY [type],taskname ) AS RN
	FROM #tmp 
)
SELECT
	id, [1],[2],[3],[4],[5],[6]
FROM
	cte
PIVOT( MAX([Date]) FOR RN in ([1],[2],[3],[4],[5],[6])) P

GO

DROP TABLE #tmp;
GO
1 Like
5

Continuing the discussion from Pivoting columns in SQL:

It's going to be really ugly (I like James' solution better, even though his solution is semi-hardcoded aswell), but here goes:

declare @sqlstring varchar(max);
declare @selectstring varchar(max);
declare @whenstring varchar(max);
set @selectstring=(select stuff((select ',max('+taskname+'_'+type+') as '+taskname+'_'+type
                                   from yourtable
                                  group by taskname
                                          ,type
                                  order by type
                                          ,taskname
                                  for xml path('')
                                )
                               ,1,0,''
                               )
                  );
set @whenstring=(select stuff((select ',case when taskname='''+taskname+''' and type='''+type+''' then date else null end as '+taskname+'_'+type
                                 from yourtable
                                group by taskname
                                        ,type
                                order by type
                                        ,taskname
                                for xml path('')
                              )
                             ,1,0,''
                             )
                );
set @sqlstring='
select id
      '+@selectstring+'
  from (select id
              '+@whenstring+'
          from yourtable
       ) as a
 group by id
';
exec(@sqlstring);
6

Thanks James and Bitsmed for quick response. This solution will help. Thanks again to both.

7

You can use a good ol' dynamic CROSSTAB to do this, as well. The small advantages are that it only hits the table twice, is a bit shorter, and you don't have to worry about slowing things down with de-entitization of the XML in case someone throws some XML reserved characters into the mix.

DECLARE @SQL VARCHAR(MAX) = ''
;
WITH
ctePreAgg AS
( 
 SELECT DISTINCT TNT = TaskName+'_'+[TYPE]
   FROM #Tmp
)
 SELECT @SQL =  @SQL
             +  REPLACE(REPLACE(SPACE(8)
             +      ',<<TNT>> = MAX(CASE WHEN TaskName+"_"+[TYPE] = "<<TNT>>" THEN CONVERT(CHAR(10),[Date],110) END)
'
                ,'<<TNT>>',TNT)
                ,'"'      ,'''')
   FROM ctePreAgg
  ORDER BY TNT
;
 SELECT @SQL = '
 SELECT  ID
' + @SQL + '   FROM #Tmp 
  GROUP BY ID
;'
   EXEC (@SQL)
;