Create multiple Dynamic columns in an MSSQL

[afrika]

hello guys,
I am trying to create a dynamic reporting system and not sure about how to go about it.

I have a table of departments (which vary for different clients) and the clients have no general naming conventions or lenghts and any number e.g. ABC1, DBR4, AKJ9, akdjafdk etc The last one I did had 47 columns

As they want reports generated and exported to excel files

So when I run select distinct departments from reports where IsEnabled = 1 I get the list of departments

e.g.

ABC1
DBR4
AKJ9
akdjafdk

Now the client wants these as column names. Here is my HARD CODED script of column names. I created a loop to update the columns

How can I achieve dynamic columns ?

thanks

update final_reports
set IAAFDHN = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘IAAFDHN’ ),
IAafd2M = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘IAafd2M’ ),
IFasdA2A = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘IFasdA2A’ ),
IA4AFDM = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘IA4AFDM’ ),
IAAFDA4A = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘IAAFDA4A’ ),
RDAAI3A = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘RDAAI3A’ ),
RAADFAI1A = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘RAADFAI1A’ ),
RAAFDAI2A = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘RAAFDAI2A’ ),
NGEAFD4M = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘NGEAFD4M’ ),
ZAFDAAL1M = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘ZAFDAAL1M’ ),
HFDAPN2M = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘HFDAPN2M’ ),
HAFDPN2A = (select (count()/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘HAFDPN2A’ ),
HPAFDAN3M = (select (count(*)/1000.000) from .[dbo].[##Temp_Reports] WHERE USERNAME = @each and DeptID =‘HPAFDAN3M’ ),

where enabled = 1

[DaveMaxwell]

Sounds like you want a variation of a pivot table, but a little more convoluted.

This page has a number of different approaches which might meet your needs: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

[afrika]

thanks Dave, that solves the problem. I need a little bit more assistance

Is it possible to loop through the username columns and update each one based on the username. Here is my code

select * from
(select username
, clinicid, id
from Reports
)
as ehi pivot(avg(id) for clinicid in (
TESTRAI3A,TESTDT2M,TESTONCEX,TESTGBE3B,TESTHPN3M

))
as oze

[DaveMaxwell]

I’m sorry but I’m not understanding the question…

[r937]

i’m afraid i don’t get it either, and SQL is in my wheelhouse

@afrika, could you please rephrase your requirement for updating the usernames?

[afrika]

haha,
hi Rudy. I recall you helping me out back in 2003 or 2005 not sure, when I first joined this site.

Apologies for my earlier post, maybe some sample data might help. (I have blanked out some names as its live data)

In the diagram above, the username column comes 1st and the pivoted columns give you the average stats

What I am trying to achieve is a sub query like this. e.g. for the first username and first column

SELECT Count(distinct jobid) FROM [dbo].[Reports] WHERE USERNAME = ‘xxxxx.daley’ and ClinicID =‘column1’

for the 2nd row

SELECT Count(distinct jobid) FROM [dbo].[Reports] WHERE USERNAME = ‘xxxxx.taylor’ and ClinicID =‘xxxx123’

etc

and for the 2nd column

SELECT Count(distinct jobid) FROM [dbo].[Reports] WHERE USERNAME = ‘xxxxx.daley’ and ClinicID =‘xxxx123’

As each row show how many distinct JOBID a user (e.g. xxxx.daley) did for that clinic etc etc

Hope this is much clear, thanks

[r937]

@afrika

As each row show how many distinct JOBID a user (e.g. xxxx.daley) did for that clinic etc etc

i would retrieve this information from the base tables, not from a pivot query