Dynamic column headings (based on SELECT) in Temp table

[Earth]

Hi everyone,

Overall problem:
I currently have a nice sproc that returns the "right" data but in the "wrong" format. For example,

Current format -

AsAtDate	Mnemonic	Amount
--------	--------	------
Date1		M1		A1
Date1		M2		A2
Date1		M3		A3
Date2		M1		A4
Date2		M3		A5
Date2		M4		A6
Date3		M1		A7
Date3		M2		A8
Date3		M4		A9

Desired format -

AsAtDate	M1	M2	M3	M4
--------	--	--	--	--
Date1		A1	A2	A3	<NULL>
Date2		A4	<NULL>	A5	A6
Date3		A7	A8	<NULL>	A9

Points to note:
There is no way to determine the number of Mnemonic's (ie. the number of cols that will need to be returned) until the initial SELECT statement has been run (ie. to determine which Mnemonic's are in the set).

The query that returns this data set is rather complicated already - merging results off various servers and dbs. Hence, the approach (in my sproc) I am trying to get work atm (which won't be the most efficient, but is a nice starting place) is:

****

CREATE PROCEDURE dbo.spByTypeTesting AS

DECLARE @sql varchar(1500) 

CREATE TABLE #tblData (AsAtDate datetime, Mnemonic varchar(4), Amount float)
/* Table to hold the "right" data temporarily */

INSERT INTO #tblData exec spByType
/* We put the RIGHT DATA into it */

/* The below query finds the "column headings".... but how do I make them "be" the column headings? */
SELECT Mnemonic
FROM #tblData

/* Let's create the Table itself... only problem is, need to create it dynamically. */
CREATE TABLE #tblRedoneData (AsAtDate datetime)

/* Insert some code that:
Iterates through each row of #tblData. For each row:
	If AsAtDate IS IN @tblRedoneData
		Insert Amount into Column "Mnemonic"  (There will only ever be one Amount for each Mnemonic on a date)
	Else
		Insert AsAtDate into AsAtDate Column, Amount into Column "Mnemonic"
*/

SELECT *
FROM #tblRedoneData
DROP table #tblRedoneData

GO

****

In other words, have the right _data_ in a temp table, and then figure out how to transform it into the right _format_.

Problem is that I have no idea how to do this. I've been reading all the threads I have been able to find. I don't believe a Pivot Table is the answer. Perhaps a cursor?

The first step I have been trying with the above code is simply to create the table with the right column headings. However, not even -

****

SET @sql='CREATE TABLE #tblRedoneData (AsAtDate datetime)'

print(@sql)
exec(@sql)

SELECT *
FROM #tblRedoneData

****

Will work. I am guessing that that is because the temp table will only exist for the duration of the execute statement?

Any ideas, links, solutions would be much appreciated. I am very much stuck on this one!

Much TIA

 

[tlbroadbent]

I think a pivot table will work. Try something like this.

set nocount on
Create Table #tektest
(AsAtDate smalldatetime,
Mnemonic char(2),
Amount char(2))

Insert #tektest values('7/22/2001', 'M1', 'A1')
Insert #tektest values('7/22/2001', 'M2', 'A2')
Insert #tektest values('7/22/2001', 'M3', 'A3')
Insert #tektest values('7/22/2002', 'M1', 'A4')
Insert #tektest values('7/22/2002', 'M3', 'A5')
Insert #tektest values('7/22/2002', 'M4', 'A6')
Insert #tektest values('7/22/2003', 'M1', 'A7')
Insert #tektest values('7/22/2003', 'M2', 'A8')
Insert #tektest values('7/22/2003', 'M4', 'A9')
go
Select
AsAtDate=convert(char(10), AsAtDate, 101) ,
M1 = Max(Case When Mnemonic='M1' Then Amount Else Null End),
M2 = Max(Case When Mnemonic='M2' Then Amount Else Null End),
M3 = Max(Case When Mnemonic='M3' Then Amount Else Null End),
M4 = Max(Case When Mnemonic='M4' Then Amount Else Null End)
From #tektest
Group By convert(char(10), AsAtDate, 101)
go

drop table #tektest
go

Check the following links for more Pivot/Crostab techniques including dynamic crosstabs.

http://www.swynk.com/sqlscripts/misc7-2.asp

http://www.sqlteam.com/item.asp?ItemID=2955

http://www.umachandar.com/technical/SQL6x70Scripts/Main85.htm

http://www.umachandar.com/technical/SQL6x70Scripts/Main86.htm

http://www.umachandar.com/technical/SQL6x70Scripts/Main9.htm

http://www.sqlmag.com/articles/index.cfm?articleid=15608

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Earth]

Terry,

As far as I can tell the solution you suggested doesn't allow me to create a fully dynamic table - where the number of columns (and names of them) are dependent on a previous select statement.

I believe I will have to use a cursor to make the table, but then perhaps a solution similar to yours could be used to populate my table.

I will let you know how I progress.

Thanks.

 

[tlbroadbent]

I did recommend some pages that discuss dynamic pivot tables because I relaize the limitations of the of the script I posted. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Earth]

I have adapted the solution at the first link sent. It seems to be a very flexible and good solution! If anyone goes onto use a similar approach, be careful as the solution does not cater for NULLS being returned in the query you specify to create your Column Headings. An easy solution to that for me, was to change my query from:

SELECT DISTINCT [Mnemonic] 
FROM #tblData
ORDER BY [Mnemonic]

To:

SELECT DISTINCT [Mnemonic] 
FROM #tblData
WHERE [Mnemonic] IS NOT NULL
ORDER BY [Mnemonic]

This is a good short term work around anyway - just change your input paramter.

Thanks for the suggestions Terry.