SQL Server pivot using multiple criterias

Question

I struggled to solve the issue I will describe bellow, but still without any success

So, I have the following database structure: http://sqlfiddle.com/#!6/afb83b/1

What I want to achieve is instead of displaying the same group 3 times (or in other cases multiple times), the query should render the following output instead of the output from SQLFiddle:

id_project  | id_group  | A  | R  | K  |  Year  | Month  | Value
1             1           A1   R1   K1    2017    05       0.77
1             2           A1   R1   K2    2017    05       0.3
1             3           A1   R1   K3    2017    05       0.22
1             4           A1   R1   K4    2017    05       0.45
...
1             100         A3   R3   K8    2017    05       0.34

I tried using PIVOT, but I have issues on displaying it like I want it..

Pரதீப் · Accepted Answer · 2017-07-27T08:32:56.267

1

You need to do something like this

select * from
(
select dd.id_project, dd.id_group,dmn.map, dd.year, dt.type,dd.month, dd.value from dm_data dd
left join dm_group_map dgm ON dgm.id_group = dd.id_group 
left join dm_mapping dm ON dm.id_map = dgm.id_map
left join dm_map_names dmn ON dmn.id_map_name = dm.id_map_name
left join dm_type dt ON dt.id_type = dm.id_type
) a
pivot (max(map) for type in ([A],[R],[K]))pv

Demo

Dynamic version

declare @sql varchar(max)='',@col_list varchar(max)=''

set @col_list =stuff((select  distinct ','+quotename(type) from dm_type for xml path('')),1,1,'')

set @sql = '
select * from
(
select dd.id_project, dd.id_group,dmn.map, dd.year, dt.type,dd.month, dd.value from dm_data dd
left join dm_group_map dgm ON dgm.id_group = dd.id_group 
left join dm_mapping dm ON dm.id_map = dgm.id_map
left join dm_map_names dmn ON dmn.id_map_name = dm.id_map_name
left join dm_type dt ON dt.id_type = dm.id_type
) a
pivot (max(map) for type in ('+@col_list+'))pv'

exec (@sql)

Demo

edited Jul 27 '17 at 08:32

answered Jul 27 '17 at 07:59

Pரதீப்

91,748
19
131
172

Yes, that is one idea, it will work if the users are never going to change (add, edit, delete) the content from dm_type table. But, since everything is dynamic I assume that instead of fixing the columns there should be a Select in the IN clause from the pivot.. but that's where I am blocked.. – rosuandreimihai Jul 27 '17 at 08:05
@rosuandreimihai - thats really easy search for Dynamic pivot in sql server.. Then use my answer – Pரதீப் Jul 27 '17 at 08:05
Ok, then, one other issue.. what about the performance of the query? is it to slow down if we have more than let's say 100000 rows? – rosuandreimihai Jul 27 '17 at 08:07
@rosuandreimihai - Performance depends on so many factor.. To start with I need to know about the index details.. Run the query in your database and post the execution plan – Pரதீப் Jul 27 '17 at 08:29
@rosuandreimihai - Added dynamic version – Pரதீப் Jul 27 '17 at 08:33
1

Flawless! Thank you! – rosuandreimihai Jul 27 '17 at 08:37
Do you know by chance how I can put it in a function? Because of the EXEC I cannot use it with a function.. – rosuandreimihai Jul 28 '17 at 10:06
@rosuandreimihai - Dynamic queries cannot be used inside function. Create a stored procedure to do this. – Pரதீப் Jul 28 '17 at 10:12
Yes, but then I would end up in having to create a new table every time I use it, and because it's dynamic I cannot add new columns to the new table everytime.. :( – rosuandreimihai Jul 28 '17 at 10:17

SQL Server pivot using multiple criterias

1 Answers1