Using data from tables to dynamically create report column headers

[ghbeers]

Is it possible to use values of data from a table as column headers? Here is what I would like to do if I use SQL to create this report. My table has data associated with a term term in each record. For example if the data exists when school term = '14FA' then I want the value (Or a flag) from the desired field in the record to appear in the column that would be dynamically labeled '14FA'. As I said I want the column headers to be dynamic. So when I run the report this year it might have column headers '14FA', '14SU', '14SP', '13FA', etc. There may be 10 or more different terms I want to report on, and I only want the term column to appear if the data I am looking for is found. Then next year when the report is run, it might contain dynamic column headers such as '15FA', '15SU', '15SP', '14FA', '14SU', etc.

I was thinking about first creating a temp table related to the terms that contain the data I want, hoping I could use it to define the columns I would have in the report. But I do not know how to convert, if possible, the term value to a column header.

Is this possible without hard-coding the terms as the column headers?

 

[philhege]

We're not sure what your data schema looks like, or what you want the results to include.

I'll take a stab and say you probably want a pivot table.

-----------
With business clients like mine, you'd be better off herding cats.

 

[ghbeers]

This looks very interesting and promising. I will give it a try. Thank you very much.

 

[RyanEK]

Hi, any chance you can give sample of your data?

 

[ghbeers]

Using the pivot function did work but was not dynamic as time goes on and the report is used in the future. Since you need to select on what is in a list of fixed col heading, e.g. 2012FA, 2013SP, 2013FA, 2014SP, ... , having to fix the entries in the list was a deal breaker. This has to be dynamic so that the report user never has to make any modifications to the actual query, other then being prompted for a date.

I created a temp table with a key, a term and a flag with no problem. This was dynamic. However having to fix the terms in the "in" list will not cut it.

 

[DaveInIowa]

Maybe a dynamic query will solve the problem?

DECLARE @DynamicQuery VARCHAR(1024) = 'SELECT Column1 <C1>, Column2 <C2>, Column3 <C3> FROM MyTable'

DECLARE @Column1Name VARCHAR(50)
DECLARE @Column2Name VARCHAR(50)
DECLARE @Column3Name VARCHAR(50)

-- Retrieve the current column names...
SELECT @Column1Name = Column1Name, @Column2Name = Column2Name, @Column3Name = Column3Name
  FROM MyTermTable
 WHERE Term = 'A parameter value'

-- Plug them into the predefined query...
SET @DynamicQuery = REPLACE(@DynamicQuery, '<C1>', @Column1Name)
SET @DynamicQuery = REPLACE(@DynamicQuery, '<C2>', @Column2Name)
SET @DynamicQuery = REPLACE(@DynamicQuery, '<C3>', @Column3Name)

EXEC (@DynamicQuery)

 

[gk53]

try that

CREATE TABLE #CourseSales
(Course VARCHAR(50),Year INT,Earning  MONEY)
GO
--Populate Sample records
INSERT INTO #CourseSales VALUES('.NET',2012,10000)
INSERT INTO #CourseSales VALUES('Java',2012,20000)
INSERT INTO #CourseSales VALUES('.NET',2012,5000)
INSERT INTO #CourseSales VALUES('.NET',2013,48000)
INSERT INTO #CourseSales VALUES('Java',2013,30000)

declare @headers as varchar(8000)

CREATE TABLE #Course
(Course VARCHAR(50),Year INT)



insert into #Course
select distinct Course ,Year INT
from  #CourseSales

select @headers = (
Select  distinct 
		 substring(
			(
				Select '],['+ ST1.Course  AS [text()]
				From #Course ST1
				Where ST1.Year = ST2.Year
				ORDER BY ST1.Course
				For XML PATH ('')
			), 3, 1000) [coutses]
	From #Course ST2
	where Year = 2012) + ']'

select @headers

declare @sql varchar(8000)

set @sql = 'SELECT * 
				FROM #CourseSales
			PIVOT(SUM(Earning)
				FOR Course IN (' + @headers + ')) AS PVTTable'
exec(@sql)				

      
drop table #CourseSales      
drop table #Course

 

[ghbeers]

Well these posts took what I tried a step farther into something I did not know how to do. Thank you, I am anxious to try these out.

 

[ghbeers]

I tried to work with gk53's syntax, but sql is complaining about the substring( select ... for some reason I can't determine. Was hoping to fully understand what is going on in this approach, but I can't get that far. Is there something missing in this syntax?

 

[gk53]

the code I published works fine
substring function there because we need to strip ], on first item.