Pivot multiple columns dynamically..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pivot multiple columns dynamically..

Author

Topic

sql_chaser
Starting Member

33 Posts

Posted - 2015-04-13 : 15:37:56

[code]Create table tbl_Emp_Log
( As_Of_Dt Int,Load_Dt date, Emp_Id varchar(50),Log_Seg Int,Work_Code varchar(10),Enter_Date int,Enter_Time int,Work_Assigned varchar(100),Completion_Date int)[/code]

[code]insert into tbl_Emp_Log
select 20150410,'2015-04-10','6A23419',89,'CODE1',20140822,825,'John',20140822 UNION ALL
select 20150410,'2015-04-10','6A23419',107,'CODE10',20140612,1630,'Sam',20140822 UNION ALL
select 20150410,'2015-04-10','6A23419',90,'CODE2',20140822,825,'Aien',20140822

select 20150410,'2015-04-10','9DF679',67,'CODE8',20140823,625,'MKJ',20140826 UNION ALL
select 20150410,'2015-04-10','9DF679',34,'CODE10',20140617,1530,'Lon',20140826 UNION ALL
select 20150410,'2015-04-10','9DF679',123,'CODE12',20140829,425,'Dacy',20140826 [/code]

------------------------------------------------------------------------

Need some help to dynamically pivot few columns based on the Emp_Id and Work_Code.

The view should be based on the Emp_Id and Work_Code as the result should be

As_Of_Dt,Load_Dt,Emp_Id,CODE1_Log_Seq,CODE1_Enter_Date,CODE1_Enter_Time,CODE1_Work_Assigned,CODE1_Completion_Date,CODE10_Enter_Date,CODE10_Enter_Time,CODE10_Work_Assigned,CODE10_Completion_Date,CODE2_Enter_Date,CODE2_Enter_Time,CODE2_Work_Assigned,CODE2_Completion_Date,

The same applies to the next emp_id "9DF679".The code can be added more..

sql_chaser
Starting Member

33 Posts

Posted - 2015-04-13 : 18:57:36

The below query works but is failing when Work_Assigned field is turned...It fails at the Max value conversion
Conversion failed when converting the varchar value 'John' to data type int.

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

select @cols = STUFF((SELECT ',' + QUOTENAME(Work_Code + col)
from tbl_Emp_Log t
cross apply
(
select 'Log_Seg', 1 union all
select 'Enter_Date', 2 Union all
select 'Enter_Time',3 union all
select 'Work_Assigned',4 union all
select 'Completion_Date',5
) c (col, so)
group by col, so,Work_Code
order by col, so,Work_Code
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
print @cols

set @query = 'SELECT Emp_Id,' + @cols + '
from
(
select Emp_Id,
col = Work_Code + convert(varchar(100),col),
value
from tbl_Emp_Log t
cross apply
(
select ''Log_Seg'', Log_Seg union all
select ''Enter_Date'', Enter_Date Union all
select ''Enter_Time'', Enter_Time union all
--select ''Work_Assigned'', Work_Assigned union all
select ''Completion_Date'', Completion_Date
) c (col, value)
) x
pivot
(
max(value)
for col in (' + @cols + ')
) p '
execute(@query)

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-14 : 01:53:38

Your problem is related to that Union ALL.
Try to cast to varchar


cross apply
	(
		select ''Log_Seg'', CAST(Log_Seg AS VARCHAR(100)) union all
		select ''Enter_Date'', CAST(Enter_Date AS VARCHAR(100)) Union all
		select ''Enter_Time'', CAST(Enter_Time AS VARCHAR(100)) union all
		select ''Work_Assigned'', Work_Assigned union all
		select ''Completion_Date'', CAST(Completion_Date AS VARCHAR(100))
	) c (col, value)
) x

sabinWeb MCP

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-14 : 10:40:53

This worked for me:


declare @dyn nvarchar(max);
set @dyn = stuff(
(
    select
         ',MAX(case when Work_Code = ''' + Work_Code + ''' then Log_Seg end) AS '         + Work_Code + '_Log_Seq' 
       + ',MAX(case when Work_Code = ''' + Work_Code + ''' then Enter_Date end) AS '      + Work_Code + '_Enter_Date'
       + ',MAX(case when Work_Code = ''' + Work_Code + ''' then Enter_Time end) AS '      + Work_Code + '_Enter_Time'
       + ',MAX(case when Work_Code = ''' + Work_Code + ''' then Work_Assigned end) AS '   + Work_Code + '_Work_Assigned'
       + ',MAX(case when Work_Code = ''' + Work_Code + ''' then Completion_Date end) AS ' + Work_Code + '_Completion_Date'
       + ',MAX(case when Work_Code = ''' + Work_Code + ''' then Enter_Date end) AS '      + Work_Code + '_Enter_Date'

    from (select distinct Work_Code from tbl_Emp_Log) _
    order by Work_Code
    for XML path('')
)
,1,1,'')
--select @dyn



declare @sql nvarchar(max);

set @sql =
' SELECT As_Of_Dt,Load_Dt, Emp_Id, ' + @dyn +
' FROM tbl_Emp_Log' +
' GROUP BY As_Of_Dt,Load_Dt, Emp_Id'

select @sql

exec sp_executesql @sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources